sql- import and updating from txt file

[r6ashih]

hi,
how do i import info from a tab delimited txt file so that it updates records and adds new records? without just making all new records?

thanks.

microsoft sql 97

 

[MrChad]

Read up on Data Transformation Services. I believe that it will do what you are looking for.

 

[labgeek]

DTS will do it, but there should be an "Import and Export Data" that's basically a wizard front-end to DTS that walks you through it step by step.

 

[r6ashih]

i tried using DTS but i get an error: data source for column 31 ('reference') is too large for the specified buffer size.

also when i import, my source file doesnt have some columns that are in the destination table. will that cause any problems?

thanks

 

[labgeek]

If you use the import/export wizard front end, there's a step that let's you do column mapping and transformations. The column mapping let's you pick individual source elements and map them to the destination fields. That can take care of getting the right data to the right field. By default it goes 1 for 1 if I remember right, and missing fields are at the end. Transformations let you do things like get dates in the right format, string functions, etc. if you need to do that.

In the DTS package if you've built one, double-click on the arrow between the Text Object and the Microsoft OLE DB object to do the same thing.

For your buffer problem, check out these ...

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q281/5/17.ASP&NoWebContent=1

http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326

 

[r6ashih]

hi again. i still cant seem to get it to update the table. i selected append destination table and it just adds all the rows from my source as new rows to the destination.

I need to get it to update rows and add new rows from my txt file.

 

[labgeek]

I misread your original post... You won't be able to import. That'll do exactly what your seeing - all new records.

You're going to need to design a custom DTS package. It will need to read the source file, get the record id, attempt to read the 2nd file, if found update the record, if not append the record to the table. I'm about to go start a system shutdown for monthly maintenance, and will be heading out after that. But PM me your email address and I'll see if I can't remember to get you a stripped down version of what we do in the dts package tommorrow morning. BTW, we import to a temp table in the database for speed then process from there to the real table. We found it to was quicker - don't ask why, we just accepted it.

 

[labgeek]

I can't send you the whole DTS package. There's too much in there I'd have to edit out. However I'll PM you with a section with an "Active Script Task" that does some matching of records from one table and updating another table. This is the section you'd be interested in, and it should give you an idea of what you could do. A couple of notes about it... We use several "SQL Tasks" prior to and post this script task inside the DTS to setup and cleanup the tables as needed for the script. You will also need a Microsoft OLE DB Provider object inside the DTS for the script to have access to the database. In the DTS we've set most of the workflow rules to continue on success. We have others that execute on failure to recover and notify if need be.