INSERT Missing Records Query (Help!)

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

INSERT Missing Records Query (Help!)

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-01-16 : 09:58:34

I have two tables of related (but not similar) information stored in two databases. One database will be updated quite frequently; I need to write a query that inserts new records into the second database when needed.

I have the first part of the query hashed out:


SELECT
	apinfo.vendornumber 
from 
	apinfo 
where 
	apinfo.vendornumber 
	NOT IN 
	(SELECT certdb.dbo.certificates.vendornumber from certdb.dbo.certificates)

This will give me a list of vendors that are in the apinfo db but not in the certificates db. However, now I'm stuck trying to write the INSERT query.

I thought it might be:


INSERT INTO 
	certdb.dbo.certificates (VendorNumber, VendorName)
VALUES
	(SELECT 
		apinfo.VendorNumber, 
		apinfo.VendorName 
	from 
		apinfo 
	where 
		apinfo.vendornumber 
		NOT IN (SELECT certdb.dbo.certificates.vendornumber from certdb.dbo.certificates))

But that gives me an 'incorrect syntax' error. In fact, pretty much anything I put after VALUES gives me a syntax error.

Any ideas what I am doing wrong? Also, how hard/advisable would it be to handle something like this as a stored procedure, rather than a saved query that gets run from Management Studio every so often?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-16 : 10:07:40

Do it without 'VALUES' and without the outer brackets.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 10:17:56

Is the NOT IN as efficient as a NOT EXISTS?

I have found that IN(...) with LOTS of values has terrible performance, but that is with a variable containing the LIST, so in this case the correlated query might be optimised and it doesn't matter.

However, I would do this:


INSERT INTO 
	certdb.dbo.certificates (VendorNumber, VendorName)
SELECT 
	SRC.VendorNumber, 
	SRC.VendorName 
from 
	apinfo AS SRC
where 
	NOT EXISTS
	(
		SELECT *
		FROM certdb.dbo.certificates AS DST
		WHERE DST.vendornumber = SRC.vendornumber 
	)

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2010-01-16 : 10:44:18

quote:
Originally posted by Kristen

Is the NOT IN as efficient as a NOT EXISTS?

I have found that IN(...) with LOTS of values has terrible performance, but that is with a variable containing the LIST, so in this case the correlated query might be optimised and it doesn't matter.

However, I would do this:

INSERT INTO
certdb.dbo.certificates (VendorNumber, VendorName)
SELECT
SRC.VendorNumber,
SRC.VendorName
from
apinfo AS SRC
where
NOT EXISTS
(
SELECT *
FROM certdb.dbo.certificates AS DST
WHERE DST.vendornumber = SRC.vendornumber
)

Thanks to both of you for your help; to be honest, with the relatively low level of SQL proficiency that I have, I'm just happy when my select * statements work. Efficiency considerations are a whole different league (for now), but it definitely helps knowing that a NOT EXISTS is more efficient than a NOT IN, and that's what I implemented.

Your suggestion works flawlessly, and I thank you for the help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-16 : 10:55:04

Kristen you are absolutely right!
In our company we named the "not in()" as the EVIL because of poor performance if there are lots of values in the list.
The difference in performance i.e. problems with poor performance in queries was coming up since we changed from 2000 to 2005.
In SQL Server 2000 there was never a problem...
We have changed all queries to exists() and all is fine again.
In this thread I have missed to have a closer look and worried only about the incorrect syntax for that insert.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 11:33:56

Hehehe! Well ... I'm a "detail" person ... so all my colleagues tell me, anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources