T-SQL Statement needs improvement.

Question

I have the following T-SQL code (in SQL 2000) which functions 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 has to be a more elegant way of doing it. I'm referencing the same table two different ways in the same script here.

Any suggestions?

Answer 1

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

Answer 2

You could do it as a left join where the join result 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 drive the end result more than the niceness of the code. You could also do a 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)