SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Data in Prod table. Lookup ? Merge?


Update Data in Prod table. Lookup ? Merge?

Author
Message
connect.akshay
connect.akshay
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 181
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.
Tarun Jaggi
Tarun Jaggi
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1661 Visits: 703
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.
connect.akshay
connect.akshay
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 181
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.
Paul Hernández
Paul Hernández
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 661
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
tmitchelar
tmitchelar
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 508
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.
connect.akshay
connect.akshay
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 181
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.
Paul Hernández
Paul Hernández
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 661
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search