CODEWITHSUNDEEP
sql-server
davidjwest
davidjwest

Reputation: 546

SQL Server Merge Two Tables

I have two slightly different tables:

Table 1:

    Sm Reads Oct
Install, Date, Con, MT

Table 2:

Sm Reads
Contract, Inst, Read T, Date

New Table:

Sm Reads Master
Contract, Install, Date, Type

Code:

CREATE TABLE [Sm Reads Master]
(
    [Contract] FLOAT,
    [Install] FLOAT,
    [Date] DateTime,
    [Type] NVARCHAR (3)
)
GO

INSERT INTO [Sm Reads Master]
SELECT  *
FROM    (
        SELECT * FROM [Sm Reads Oct]
        UNION
        SELECT * FROM [Sm Reads]
        ) LU

I suppose it doesn't like the fact that the two tables have different field names and different data, so is it even possible to do this?

Thanks for any help.

(MT in the first table is the same data as Read T in the second)

Upvotes: 0

Views: 47

Answers (2)

UJS
UJS

Reputation: 861

Your union query is not correct .Column Name must be same and also in same sequence

Try following script : 

INSERT INTO [Sm Reads Master] ([Contract],[Install],[Date],[Type])
SELECT [Contract] = Con,
                [Install]  = Inst, 
                [Date],
                [Type]     = MT
        FROM [Sm Reads Oct]

        UNION

        SELECT  [Contract],
                [Install], 
                [Date],
                [Type]
        FROM [Sm Reads]

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460028

You need the same column-names (and types):

INSERT INTO [Sm Reads Master]
SELECT * FROM   
FROM    (
        SELECT  Contract = Con,
                Install  = Inst, 
                Date,
                Type     = MT
        FROM [Sm Reads Oct]

        UNION

        SELECT  Contract,
                Install, 
                Date,
                Type
        FROM [Sm Reads]
        ) LU

Upvotes: 1

Related Questions