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 |
|
Maquis
Starting Member
25 Posts |
 Posted - 2003-10-17 : 11:07:06
|
| I have an excel file I am trying to import into a SQL table on a weekly basis. Each time I receive the file, it contains the entire history, and I only want to import the records that are new (since the last import). I know I can import into a staging table in SQL and then only update the final destination table with the new stuff using a query, but my boss wants to only import the Excel rows that are new. Is this done on the transformation tab in the DTS package? Is it an ActiveX script? I can't seem to get it right...(There is a common ID field that I can compare the incoming records against)Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 13:25:11
|
| You could do it inside the transformation. Instead of the default copy column, you could use an ActiveX script. This ActiveX script would have to look at the destination table and check if the common ID already exists. You could also just truncate the table prior to the load.I would recommend using a staging table though. Try to talk your boss into it if possible.Tara |
 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-17 : 13:29:53
|
| You can use instead of DTS the following:SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\my.xls;Extended Properties=Excel 8.0')...[sheet1$] WHERE [id] NOT IN (select [id] from t) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 13:42:39
|
| I never thought of that. I guess I should use the OPENDATASOURCE so I can get familiar with it. I have only used it once so far.Tara |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-10-17 : 14:30:39
|
| Oh I myself got known it by sheer chance... so much info to learn :(<Just in case> This works too:INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\my.xls;Extended Properties=Excel 8.0')...[sheet1$]SELECT * FROM t |
|
|
|
|
|
|
importing selected records from excel file