Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2003-06-03 : 04:43:40
|
| I get regular incrimental CSV dumps of data from a legacy UNIX financial system. The data in the dump has overlapping entries with previous dumps.What is the best way to create & maintain an accurate relational structure of this data? Duplicate the structure with one db being the main one and use DTS to empty other one and pull new dumps into it then use some form of replication between the 2?Any ideasScottPS The data being inserted does have a primary key, so I could just insert it and tell the insert statment not to insert duplicate values for that field but to continue with the next record. How do I do this?Edited by - scott on 06/03/2003 07:18:35 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-03 : 09:49:13
|
| You have a table that you need to keep up to date.Create another table with the same structure in another database.(I append Staging to the names of the table and database).You have two processes - import data to staging, merge data with production table. It is important to keep the two processes separate.import data to staging delete data from staging table and import the data.The import method can be anything. For csv files I prefer bulk insert as it's easier and faster.You probably need to look into a directory to see if there's a file there then call the import with that file name then archive the file to another directory.Merge data with production tableUpdate rows that are already there and insert those which aren't.I tend to delete using the primary key then insert all.If you are certain that you aren't getting updated rows then you can just insert rows that don't already exist.This means it doesn't matter if you reprocess files.Logging.Make sure you log the file that was imported, how many records in the file, how many updated and inserted and how many records in the table afterwards.For csv files it's easiest to just run the process in a loop until there are no files in the import directory then it's easy to reprocess lots of files after an error.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2003-06-04 : 05:06:41
|
| Thanks, got most working just need help on the insert/update.What is the best way to do this? Case/cursor...We have our table staging and production they both have 2 fields (Field1 & Field2) Field1 is the PK.Some of the data in staging is already in production and some is new. What will the statement be to get the new stuff inserted and the existing updated?Thanks |
|
|
|
|
|
|
Loading & Syncronising legacy data