Inserting non-duplicates from table to table

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

SQL Server 2000 Forums

SQL Server Development (2000)

Inserting non-duplicates from table to table

Author

Topic

Munchausen
Starting Member

25 Posts

Posted - 2005-09-19 : 20:33:22

I have two tables, and I'm trying to insert records into Table1 from Table2 that exist in Table2, but not in Table1.

The thing is, there are two columns that together make a record unique, not just one, and it's kind of throwing me off.

So to sum up: I need to check for records with combinations of these two columns that exist in Table 2 and not Table 1, and then insert those records into Table 1.

Any help would be greatly appreciated.

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 22:28:31

Is this what you are looking for?


INSERT INTO dbo.Table1(Col1, Col2, ...)
SELECT ColA, ColB, ...
FROM   dbo.Table2 AS T2
WHERE NOT EXISTS
    (
        SELECT *
        FROM   dbo.Table1 AS T1
        WHERE     T1.Col8 = T2.ColX
              AND T1.Col9 = T2.ColY
    )

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-19 : 22:31:41

thinking from the top of my head without really knowing the data types

insert into table1(fields...)
select fields... from table2
where (col1 + col2) not in (select col1 + col2 from table1)

will this do?

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-19 : 22:35:10

oh shucks, is that 3 seconds?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 23:27:30

Nope, 3 minutes

... but it took us both 2 hours to then answer at the same time! Sorry about that.

I prefer yours Jen - given that neither col1 nor col2 can be NULL (and assuming that both are varchar)

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-19 : 23:46:27

i think your approach is better with filtering on the subquery (not to return all rows),
outer join with exclusion for null values perhaps?

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-09-19 : 23:57:07

"not to return all rows"

Oh right, yes I suppose that is a benefit. It would be easy enough to allow for NULLs too (but doesn't sound like that's an issue for this instance):


INSERT INTO dbo.Table1(Col1, Col2, ...)
SELECT ColA, ColB, ...
FROM   dbo.Table2 AS T2
WHERE NOT EXISTS
    (
        SELECT *
        FROM   dbo.Table1 AS T1
        WHERE     (T1.Col8 = T2.ColX OR (T1.Col8 IS NULL AND T2.ColX IS NULL))
              AND (T1.Col9 = T2.ColY OR (T1.Col9 IS NULL AND T2.ColY IS NULL))
    )

Kristen

Munchausen
Starting Member

25 Posts

Posted - 2005-09-20 : 20:49:31

I just wanted to thank you ladies for your help. It worked great.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-20 : 23:08:15

better retract that back

Kristen is a guy