March 8, 2013 at 12:36 am
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.
March 11, 2013 at 5:25 am
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.
March 11, 2013 at 5:41 am
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.
March 12, 2013 at 3:09 pm
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.
March 13, 2013 at 12:37 am
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.
March 13, 2013 at 12:43 am
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply