T-SQL statement needs improvement
I have the following T-SQL code (in SQL 2000) that is functioning correctly:
INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
fcpoptext,
fcpopval,
fnorder,
fcpopacces)
SELECT CSP69.fcpopkey,
CSP69.fcpoptext,
CSP69.fcpopval,
CSP69.fnorder,
CSP69.fcpopacces
FROM M2MData69..CSPOPUP CSP69
WHERE CSP69.fcpopkey = 'SHIPVIA'
AND NOT EXISTS
(SELECT CS01.identity_column
FROM m2mdata01..cspopup CS01
WHERE CS01.identity_column = CSP69.identity_column)
There just needs to be a more elegant way to do it. I am referencing the same table in two different ways in the same script here.
Any suggestions?
+2
source to share
2 answers
Like this?
INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey,
fcpoptext,
fcpopval,
fnorder,
fcpopacces)
SELECT CSP69.fcpopkey,
CSP69.fcpoptext,
CSP69.fcpopval,
CSP69.fnorder,
CSP69.fcpopacces
FROM M2MData69..CSPOPUP CSP69
LEFT
JOIN m2mdata01..cspopup CS01
ON CSP69.identity_column = CS01.identity_column
WHERE CSP69.fcpopkey = 'SHIPVIA'
AND CS01.indentity_column IS NULL
+2
source to share
You can do it like a left join where the result of the join is null:
SELECT CSP69.fcpopkey,
CSP69.fcpoptext,
CSP69.fcpopval,
CSP69.fnorder,
CSP69.fcpopacces
FROM M2MData69..CSPOPUP CSP69
LEFT JOIN m2mdata01..cspopup CS01 on SC01.indentity_column = CSP69.identity_column
WHERE CSP69.fcpopkey = 'SHIPVIA'
AND CS01.identity_column is null
Although, in my experience, performance will bring the end result more than the niceness of the code. You can also do it not in:
SELECT CSP69.fcpopkey,
CSP69.fcpoptext,
CSP69.fcpopval,
CSP69.fnorder,
CSP69.fcpopacces
FROM M2MData69..CSPOPUP CSP69
WHERE CSP69.fcpopkey = 'SHIPVIA'
AND CP69.identity_column not in
(select identity_column from m2mdata01..cspopup)
+2
source to share