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.
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.