question

mike jun 1 avatar image
mike jun 1 asked

insert records from one table to another

there 2 identical tables and one has more records than another. I want to insert those records to the table that has less records.

USE GIS GO INSERT INTO Xcobadds SELECT * FROM Xcobadds_V WHERE Xcobadds.UID_NUM <> Xcobadds_V.UID_NUM

however I got error message like this.

Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "Xcobadds.UID_NUM" could not be bound.

insert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered
INSERT INTO Xcobadds             
SELECT *             
FROM Xcobadds_V V            
WHERE NOT EXISTS            
(            
SELECT *            
FROM   Xcobadds X            
WHERE X.UID_NUM = V.UID_NUM            
)            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jay D avatar image
Jay D answered

Try an OUTER JOIN to determine the missing rows... You're SQL should look something like this:

INSERT INTO Xcobadds(fieldName1, fieldName2, fieldName3)            
SELECT a.FieldName1, a.FieldName2, a.FieldName3             
FROM Xcobadds_V as a            
FULL OUTER JOIN Xcobadds as b            
ON a.UID_NUM = b.UID_NUM            
WHERE b.UID_NUM IS NULL            

To see an example copy/paste the following SQL:

CREATE TABLE #demo1            
(            
      UID_NUM INT NOT NULL IDENTITY            
    , DATA  VARCHAR(25)            
)            
            
CREATE TABLE #demo2            
(            
      UID_NUM INT NOT NULL IDENTITY            
    , DATA  VARCHAR(25)            
)            
            
INSERT INTO #demo1 VALUES ('Data Sample 1')            
, ('Data Sample 2')            
, ('Data Sample 3')            
, ('Data Sample 4')            
            
INSERT INTO #demo2 VALUES ('Data Sample 1')            
, ('Data Sample 2')            
            
            
SELECT UID_NUM AS UID_OriginalD1, DATA AS DATA_OriginalD1 FROM #demo1            
            
SELECT UID_NUM AS UID_OriginalD2, DATA AS DATA_OriginalD2 FROM #demo2            
            
SET NOCOUNT ON            
SET IDENTITY_INSERT #demo2 ON            
INSERT INTO #demo2 (UID_NUM, DATA)            
            
SELECT a.UID_NUM, a.DATA FROM #demo1 as a            
FULL OUTER JOIN #demo2 as b            
ON a.UID_NUM = b.UID_NUM            
WHERE b.UID_NUM IS NULL            
            
SELECT UID_NUM AS UID_NewD1, DATA AS DATA_NewD1 FROM #demo1            
            
SELECT UID_NUM AS UID_NewD2, DATA AS DATA_NewD2 FROM #demo2            
            
DROP TABLE #demo1            
DROP TABLE #demo2            

Will find the rows that don't exist in the 2nd table that are in the 1st... resulting in

UID_OriginalD1 DATA_OriginalD1            
-------------- -------------------------            
1              Data Sample 1            
2              Data Sample 2            
3              Data Sample 3            
4              Data Sample 4            
            
UID_OriginalD2 DATA_OriginalD2            
-------------- -------------------------            
1              Data Sample 1            
2              Data Sample 2            
            
UID_NewD1   DATA_NewD1            
----------- -------------------------            
1           Data Sample 1            
2           Data Sample 2            
3           Data Sample 3            
4           Data Sample 4            
            
UID_NewD2   DATA_NewD2            
----------- -------------------------            
1           Data Sample 1            
2           Data Sample 2            
3           Data Sample 3            
4           Data Sample 4            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.