check if a certain value for a combined prim key

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)

check if a certain value for a combined prim key

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-07-20 : 04:28:54

Hello,

just would like to write into a table (target) the combined primary key of another table (source), if the value of the key does not exsit in the target table.

source:tblIMISTravelSum
tarbet: tblIMISTravelExtras
combined primary key: [TravelID]+[FiscalYear]

INSERT INTO tblIMISTravelExtras ([TravelID],[FiscalYear])
SELECT [TravelID],[FiscalYear] FROM tblIMISTravelSum
WHERE [TravelID],[FiscalYear] NOT IN (SELECT [TravelID],[FiscalYear] FROM tblIMISTravelExtras)

how is the right syntax, is there a special function to check values of primary keys

thank you!

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-20 : 04:33:07

One way of doing it is to use a left join and check for NULL in the joined table.


INSERT INTO tblIMISTravelExtras ([TravelID],[FiscalYear])
        SELECT [TravelID],[FiscalYear] 
        FROM tblIMISTravelSum A
             LEFT JOIN tblIMISTravelExtras B ON A.TravelID=B.TravelID AND A.FiscalYear=B.FiscalYear
        WHERE B.TravelID IS NULL

vux
Starting Member

45 Posts

Posted - 2004-07-20 : 05:09:31

hey that s a great idea - i am to new to this sql programming, don't get these ideas...

unfortunately now the a error message comes up saying ambiguous colum names...

i put brackets around the colum names, but still not parsing...

INSERT INTO tblIMISTravelExtras ([TravelID],[FiscalYear])
SELECT [TravelID],[FiscalYear]
FROM tblIMISTravelSum A
LEFT JOIN tblIMISTravelExtras B ON A.[TravelID]=B.[TravelID] AND A.[FiscalYear]=B.[FiscalYear]
WHERE B.[TravelID] IS NULL

vux
Starting Member

45 Posts

Posted - 2004-07-20 : 06:09:10

ah ko just got it fixed

must be select A.travelid, A.FiscalYear of course

thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources