update a table with primary 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

General SQL Server Forums

New to SQL Server Programming

update a table with primary key

Author

Topic

gongxia649
So Suave

344 Posts

Posted - 2007-05-01 : 09:22:00

i have 2 tables. Table a and Table b. i need to insert only the new records from Table a to Table B

how do you update table B where the primary key is 4 columns.

solved.!

gongxia649
So Suave

344 Posts

Posted - 2007-05-01 : 09:29:00

and how do you create a primary using 4 columns on EM ss2k5?. 2k used to be able to select 4 columns and click on the key and bang!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-01 : 09:57:49

Right click the table in SSMS and choose design. In design table page, hold shift key while click on columns you like to put pkey on, then right click and choose set primary key.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-01 : 10:25:14

If I'm understanding you correctly:


INSERT INTO TableB
(   Col1, 
    Col2, 
    Col3, 
    Col4
    -- Other Columns
)
SELECT    a.Col1, 
          a.Col2, 
          a.Col3, 
          a.Col4 
          --Other Columns
FROM      TableA AS a
LEFT JOIN TableB AS b
ON        a.Col1 = b.Col1
  AND     a.Col2 = b.Col2
  AND     a.Col3 = b.Col3
  AND     a.Col4 = b.Col4
WHERE     b.Col1 IS NULL

Mark

gongxia649
So Suave

344 Posts

Posted - 2007-05-01 : 11:30:31

actually this is my answer. but when i run it twice. it shows this error. Im scheduling a sql job to run that code. But it fails since there are no new records to insert. how can i avoid this error.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.employees' with unique index 'ciemployees'.
The statement has been terminated.

insert jose.dbo.employees (jose.dbo.employeesID, jose.dbo.employeesFname, jose.dbo.employeesLname, jose.dbo.employeeszipCode, jose.dbo.employeesDOB, jose.dbo.employeesSalary)
select a.employeesID, a.employeesFname, a.employeesLname, a.employeeszipCode, a.employeesDOB, a.employeesSalary
from jose.dbo.employeesInformation a
join jose.dbo.employees b
on a.employeesID <> b.employeesID
and a.employeesFname <> b.employeesFname
and a.employeesLname <> b.employeesLname
and a.employeeszipCode <> b.employeeszipCode

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-01 : 11:40:21

Your code is wrong -- that logically makes no sense ... did you read what Mark wrote for you?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources