February 25, 2012 at 9:19 pm
Hello everyone
My excel source has :
FirstName
LastName
Age
Address
Phonenumber
Zipcode
Destination Table:
ID---------------Primary
FirstName
LastName
Age
Address
Phonenumber
Zipcode
Two Things :
1. Insert new rows
2. how do I update the destination table if the excel source has changed on multiple columns ( for ex : age,Address,Phonenumber,Zipcode) and exist in the database .
How do I compare between my excel source and my destination table?
The problem is my excel source doesn't have a Unique Identifier to look up between source and destination and update the records...
Please Help me I need it for my project...for work :((
Rahman
February 25, 2012 at 11:46 pm
if your excel have all set of correct record then clean the table first and re-populate it with excel data every time.
else
create linked server with your source excel and then update the record in the table which is changed and insert new records.
----------
Ashish
February 27, 2012 at 12:05 am
If the Excel file contains the whole set of records, truncate the table and load it from scratch, as crazy4SQL suggested.
If it doesn't, you need to find the business key. It's one column or more that uniquely identifies a row. If you don't have that, you can't meet your requirements.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 27, 2012 at 1:37 pm
Hello All,
Thanks for the reply... well this excel sheet is like a transaction table... repeating rows.. so no business keys... what options are there... few options that I have tried :
1. Load it on temporary table... then update (merge statement)..doesnt work because when the temp table is loaded its reading from the actual source ( excel)...
2. oledb command but I cannot put the where clause...
Is there any way I can get this done.... ? ;(
February 27, 2012 at 8:07 pm
without following the RDBMS concept, you can not acheive this.
You need to maintain at least one key common and unique in your sql table and excel sheet.
If its still not too late, make one auto-increment(by 1) key in sql table and similarly maintain the same value in excel.
----------
Ashish
February 27, 2012 at 8:51 pm
Hi,
Your Question seems to be like the same here,hope this helpful to you...!!
Please ignore if not,i'm new to this forum and learning something.
http://qa.sqlservercentral.com/Forums/Topic974429-392-1.aspx
February 29, 2012 at 5:56 am
u can take script comp
u can add column as row id (auto increament) in output column of script comp.
and insert firstname,lastname ,age
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply