Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.



View 5 Replies


Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

May 12, 2015

In Master tabel i have these date datas




SO when i try to load from  Master table to parent and child table i am using using expresssion like 

WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE())  and convert(date,B.Ftpdate) = convert(date,getdate()) ;

If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.

My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this that yeserday date data alone will get loaded from Master to parent and  child tables.

If i run this expression to remote server  i cannot change the system date in server.

while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.

What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.

View 10 Replies View Related

Transact SQL :: Is Table Will Be Extracted When Querying To DB Views

Sep 15, 2015

I wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?

View 5 Replies View Related


Dec 7, 2007

I have the following data



I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.

How can I do that???

View 1 Replies View Related

DB Design :: Specifying Table Types - Master Or Transaction File

Jul 17, 2015

Is it possible to categorize a table by "type"; for example "Master file" or "Transaction file"?

If yes, then:

Where is this specification made on the table, and how can I interrogate the table to determine the "type" ....

View 8 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data  in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

Data Warehousing :: Querying In Fact Table

May 2, 2015

I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.

Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?

1. XML
2. Spatial
3. Clustered
4. Non-Clustered

View 2 Replies View Related

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,


Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

Transact SQL :: Update Multiple Table Referencing New Table Data

Aug 4, 2015

I have a table called ADSCHL which contains the school_code as Primary key and other two table as

RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.

I tried using Merge Update function not sure if this is going to work.

Update dbo.ADSCHL

View 10 Replies View Related

Transact SQL :: Insert Data From Temp Table To Other Table

Oct 5, 2015

I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary  (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.

Actual Data
Expected Result



View 3 Replies View Related

Transact SQL :: Table To Store All Transaction Happened On A Single Day

Jun 11, 2015

I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this

My I want to show this result like this
  BNT          0            0
  BCN        0            0
  SCN        1            12
  BTC        0             0
  STC        0             0
  SNT        0             0

and so on for all the the currency can I achieve like this .

View 6 Replies View Related

Transact SQL :: Append A Column To The Huge Transaction Table

Oct 18, 2015

I want to append the column to the transaction table(60 million records in it.) ..

Our transaction table is being used in production.. but i have very less amount of time ..

Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..

View 8 Replies View Related

Transact SQL :: Insert Data To Different Table From Other Table

Aug 22, 2015

we have a table below

ID         Roll        Name         Amount     . . . . . . so on
1            2           Alex            500
2            5           Jones          600
3            2           Ales            400

and we have TABLE 2

ID         Roll        Name         Amount   . . . . . . . so on

In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,

1) Remove all data from TABLE2 First
2) Select those records from TABLE1 where Roll = 2
3) Then, Insert the results to TABLE 2

So we should get the result similar to below:

ID         Roll        Name         Amount   . . . . . . . so on
1            2           Alex            500
3            2           Ales            400

how can I do this in a same sql statement.

View 3 Replies View Related

How To Recover Master Database Table Data

Aug 14, 2006

Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.

Here is the script:

declare @table_name sysname
declare @alter_table_statement varchar(256)
declare @delete_statement varchar(256)

-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
xtype = 'U'
name <> 'dtproperties'

-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
while @@Fetch_Status = 0
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' nocheck constraint all'
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
while @@Fetch_Status = 0
fetch next from table_name_cursor into @table_name
select @delete_statement = 'delete from ' + ltrim(rtrim(@table_name))
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
while @@Fetch_Status = 0
fetch next from table_name_cursor into @table_name
select @alter_table_statement = 'alter table ' + ltrim(rtrim(@table_name)) + ' check constraint all'
close table_name_cursor

deallocate table_name_cursor

I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.

Is there anyway to recover the data that I have lost?

View 5 Replies View Related

Master Data Services :: Table Partitions In MDS?

Apr 13, 2015

Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....

We created table managers_plan in MDS :

id_manager (domain attr)
POS (domain attr)
Managers ~ 800 records
POS  ~ 100 000 records

