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 |
|
g_r_a_robinson
Starting Member
45 Posts |
 Posted - 2004-11-15 : 06:01:43
|
| I'm importing an excel spreadsheet into sql server and even though there a few ways to do it with code I am looking into to doing it with the DTS package. For the most part it seems pretty straightforward. I've mapped my columns from the csv to the table fields in my db etc. Heres my problem. I have four tables and one csv file. The CSV file contains some data that I want in one table, some data in another table and so on and so fourth. In a nutshell how do I relate the data for one row in the csv across the four tables. For eg CSV file contains data on the products and its supplier. And the tables as below Products FK_supplier_id Suppliers supplier_id - primary key which is auto-incremented (but i need to make sure that this primary key ends up in the FK_supplier_id of the products table otherwise the product table won't have a reference to its supplier) See my problem. Whats the best way to do this. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-15 : 06:03:25
|
| Why not import into a staging table then run a stored proc to do it.Can control it all from a dts package.==========================================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. |
 |
|
|
g_r_a_robinson
Starting Member
45 Posts |
Posted - 2004-11-15 : 17:18:58
|
| Would the staging table be created on the fly to match whatever the csv file contains or do I have to create the table first. Secondly how do I go about initiating the stored procedure to do its job when the dts has finished inserting into this staging table. An example or tutorial on this would be most helpful as I have never worked with DTS however it seems to me to be perfect for what I need to do.Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-15 : 20:48:46
|
| If it is a csv file I wouldn't use dts but do it all in an sp using bulk insert to insert.seehttp://www.nigelrivett.net/ImportTextFiles.htmlIf you want to use dts then create a table to match the csv file.Use a data transformation task to import the data.Use a sql task with an on success workflow to call an SP to put the data into the tables.==========================================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. |
|
|
|
g_r_a_robinson
Starting Member
45 Posts |
Posted - 2004-11-18 : 17:22:22
|
| Thanks for your example it was a real help and I would really like to use it. Only problem for me is, is that in your example you use a text file with the format aaabbcc1234 etc and I need to parse a csv file. I know the changes for csv must be trivial but i'm not really not that confident with t-sql. Could you please point in the right direction as to what parts I need to change for it to work with csv.Thanks Again |
|
|
|
|
|
|
Help with a DTS package.