SQL: outer join two tables on String and insert similar into another

Question

I have two tables A and B with 30 columns (same variable names, data for different year), no primary key, almost a million records each.

I want to compare A.X1 with B.X1 (nvarchar8, contains spaces, -, letter and numbers) and insert the outer join results in another table C (with same 30 columns) so I have all rows of A and where B!=A on B.X1).

Example:

Table A

X1   X2   X3 ..... X30
11   E     R .....  G
12   R     4        L
13  S      5        NULL   
14  D      T        NULL

Table B

X1   X2   X3 ..... X30
11   E     R .....  G
12   R     4        L
15  R2     56        NULL   
16  R1      T1        NULL

Resulting table C

X1   X2   X3 ..... X30
11   E     R .....  G
12   R     4        L
13  S      5        NULL   
14  D      T        NULL
15  R2     56        NULL   
16  R1      T1        NULL

How do I do that.

I tried

INSERT INTO C
SELECT *
from A
full outer join B
on A.X1 = B.X1

Error I get

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

I have C created, which is currently empty.

And you should also always specify the column on the `INSERT` clause to avoid any surprises and problems! — marc_s, Aug 01 '13 at 16:11

Andomar · Answer 1 · 2013-08-01T16:19:38.340

1

insert  C
select  *
from    A
union all
select  *
from    B
where  not exists
       (
       select  *
       from    A
       where   X1 = B.X1
       )

edited Aug 01 '13 at 16:19

answered Aug 01 '13 at 16:11

Andomar

232,371
49
380
404

score 0 · Answer 2 · answered Aug 01 '13 at 16:16

Your query will not work becasue you are joining the tables and retunring *, with ill results in twice the number of columns that you need. What you really want to do is select everything from table A then APPEND (rather than join) all the records from table B. Appends are achieved by using a UNION. Here is some sample code. Note: never, ever use SELECT *. Addapt the follwing to include the specifically named fields in the correct order.

Also, I am using UNION rather than UNION ALL so that the query automaticlly excludes the records in B that are duplicates of records in A.

SELECT FIELDS...
FROM TABLEA
UNION
SELECT SAME_FIELDS...
FROM TABLEB

score -1 · Answer 3 · answered Aug 01 '13 at 16:10

-1

Insert Into TableC
(
  -- List your fields explicitly
)
Select
    -- List all tableA.Fields explicitly
From tableA
Left Outer Join tableB On tableB.X1 = tableA.X1
Where tablB.X1 IS NULL

answered Aug 01 '13 at 16:10

ganders

7,285
17
66
114

1

An outer join between A and B will result in a table of 60 columns width. C has just 30 columns. – Andomar Aug 01 '13 at 16:11
That's why I said "List all tableA.Fields explicitly". Thanks for the downvote. – ganders Aug 01 '13 at 16:21
This kind of operation requires a union, not a join. You're missing the point. And it's not my downvote. – Andomar Aug 01 '13 at 16:28

SQL: outer join two tables on String and insert similar into another

3 Answers3