Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Data in Prod table. Lookup ? Merge? Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 12:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 5:42 AM
Points: 19, Visits: 170
Hi,

I am in need of a solution. I am supposed to load the data of a table from PROD server to UAT. If records are missing in UAT, load the missing rows. How Should i go about it ?

Second Problem. I am fetching some data (EmpId,NAME,CreditCardNumebr) from some text files. They are collaborated based on EmpId from a table in SQL Server (ID,Address,ContactNumber). The combined information (ID,NAME,ContactNumber,Address,Creditcard) have to be loaded in the main table. IF the record doesn't exist, ADD. But if some information is missing in the fields of the records present, UPDATE.

I was able to get some information from Lookup Video session uploaded. But not able to do the required things.

Please help.
Post #1428429
Posted Monday, March 11, 2013 5:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:48 AM
Points: 1,166, Visits: 562
Can't you just truncate the target table and reload it again instead of checking whether the record exists or not?
This will give you a huge boost from the performance point-of-view.
Post #1429178
Posted Monday, March 11, 2013 5:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 5:42 AM
Points: 19, Visits: 170
I could have. But it is not a Staging table. It is main table, acting as a main repositry.
Anyway I did make a partial solution for the problem.

We need to use 2 LookUp Transformations to get the desired result.
1 For collabarating data of the Flat file and the table that holds the partial data.
1 For checking for record existance based on the business key (i.e. ID (Primary Key))

Flat File Source --> LookUp (For collabaration) --> LookUp (For record check) --> OleDb Destination

The records that comes out in the (NO Match Output) are filled in the table.
I need to find out the way to update the records (Which come in the Match output)

If you guys can provide me a solution for it , it will be highly appreciated.
Post #1429190
Posted Tuesday, March 12, 2013 3:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
Hi Akshay,

I just wrote an example of the usage of the Merge command in this post

I think the T-SQL merge command is a powerful option.

If you have any question I could explain you a little bit more.

Kind Regards.


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1429626
Posted Tuesday, March 12, 2013 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 11:37 AM
Points: 124, Visits: 487
akshay.thanai (3/8/2013)
Hi,

I am in need of a solution. I am supposed to load the data of a table from PROD server to UAT. If records are missing in UAT, load the missing rows. How Should i go about it ?

Second Problem. I am fetching some data (EmpId,NAME,CreditCardNumebr) from some text files. They are collaborated based on EmpId from a table in SQL Server (ID,Address,ContactNumber). The combined information (ID,NAME,ContactNumber,Address,Creditcard) have to be loaded in the main table. IF the record doesn't exist, ADD. But if some information is missing in the fields of the records present, UPDATE.

I was able to get some information from Lookup Video session uploaded. But not able to do the required things.

Please help.


For problem 1, I would extract data from your PROD server, do a lookup to your UAT table's primary key to find out if it exists or not. If it exists, ignore, if it doesn't exist then insert.

For problem 2, I think a MERGE Statement is the easiest way to handle your data flow properly. Or you could pull data in from your text file, do a lookup to your main table, and if it doesn't exist - add. If it does exist, send the data to a staging table and then peform a SET Update from your staging table (that houses all of your existing rows) to your main table. If there isn't much data and/or your table only has a few columns then you could perform an update only when one of the columns (outside of the primary key) are not equal to eachother. For tables with a lot of data and/or many columns, you might want to look into storing all of the non-primary key values as a hashbyte value. Then perform an update if that hashbyte value is different between your incoming data and main table data.
Post #1430119
Posted Wednesday, March 13, 2013 12:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 5:42 AM
Points: 19, Visits: 170
Thank you all for suggesting your solutions. Use of a staging table would definitely help.
As the number of columns are less, I applied a different solution.
Insertion was done as I explained above.
Use OLEDB command to update the records. Map columns in exact order from source to output as in update command. E.g.
UPDATE [dbo].[EmpDetails] SET Name = ?, Address = ?,Salary = ?,PhoneNumber = ?, PanNumber =? where id = ?

Hope this make sense for bigger data models as well.
Post #1430234
Posted Wednesday, March 13, 2013 12:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:46 AM
Points: 115, Visits: 472
Hi Akshay,

I wouldn't use a OLE DB Command, it is extremly slow for large datasets. It will perform the updates row by row. It is a good exercise for you to make it and test it.
You can also use a Temp Table and then make a batch update using an Execute SQL Task, but at the end I bet that you will use the MERGE solution.

Further question, just write back!


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1430237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse