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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-20 : 08:31:55
|
| David writes "Versions:SQL2000WIN2000Scenario:CSV file, with very high redundancy, but the redundant data changes over time. EG. It is a call record list from a PABX and many call records have the same details, but these details change as our call rates change and users move around our uni campus...So I can not anticipate what the look-up tables should contain, they must be populated as a new data value is found in the database that is not in the look-up.My 1st Attempted solution:Use DTS (arghhh) to pump the data from the csv file into the database and i tried using an active x script to check each line as it gets pumped across and compare the fields with the corresponding look-up table fields. If the data isn't found in the look-up, then it will add the new line to the look-up and re-pump the line using the pseudonew look-up table. IT COULDN'T BE DONE. It could check, or insert but not the two at once.My 2nd Attempted solution:The DTS starts to pump the csv file data to the database, if an error occurs, because of a non-existent value in a look-up table. Then I'd manually look to see which table caused the error, then I run another DTS package which is designed to only populate the particular table with any new data values from the csv file. I then, re-run the original DTS pump and the error is gone. THIS IS TOO TEDIOUS, and I don't want to have to code an external applet to do the manual labor, unless it is the ONLY solution. My question:Which is the smartest, fastest, and best way to do this. Should I steer clear of DTS? and if so, which stored-procs should I use? (DTS would be preferred by my boss, because it is his standard, but I don't care personally, as long as it works) I am quite new to SQL and if this is a really dumb question, can you just point me to the right information resources; I couldn't find anything specific on this subject, and the general help is to general. I need direction #:o/ Thank you.Regards,David Di MuroMyURL: www.mp3.com.au/DiscoDave/" |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-20 : 09:18:57
|
How fast does dts import your file?If you need more speed in the import than bcp is the way to go.However I would consider importing the file to a stagging table and then using stored procedures to do your check and manipulate your data into its final destination.quote:
Should I steer clear of DTS? and if so, which stored-procs should I use? (DTS would be preferred by my boss, because it is his standard, but I don't care personally, as long as it works)
You can use dts to import data and run T-SQL statements that includes calling stored procedures. DTS is not an alternative to stored procedures its a process by which you can manipulate data from various sources.http://www.sqlteam.com/item.asp?ItemID=10627http://www.swynk.com/friends/knight/optimizingdataloads.asphttp://www.sqldts.com/ |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-11-24 : 19:54:38
|
| are you using the MULTI-PHASE functionality of the data pump in SQL2K (aka "multiphase data pump")??It is possible to attach vbscript to the various events in the multiphase data pump, including simple calls to ADO to check/insert the lookup data BEFORE the data row is inserted.So, with respect, I doubt that your statement "IT CANT BE DONE" will hold true.----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
|
|
|
|
|
automatic Look-Up table population. 