Importing only new rows from Excel, with PK

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Importing only new rows from Excel, with PK

Author

Topic

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 16:25:08

Hi guys,

I am not advanced in SQL Server so please dont beat on me too much.

I have to import and excel spreadsheet into this table every six months.

The table is set up like this:

SSN (PK) LastName FirstName
-------- -------- ---------
444444444 Jones Don

Now, when I get a new spreadsheet to import, some of the rows will already exist in the destination table. I only want to import the new rows, but the SSN column will be Unique Primary Key. How do I go about doing this? Will it give me an error when I try to import? Everything I've searched pointed me to SSIS and I have no idea how to us that tool. Is there a simpler way to this?

Please help me if you can guys. It's much appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 16:34:50

See http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

E 12°55'05.63"
N 56°04'39.26"

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 16:44:08

quote:
Originally posted by Peso

See http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

E 12°55'05.63"
N 56°04'39.26"

I dont understand this?????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 16:46:31

You can use either LEFT JOIN, NOT IN or NOT EXISTS to find out which records that are new, or missing, in table.

E 12°55'05.63"
N 56°04'39.26"

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 16:52:57

quote:
Originally posted by Peso

You can use either LEFT JOIN, NOT IN or NOT EXISTS to find out which records that are new, or missing, in table.

E 12°55'05.63"
N 56°04'39.26"

OK. Thanks sir. I will try.

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 17:07:25

jeez. I am confused.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 17:17:46

About what?

E 12°55'05.63"
N 56°04'39.26"

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 17:24:03

How do I incorporate this into an import?

I mean, I ran the NOT IN query, and I got some results, but how do I get the rows that arent in the tab but are in the excel spreadsheet, into the table without an error?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-16 : 17:26:16

INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT DISTINCT s.Col1, s.Col2, s.Col3
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT * FROM Targetable AS x WHERE x.SSN = s.SSN)

E 12°55'05.63"
N 56°04'39.26"

rhinton21
Starting Member

10 Posts

Posted - 2009-06-16 : 18:02:24

quote:
Originally posted by Peso

INSERT INTO TargetTable (Col1, Col2, Col3)
SELECT DISTINCT s.Col1, s.Col2, s.Col3
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT * FROM SourceTable AS x WHERE x.SSN = s.SSN)

E 12°55'05.63"
N 56°04'39.26"

THANKS!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 00:44:26

See my edited response above.
The referenced tabli in the NOT EXISTS part should be TargetTable, not SourceTable.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources