Conditional Insert Question - SQL Server Forums

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

Transact-SQL (2000)

Conditional Insert Question

Author

Topic

glidin
Starting Member

1 Post

Posted - 2005-03-04 : 09:20:24

I'm cleaning up a data conversion mess that wasn't fully competed and came across this problem. Is there a way to insert records into FPD only if the "key" isn't already there. The key being PFID, FCPID, and SDID.

INSERT INTO FPD (PFID, FCPID, SDID)
SELECT PFID, FCPID, 1227 FROM FCP WHERE PFID IN
(SELECT PFID FROM PF WHERE PSID = 2 AND StateDOIID IN (2, 15, 16, 18, 19, 25, 26, 27, 30, 33, 36, 40, 44, 45))

Any help would be GREATLY appreciated! Thanks.

Brian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-04 : 09:49:04

Advice: if you are doing data conversions, don't list out values in SQL statements (i.e., your big IN() clause), put them in tables and join to them. Then you can use the same lists over and over, store translation values, and print nice reports listing different values. Trust me on this one -- it will make your life easier.

Same if you find yourself using CASE statemnts to convert values; i.e., CASE State WHEN 'MA' THEN 1 WHEN 'RI' THEN 2 ... etc -- I've seen it done far too many times because it seems easier at the time. instead, store OldValue/NewValue in a table and join to it. Use as many auxiliary lookup/filter/translation tables as possible during conversions.

Getting back to your questions, to add rows to a table that are not already there, I use a LEFT OUTER JOIN:

INSERT INTO DestTable ...
SELECT ...
FROM SourceTable A
LEFT OUTER JOIN DestTable B
ON A.ID = B.ID
WHERE B.ID is not Null

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 10:03:10

What Jeff said...and


  INSERT INTO FPD (PFID, FCPID, SDID)
       SELECT PFID, FCPID, 1227 
	 FROM FCP o
	WHERE PFID IN    ( SELECT PFID 
		             FROM PF 
		            WHERE PSID = 2 
		              AND StateDOIID IN (2, 15, 16, 18, 19, 25, 26, 27, 30, 33, 36, 40, 44, 45))
	  AND NOT EXISTS ( SELECT * 
			     FROM FPD i
			    WHERE o.PFID  = i.PFID
			      AND o.FCPID = i.FCPID
			      AND o.SDID  = i.SDID)

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources