October 25, 2001 at 11:31 pm
Can I do a DTS import from an Excel file, whereby the package updates those records which already exists in the database from excel and insert new records which doesnt exists from excel to SQL?
It will do a comparison by a unique(e.g. username) between excel and my db.(By transformation or stored procedure?) This package will be executed periodically.
can the above be done?
October 26, 2001 at 9:13 am
Somebody else may have a better / different way of doing this but, I usually use a transform task or a bulk insert task to put the file contents into a temp table and then execute straight T-SQL to do my updates and inserts. Pretty plain but effective.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 26, 2001 at 9:21 am
I agree with David - even though you can treat Excel as an OLEDB data source doesn't mean you should! Not much extra work to get into a temp table.
Andy
October 26, 2001 at 9:48 am
actually i had 2 remote db,both db has the table has almost identical attributes. i thought of doing a ,erge replication but my manager do not wish to meddle around with the proxy/firewall. i doubt this 2 db are on tbe same network cos one of which is in the production site and the other is for intranet.
thus, i thought of doing a DTS package, to export the table records from production db to excel and then some transformation/validation to import from the intranet db.
do u guys have better way out than this? really appreciate ur input on this..thanks
October 26, 2001 at 10:21 am
I do this on a regular basis and import into a staging table. Then I have a sproc that updates changed records (by matching PKs) and deletes them from the staging table. Then the sproc imports the remaining rows.
I use the DTS package to import the rows and run the sproc.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply