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 |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2004-05-06 : 04:58:46
|
| Hi....I was given an Excel file that contains data to import. The Excel file contains one row of data for a person, but the data elements (Excel columns) need to go to more than one table. For example, the main table contains Users and a few data elements. There is also an Education table (contains a transcript of classes for each user). There is a primary key (userid) in the Users table and a foreign key (fkUserid) in the Education table.Now, part of my problem (at least to me) is that the userid values are created on each INSERT into the Users table. So....if I use DTS to import the Excel data into just the Users....this works great. But I cannot figure out a way to also import the Education data from the Excel file....and keep the userid values in synch. Is there an example or article that I could review?Thanks - dw |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-06 : 05:04:50
|
| Import the data into a table. That table should match the format of the source.Run an SP on this to add the data to the database. If necessary do it row by row to get the ID's but you can use set based operations if you wish. Depends how much data you need to deal with and how much time you are willing to spend on the development.==========================================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. |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2004-05-10 : 03:49:12
|
| Thanks for the suggestion. I didn't even think about importing the data into a table first...before getting it to the final table. This is definitely worth a try.Thanks. - dw |
|
|
|
|
|
|
DTS from Excel into multiple tables