managers_plan Total records for
1 year = 100K x 12 = 1 200 000

managers_plan  - table partitions  
- Will bemade ?

View 3 Replies View Related

Master Data Services :: How To Transfer Data From Table To MDS

Nov 26, 2015

I am newbie to MDS of SQL,want to know how we can transfer tables from two different SQL Databases to MDS.Suggest me the steps to proceed with any examples.

View 2 Replies View Related

Transact SQL :: Querying Data With Latest Date

Jun 24, 2015

Below is the table information. I want the Code information with latest EDate only.

EDate Datetime,
Code varchar(255),
Cdate int,
Price int

[Code] ....

Expected output :
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22

View 13 Replies View Related

Master Data Services :: Unique Identifier In MDM Profisee Stage Table

Apr 13, 2015

I am new to MDM profisee tool and currently working for Addres verification project for my organization.I wanted to clear my doubts here about Unique Identifer in Stage table and how it works.. Here what i understand till now:

Step 1) I created an Entity using MDM profisee UI and it generated a stage table in MDS database called stg.Address_leaf
Step 2) I have loaded data from external source to MDS stage table using ETL and passed Import type as 2 and Import status id as 0
Step 3) I have run store procudure system generated something stg.udp_Address_leaf to load the model and passed the version name as Version_1, Log flag as '1' and Batch tag as 'Address'

Now my below are my questions:
1) What is the field i can use in MDS stage table to populate my unique intifier value coming from source? (lets say Address_Id is my Unique value for all the records coming from source)
2)  Where/how Unique Identifier is useful in this process? Will this be useful in next time load from stage to Model?
3) If i truncate and load my MDS stage table in next run and few earlier records has been updated how it will update those records in Model? will this process (code present in SP) recognize by Unique Identier column present in MDS stage table?

View 3 Replies View Related

How To Alter Column Length Of The Master Table Along With The Slave Table?

Aug 13, 2006

In SQL Server 2005,here are two tables, created by the following SQL Statements:



For the length of Column ID is not enough, So I want to alter its length.The alter statement is:


For the table student is referenced by table score, the alter statement can not alter the column of the table student, and the SQL Server DBMS give the errors.

But, I can manually alter the length of the column ID in SQL SERVER Management Studio. How to alter column length of the master table(student) along with the slave table(score)?


View 2 Replies View Related

Merging Master And Staging Table (was: Update Table Using SSIS)

Nov 20, 2007

Is this anyway to finding updated/ deleted recored using anyother data flow transformation tasks without using sql task.
Can find the new records using merge join task.

Is there better way to merge master table using staging table?

Thanks in advance.


View 1 Replies View Related

Selection Of Table Based On Value Selected For The Particular Attribute In Master Table

May 29, 2008

Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????

View 1 Replies View Related

Using Transaction To Insert Master/Detail Data(ASP.NET 2.0)

Sep 30, 2007

Hi !
I want to insert master/detail data using transaction if while insert if error it will Rollback. Help me! Thanks

View 2 Replies View Related

Insert / Update In Master Table And Also Save A History Of Changed Records : Using Data Flow/simple Sql Queries

Feb 9, 2007


My scenario:

I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to

1) separate the master (static) data from the flat files,

2) check whether that data is present in the master table, if not then insert that data into the master table

3) If data present then move that existing record to an history table and then update the main master table.

All the 7 fields need to be checked to uniquely identify a single record in the master table.

How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.

Thanks in advance for your help.



View 4 Replies View Related

Query A SQL Server Compact Table While Querying A SQL 2005 Server Table

May 21, 2008

Hi there,

I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.

SELECT * from RemoteDB.TESTDB.dbo.Objects

WHERE Last_Updated > '2008-05-21 10:51:00'

AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)

Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.

Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.

Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?

Thanks in advance

View 10 Replies View Related

