Today we got a client ask us to upload data from excel sheet into sql server, update data to the sql server database.
Well, my collegues usings a simple script that copy the values from the excel file. If there are 1,000 records, there will be 1,000 rows of sql update statements.
I decided to do it using another method and a good friend of our - DTS.
Actually, I got another method which uses openrowset which also works but the database server doesn't allow MSDTC to start so bo bian. The DBA scared to corrupt the production db server simply by starting the MSDTC service on the server, which I suspected was installed incorrectly, thats why it refuse to restart. Anyway, i think its ridiculous that a simple MSDTC service can corrupt a database server.
So, early in the morning i use DTS to upload to a temp table and I wrote a simple sql script to update the required table using the same Primary key on both tables as a join. Following that, the client call again, and she said the excel data is wrong, had to resend and ask us to re-upload again.
Thinking back, although writing 1,000 lines of sql script is simple but due to unforeseen cirumstances using DTS and a simple update statement can save the day and increase productivity and reduce the amount of effort wasted.