Problems inserting - 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

General SQL Server Forums

New to SQL Server Programming

Problems inserting

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 10:30:39

Hello all.

I am trying to take records from one table and insert them in a second table. The problem is that some of the values already exist in the second table so I cant insert. What i want to do is insert only if the record doesnt already exist. Here is the code i have so far (which works if there are none of the same in the second table)

INSERT INTO V_DEPT(CODE, DESCRIPTION)
SELECT DISTINCT DEPT, DEPT
FROM mockdownload_dd...['Job History$']

Thanks guys :)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 10:37:21

U can make use of "NOT EXISTS"

as in


INSERT INTO V_DEPT(CODE, DESCRIPTION)
SELECT DISTINCT DEPT, DEPT
FROM mockdownload_dd...['Job History$'] where PK_of_SourceTbl not exists (Select Code from V_Dept)

Srinika

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 10:51:08

Ah right. Thats great. Thanks very much.

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 11:21:18

Hi. I tried that code and got the following error. 'Incorrect syntax near the keyword 'exists'.'

I dont think it like the 'PK_of_SourceTbl' before the NOT EXISTS.

Thanks.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 11:27:58

Now I'm confused!

What did u say in ur earlier post ?

Nevermind, Can u post ur real query?
I think that u may have used 'PK_of_SourceTbl' rather than replacing that with the proper Field!!

Srinika

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-09 : 12:08:58

Hi, no i did replace the code. The code i have is:

INSERT INTO V_DEPT(CODE, DESCRIPTION)
SELECT DISTINCT DEPT, DEPT
FROM mockdownload_dd...['Job History$']
WHERE CODE NOT EXISTS (SELECT CODE FROM V_DEPT)

Thanks again

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 12:35:58

Oooooops,

Sorry

Either


INSERT INTO V_DEPT(CODE, DESCRIPTION)
SELECT DISTINCT DEPT, DEPT
FROM mockdownload_dd...['Job History$']
WHERE CODE NOT IN (SELECT CODE FROM V_DEPT)


INSERT INTO V_DEPT(CODE, DESCRIPTION)
SELECT DISTINCT DEPT, DEPT
FROM mockdownload_dd...['Job History$'] S
WHERE NOT EXISTS (SELECT CODE FROM V_DEPT where Code = S.code)

Srinika

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-10 : 04:10:19

Thats great. Thank you very much :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources