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 INTO Xcobadds
SELECT *
FROM Xcobadds_V V
WHERE NOT EXISTS
(
SELECT *
FROM Xcobadds X
WHERE X.UID_NUM = V.UID_NUM
)
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
