sql serversql-server-2008
I've previously used the SQL Server 2008 R2 "import data" wizard to import an Excel file into my database.
Now I'm trying to import more Excel sheets that may contain the same data that was previously imported.
How can I import these Excel sheets to the same database, while skipping the rows from Excel that already exist in the database?
Choose to save the package instead of running it right away.
Then edit the dtsx file and on your copy task check "keep nulls".
Then run the dtsx.
I didn't test it but think it will solve your case.
Testing the suggestion on the comment on express with advanced services... it worked well:) You create the destination table, set a null default on the column and after importing data with the dtsx you remove the default or set it to whatever you want. dtsx import will replace nulls with the default value of the column when you can't check the "keep nulls".
create table dbo.test (
a int null
, other columns ...
)
alter table dbo.test add constraint x default null for a
--Run Import task here
alter table dbo.test drop constraint x
Two options come to mind. If you go with the local copy approach you can manually use the AutoFill feature to fill in the blank values. For an automated approach, the one approach I would think of in T-SQL is to use a cursor to loop through all the rows, capture the non-NULL Dates and Types into variables and when you encounter a NULL value, UPDATE the current record with the last non-NULL value. An integer identity may be needed in the table you're importing to confirm the rows have been inserted in the order as found in the file.
Best Answer
2008 supports except. Import the excel data into a different table--mytemptable--then
Then you can get rid of mytemptable (or whatever you want to call it).