Insert Unique Entries from one table to another

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

Insert Unique Entries from one table to another

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-10-27 : 08:59:33

Table A

Id - PK
FirstName
LastName
UserID
SiteNum
Div_Nbr
Region
District
Job_Num
Job_Desc
SupervisorID
PositionNum

Table B

Emplid – PK
Id
First_Name
Last_Name
Job_Code
Title
Site_Num
Supv_ID
POS_NUM

I Need to enter all records in Table B that have Id’s that are not found in Table A
Based on
Table A.Id and Table B.Id

I tried

DECLARE @ID int

DECLARE duplicate_cursor CURSOR FOR
SELECT ID
FROM TABLE_B

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO TABLE_A
(ID,FIRSTNAME,LASTNAME,JOB_NUM,JOB_DESC, SUPERVISOR_ID, POSITION_NBR)
SELECT ID, FIRST_NAME, LAST_NAME, JOB_CODE, TITLE, SUPV_ID, POS_NBR
FROM TABLE_B
WHERE WIN_NBR <> @WIN

FETCH NEXT FROM duplicate_cursor INTO @ID
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor

However It keeps telling me I am violating the Primary Key rules of Table A as I am inserting duplicate records.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 09:16:40

I feel that You don't need a cursor for it.

Try after substituting the column names the below statement.

Insert into tableb (columnnames)
Select columnnames from tablea
where id not in
(select id from TableB)

Now the reason why you are getting the error:
You are getting error because you are running the insert statement in a loop. For first time it may succeed but in second iteration it will fail.

Please correct me if my understanding is wrong.