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 |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2004-03-09 : 16:05:02
|
| I'm fairly new to DTS and would like to know the best approach to moving CSV file records to SQL 2000 tables, but only if certain conditions are met. For example, I wish to check each record in the CSV file to see if it already exists in the SQL table. If it does exist, check to see if any column value has changed. If YES, either replace the entire record or replace the column that has changed. If NO, don't transfer the record.I've only come up with this approach.- Define the source file as comma delimited- Define a SQL Connection Property- Use the Transform Data Task to move ALL CSV records to a staging table- Use SQL scripts to load the primary tables, performing the necessary edits prior to inserting or updating the recordsIs there a better approach?Thanks, Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-09 : 16:16:03
|
| BULK INSERT would be the recommended approach for what you have described, instead of DTS. By doing it this way, you could just put everything in a stored procedure or a script.BULK INSERT the data into a staging table. Then run T-SQL to get the data to the appropriate table. Tara |
 |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-09 : 16:23:20
|
| Does it matter that the CSV file imports will occur nightly? I'm guessing no.Just curious, does DTS have the ability to perform conditional logic in order to determine how and if the transfer of data should occur?You're helpful as always Tara,Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-09 : 16:26:06
|
| If you go with BULK INSERT or DTS, either can be scheduled nightly. DTS can do lots of things such as conditional logic. But I'm guessing that what you are trying to accomplish can ALL be done in T-SQL.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-09 : 16:50:10
|
| Tara,With BCP can I specify a field terminator of comma followed by a space. The CSV file I am importing has a space after each comma. Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-09 : 16:53:39
|
| Yes I believe so, but bcp is different than BULK INSERT. BULK INSERT is a T-SQL command where as bcp is an executable.Tara |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2004-03-09 : 16:56:02
|
| My mistake. Reading a bit too quick. Using BCP was an option I thought about yesterday. I'll take a look at BULK INSERT.Thanks, Dave |
|
|
|
|
|
|
|
|
DTS - How to move CSV data to SQL Tables?