Best way to import/export

Question

Post reply

Best way to import/export

Anthony Burns

Grasshopper

Points: 17
More actions
February 24, 2005 at 4:10 am

#88128

Hi,
I look after a simple SQL Server database with lots of records. Every now and then I need to provide some of the data to users in Excel form, for them to update and then I need to import it back into SQL Server. For example, I do the following query:
SELECT ID, BelongsTo, StartDate FROM Jobs WHERE StartDate > '1/1/2005'
Copy and paste the results to an Excel file and then somebody goes through it and updates the BelongsTo field for each of the records, then I need to import that data back into SQL Server.
I'm primarily an ASP developer and this is my first experience with SQL Server, so the way I do it at the moment is to import this data into a temporary table and then write an ASP script to loop through each record in the temp table and update the Jobs table. I'm sure there must be a better way to do this?
Anthony Burns.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Yoav S. SSC Eights! Points: 914 More actions · Answer 1

Hi.

There is a bultin ETL product in sql server 2000 called DTS.

In enterprise manager Epand the folder "data transformation service"

And of course look at books online for the tutorial.

yoav.

Anthony Burns Grasshopper Points: 17 More actions · Answer 2

I've got a couple of books here in the office on SQL Server and I've done a search on the interweb for info on DTS, but nothing I read has any info on importing records in an UPDATE kinda way. It all just seems to be relevant to appending data.

Can you point me in a more specific direction?

Thanks,

Anthony Burns

Michael Ziese-200679 Right there with Babe Points: 789 More actions · Answer 3

So, now you must use the DTS feature in SQL.

The following steps are very simple an you find anything in BOL.

1. Create a Job to Export data from table to Excel.

2. Create a job that Import data from Excel file or files to table.

3. Schedule the jobs to run when you need it, or start it manually.

4. Let your users know where the files are stored and who users can store it Excel files. Use different folders only with the rights to read or write for users.

5. Give the Users the time sheet for your DTS jobs.

I think tahts the best way to do so.

Kind Regards

Michael

athurgar SSCrazy Points: 2816 More actions · Answer 4

DTS is the best way to go. The export to Excel is very easy. The updating is a bit harder, but a simple solution would be to have DTS import from Excel into a temporary table, then the next step would be to do an update on the permanent table from the temp table. Then drop the temp table.