SQL Studio Import XLS without duplicates

[ITGURU]

I have a SQL database table which I import from Excel.

 

Every time I update the xls I have to delete the current table and re-import all the records as it's just a source data table.

 

The XLS sheet has 1 column which is always unique so is there a way to check this against existing entries and only enter the new ones rather than creating duplicates?

 

The XLS comes from another program which exports the source data.

 

Thanks in advance.

[theriver]

Yes. Rather than import the XLS file into your SQL table, import it as a different table. You can then run a query to insert only the XLS records that don't exist in the SQL table. Although you presumably also want to update any existing rows as well, where they might be different.

 

This also has an advantage that you don't have to delete the SQL data table before you start, so if there's a problem with the import it doesn't leave you with a broken system.

[mavhc]

Something like

 

INSERT INTO tableName (columns) SELECT DISTINCT (table.column) FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\spreadsheet.xls', [sheetName$])

 

perhaps

[ITGURU]

Yes. Rather than import the XLS file into your SQL table, import it as a different table. You can then run a query to insert only the XLS records that don't exist in the SQL table. Although you presumably also want to update any existing rows as well, where they might be different.

 

This also has an advantage that you don't have to delete the SQL data table before you start, so if there's a problem with the import it doesn't leave you with a broken system.

 

Any help with the SQL code for this would be great!

Thanks.

[theriver]

Sure - I've put together an example that demonstrates the principle, just step through it.

 

There are other ways to achieve the same thing, but this is probably the simplest.

 

 

staging table demo.txt

[ITGURU]

Sure - I've put together an example that demonstrates the principle, just step through it.

 

There are other ways to achieve the same thing, but this is probably the simplest.

 

 

[ATTACH]46447[/ATTACH]

 

Thanks for this - how would this work if i'm importing from an XLS sheet? Assuming there will be quite a bit of difference in some of the code.