Transact SQL :: Key And Indexes On Two Column Data Table Or Parsed View (Large String Of Data And Filename)

Oct 4, 2015

I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.

Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based 
column b: is the source filename of the data load (varchar 256)

a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)

b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?

c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.

-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?

View 4 Replies View Related

Transact SQL :: Grouping Data Within A DB Table?

Jul 9, 2015

I have a table with couple of hundred thousand records, i have added a new column to the table named MD_Group.

Now i need this column populated, so that every 10,000 row set in that table gets a numeric (incremental number) assigned starting from 1.

So the first 10,000 rows will have MD_Group = 1, next 10,000 rows will have MD_Group = 2 , next 10K MD_Group = 3 etc.

For testing purpose here is a results table with total of 11 rows where we want data grouped every 3 rows.



-- Create Sample Table

Declare @GroupRelation_Test TABLE (
MD_Group [varchar](20),
CustomerNumber [varchar](20) ,
AmountBilled [varchar](20) ,


-- Test data

INSERT INTO @GroupRelation_Test
( CustomerNumber, AmountBilled,MinAmounttBilled)
SELECT'12','15243','' UNION ALL
SELECT'1231234','15243','' UNION ALL
SELECT'463','15243','' UNION ALL
SELECT'442','15243','' UNION ALL


View 22 Replies View Related

Transact SQL :: How To Purge Data From A Table

Nov 4, 2015

We have staging table in which data is dumped from files . The staging table is truncated for every load . In order to retain data from staging table we are creating staging_purge table which hold the staging data. what is the fastest way  to copy data from staging to purge table without impacting the load process.

View 13 Replies View Related

Transact SQL :: Table Row Data Show In Column Data

Oct 22, 2015

I have a simple table data i want want to show row data in to column data.
SELECT clblcode,mlblmsg
FROM warninglabels

My expected result will be 

0001                 0002  0003                      0004
-------                ------- --------                    --------
May Cause....       Important.....  Take madi...........         Do Not Take.......

View 16 Replies View Related

Querying Using Another Table

Apr 30, 2008

Hello all,

I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.

what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.

does anyone have an idea?
thanks in advance.

View 2 Replies View Related

Querying Table More Than Once

Jun 12, 2008

Hi All

Thx in advanced for any help anyone can offer me.

I have 2 tables Staff and Class which are as follows

ID (Primary Key)
Etc. . .

ID (Primary key)

LessonTemplateID and TermID link to other tables which i don't need right now.

The Class.TeacherID and Class.AssistantID both link to the Staff.ID

I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.

If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.

Im really stuck on this and just can't figure it out (is it even possible?).

Thx for any help Scott.

View 2 Replies View Related

Lookup From One Table To Other Master Table

Apr 21, 2015

I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'

My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -

FROM Sandbox.dbo.Sheet1$ A
LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B
ON A.[Street 1] = B.address1_line1

View 4 Replies View Related

Transact SQL :: Inserting Distinct Data From One Table To Another

Aug 19, 2015

I have 2 tables: Source Table - IncidentDimNew and Destination Table - IncidentDimNew with identical columns: Id and CreatedDate but the data is different.

I would like to insert distinct data from those 2 columns of 1st table into the same 2 columns into the 2nd table but I onlt want to replace the values in the 2nd table where the Created Date is > 2015-04

I compiled the code in order to get the information I want from 2 tables:

Source Table
LEFT(CONVERT(VARCHAR(10)), CreatedDate, 126), 7)

[Code] ...

This is the code I wrote in order to do it
INSERT INTO IncidentDim 


where left(CONVERT(VARCHAR(10), CreatedDate, 126),7) > '2015-04'


But what it does it's gives the SUM of the values in the corresponding rows where the values are not null which I don't want.

So  how do I leave the values as is from table IncidentDim and insert new values as it is from IncidentDimNew where the Created Date is > 2015-04?

View 2 Replies View Related

Copyrights 2005-15, All rights reserved