Automatically match columns in INSERT INTO ... SELECT ... FROM

SQL Server question. When doing

INSERT INTO T1 SELECT (C1, C2) FROM T2

I don开发者_开发知识库't want to specify column names of T1 because they are the same as in T2

Is it possible to do so?

Currently I'm getting error

Msg 213, Level 16, State 1, Line 1

Column name or number of supplied values does not match table definition.

Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don't want to, you should:

INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2

Yes, you can omit the field names for the table that you insert to, and you can use select * to get all fields from the table, but I would not recommend this approach.

If you omit the field name the fields are matched by position, not by name. If the fields are not in the exact same order, they will be mixed up. Generally you should avoid relying on the exact layout of the tables, to minimise the risk that changes in the tables breaks the queries.

If T1 and T2 match exactly you have two choices. You can either select all columns from T2 for the insert into T1, or you can provide a column list to the insert statement.

Even though when you do a select MSSQL provides column headers that information is not used by an insert statement to match columns up.

The other answers are good but they don't explain why it is bad to use:

INSERT INTO T1
SELECT * FROM T2

In a comment the OP talks about code duplication when specifying columns when using the safer approach:

INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2

Yet if you don't be specific you are relying upon the number of columns always matching and the ordering of the columns doing what you expect. That logic will break if one of the tables is altered to add a column.

Also you can get into trouble with silent bugs. If you use a tables with the same number of columns but different positions:

CREATE TABLE tab1 (col1 int, col2 string);

CREATE TABLE tab2 (col1 string, col2 int);

INSERT INTO tab1 values(1, 'aaa');

INSERT INTO TABLE tab2 select * FROM tab1;

Then you might have hoped that you did a copy such that tab1 and tab2 are the same. What I wanted was:

+-------------------+-------------------+
| tab2.col1         | tab2.col2         |
+-------------------+-------------------+
| 1                 | aaa               |
+-------------------+-------------------+

But it will load based on column position and cast the data so what I get is:

+-------------------+-------------------+
| tab2.col1         | tab2.col2         |
+-------------------+-------------------+
| 1                 | NULL              |
+-------------------+-------------------+

What happened was it could not convert a string into int so it set it to NULL. It could convert the int into a string as '1' which is no longer a number type.

Even if the columns do match anyone can do:

ALTER TABLE tab1 ADD COLUMNS (col3 string COMMENT 'a new column');

After that the query that does not specify columns will break saying that the number of columns in the two tables does not match. It will no longer be able to move data into tab2.

This means that the safe thing to do is to be explicit with SQL:

INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2

If someone is just trying to quickly take a copy of a table then some SQL engines support

CREATE TABLE tab3 AS SELECT * FROM tab1;

In that case sure typing columns is a waste of time and if someone was to add columns to tab1 before you cloned it being explicit would fail to clone the new column. All that counter example shows is that there are no absolute rules in programming only rules of thumb. The rule of thumb for SQL (and any other loosly typed language with implicit conversions) is to be as specific as you can if you don't want silent error at runtime and bugs when someone adds new functionality down the line.

Why not simply

INSERT INTO t1
SELECT * FROM T2

If you're worried about column names you can always alias them:

INSERT INTO T1 (C1, c2)
SELECT C1 AS C1_ALIAS, C2 AS C2_ALIAS FROM T2

Or, more succinctly:

INSERT INTO T1 (C1, c2)
SELECT C1 C1_ALIAS, C2 C2_ALIAS FROM T2

Though I can't really think why one would want to in such a simple example

First select this sql, chose your table line from sql result and change the target or source tablename. If tables has same columns (same order is not necessary) it will be work.

 with xparams as (      select (select user from dual) "OWNER", '' "ADDSTRTOFROMTABLENAME" from dual  )
  ,t1 as (  SELECT dbat.table_name from dba_tables dbat, xparams where dbat.owner = xparams.OWNER )
  ,t1c1 as (  SELECT utcs.table_name ,  LISTAGG(utcs.column_name,',') within group (order by utcs.column_name) "COLS"  from USER_TAB_COLUMNS utcs, t1 where utcs.table_name = t1.table_name group by utcs.table_name )
  ,res1 as ( SELECT 'insert into '|| t1c1.table_name || ' ( '|| t1c1.COLS ||') select '|| t1c1.COLS || ' from ' || t1c1.table_name||xparams.ADDSTRTOFROMTABLENAME ||';' "RES" from t1c1, xparams order by t1c1.table_name )
select * from res1

I use a procedure for this situation, it really helps in the right places

Small proc (Just to drop tables)

CREATE PROC MF_DROP (@TEMP AS VARCHAR(100)) AS
    EXEC('IF OBJECT_ID(''TEMPDB.DBO.' + @TEMP + ''') IS NOT NULL DROP TABLE ' + @TEMP)

This code bellow have many options on it, but the basic of it is to "match an insert", new columns are going to be automatically considered and you can use the option to create new columns when they dont exists in the target table

The procedure has statements to get the user name and send e-mail just so we have an internal alert, it can be removed with no affects

CREATE PROC [dbo].[PRC_MATCH_INSERT] (
--declare       
            @FROM AS VARCHAR(100)
= NULL /*
= '#A' --*/
            , @INTO AS VARCHAR(100)
= NULL /*
= '#B' --*/
            , @DBFROM AS VARCHAR(100) = NULL
            , @DBINTO AS VARCHAR(100) = NULL
            , @SC_OUTPUT AS NVARCHAR(MAX) = NULL
OUTPUT
            , @EXEC AS BIT = 1
            , @MERGE AS BIT = 0
            , @CREATE_COLUMNS AS BIT = 0
            , @CALLER SQL_VARIANT = NULL
            , @EXCEPT VARCHAR(MAX) = NULL
            , @ONLY_ALTER BIT = 0
            , @DIE varchar(100) = ''
) AS BEGIN
 
            --Generates script to insert columns from one table to another in order and may even create the columns Vathaire 11/10/2019
 
    IF @FROM + @INTO IS NULL BEGIN
        PRINT '
Generates script to insert columns from one table to another in order and may even create the columns
@FROM: Table name of the "FROM" statement
@INTO: Table name of the "INTO" statement
@DBFROM, @DBINTO: Database names of these tables, optional IF they are from TEMPDB or DB_NAME()
@SC_OUTPUT: To get the template of this insert (only output)
@EXEC: "1" is going to execute (Default), "0" isnt
@MERGE: Create "MERGE" template
@CREATE_COLUMNS: If is missing columns in the @INTO that exists in the @FROM, option "1" will create the new columns and insert (Default 0)
@CALLER: Send the @@PROCID or a identification name, it is goint to be used in the email if new columns are going to be created
@EXCEPT: Name of columns that doesnt need to be created, comma separated
@ONLY_ALTER: "0" will alter table and insert (default), "1" will only alter
@DIE: "Drop (column) If Exists", drop a dummy column like "_$"

'
        RETURN
    END
    /*
    
        --Test example
    
    EXEC MF_DROP #A
    EXEC MF_DROP #B
    CREATE TABLE #A (A INT, B INT, C INT, D INT, E INT)
    CREATE TABLE #B (E INT, D INT, C INT, B INT, F INT)
 
    INSERT INTO #A VALUES (1, 2, 3, 4, NULL)
    
    DECLARE @FROM AS VARCHAR(100) = '#A'
            , @INTO AS VARCHAR(100) = '#B'
            , @DBFROM AS VARCHAR(100)
            , @DBINTO AS VARCHAR(100)
            , @SC_OUTPUT AS NVARCHAR(MAX)
            , @EXEC AS BIT = 1
    SELECT * FROM #A
    SELECT * FROM #B --*/
 
    DECLARE @Q AS NVARCHAR(MAX), @QE AS NVARCHAR(MAX)
    
    SELECT
          @DBFROM = ISNULL(@DBFROM, CASE WHEN LEFT(@FROM, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END) --Changing the default database
        , @DBINTO = ISNULL(@DBINTO, CASE WHEN LEFT(@INTO, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END)
    
    EXEC MF_DROP #FROM
    EXEC MF_DROP #INTO
    CREATE TABLE #FROM (I INT, COL VARCHAR(1000), USED BIT, TP NVARCHAR(MAX))
    CREATE TABLE #INTO (I INT, COL VARCHAR(1000), USED BIT)
    
    SET @Q = 'INSERT INTO |INTO|
        SELECT
            COLUMN_ID
            , QUOTENAME(NAME)
            , 0
            /*TYPE*/
            , UPPER(type_name(system_type_id))
            + CASE
                WHEN type_name(system_type_id) IN (''VARCHAR'', ''CHAR'', ''NCHAR'', ''NVARCHAR'') THEN ''('' + REPLACE(CONVERT(VARCHAR(10), max_length), ''-1'', ''MAX'') + '')''
                WHEN type_name(system_type_id) IN (''DECIMAL'', ''NUMERIC'') THEN ''('' + CONVERT(VARCHAR(10), precision) + '', '' + CONVERT(VARCHAR(10), scale) + '')''
                ELSE ''''
            END --*/
        FROM |DB|.SYS.COLUMNS
        WHERE OBJECT_ID = OBJECT_ID(''|DB|.DBO.|TBL|'')
            --COMPUTED AND is_computed = 0 --Ignore computed columns that is going to be inserted Vathaire 11/05/2021'
    SET @QE = REPLACE(REPLACE(REPLACE(@Q, '|INTO|', '#FROM'), '|DB|', @DBFROM), '|TBL|', @FROM)
    EXEC SP_EXECUTESQL @QE
    SET @QE = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Q, '|INTO|', '#INTO'), '|DB|', @DBINTO), '|TBL|', @INTO), '/*TYPE*/', '/*TYPE'), '--COMPUTED', '')
    EXEC SP_EXECUTESQL @QE
 
    EXEC MF_DROP #RESULT
    SELECT
        CFROM = A.COL
        , CINTO = B.COL
        , A.TP
    INTO #RESULT
    FROM #FROM A
    FULL OUTER JOIN #INTO B ON A.COL = B.COL
 
        --Create non existing columns to the insert Vathaire 15/01/2020
    
    SET @SC_OUTPUT = ''
    IF @CREATE_COLUMNS = 1 BEGIN
        EXEC MF_DROP #PRC_MATCH_INSERT_CREATE_COLUMNS
        SELECT CFROM, TP INTO #PRC_MATCH_INSERT_CREATE_COLUMNS FROM #RESULT
        WHERE CINTO IS NULL
            AND ISNULL(',' + @EXCEPT + ',', '') NOT LIKE '%,' + REPLACE(REPLACE(CFROM, '[', ''), ']', '') + ',%' --Option of columns to not create Vathaire 13/02/2020
        IF EXISTS (SELECT TOP 1 1 FROM #PRC_MATCH_INSERT_CREATE_COLUMNS) BEGIN
            DECLARE @NEW_COLUMNS NVARCHAR(MAX)
            SET @NEW_COLUMNS =
                STUFF((SELECT ', ' + CFROM + ' ' + TP + '\r\n'
                    FROM #PRC_MATCH_INSERT_CREATE_COLUMNS
                        FOR XML PATH('')), 1, 1, '')
 
            IF @EXEC = 1 BEGIN
                IF ISNULL(@CALLER, '') != 'No Mail' BEGIN --For routine procedures that already it's know new columns will be created Vathaire 09/03/2020
                    DECLARE @EMAIL NVARCHAR(MAX) = CONVERT(VARCHAR(2000), 'Caller procedure' + ISNULL(
                        ': ' + CASE WHEN ISNUMERIC(CONVERT(VARCHAR(1000), @CALLER)) = 1 THEN OBJECT_NAME(CONVERT(INT, @CALLER)) ELSE CONVERT(VARCHAR(2000), @CALLER) END
                        , ' is not identified') + '<BR><BR>User: ' + SYSTEM_USER + '<BR><BR>')
                    
                    SET @EMAIL = 'Dears, the PRC_MATCH_INSERT is about to create new columns in the table  ' +
                        @DBINTO + '.dbo.' + @INTO + ' by a SELECT FROM ' + @DBFROM + '.dbo.' + @FROM + '<BR><BR>' +
                        'Check if the SPID wont be in lock <br><br>' +
                        @EMAIL +
                        'New columns:<BR><BR>' + REPLACE(@NEW_COLUMNS, '\r\n', '<BR>') + '<br><b>Team MIS</b><BR><BR>' --Signature
                    EXEC MSDB..SP_SEND_DBMAIL 
                         'your profile name'    -------------------------------
                        , @subject = 'New columns been created'
                        , @body = @EMAIL
                        , @body_format = 'html'
                        , @importance = 'high'
                        , @recipients = 'mail to' -------------------------------
                END
                SET @NEW_COLUMNS = REPLACE(@NEW_COLUMNS, '\r\n', CHAR(13))
                EXEC ('ALTER TABLE ' + @DBINTO + '.dbo.' + @INTO + ' ADD
                ' + @NEW_COLUMNS)
                
                --UPDATE #RESULT SET CINTO = CFROM WHERE CINTO IS NULL
                UPDATE R
                SET CINTO = B.CFROM
                FROM #RESULT R
                JOIN #PRC_MATCH_INSERT_CREATE_COLUMNS B ON R.CFROM = B.CFROM
                WHERE R.CINTO IS NULL
            END ELSE
                SET @SC_OUTPUT += CHAR(13) + 'ALTER TABLE ' + @DBINTO + '.dbo.' + @INTO + ' ADD
                    ' + @NEW_COLUMNS + CHAR(13)
        END
    END
 
    IF @ONLY_ALTER = 1 RETURN   
    SELECT
        @Q = STUFF((SELECT ', ' + CFROM FROM #RESULT WHERE CFROM IS NOT NULL AND CINTO IS NOT NULL FOR XML PATH('')), 1, 2, '') 
        , @Q = CASE
                WHEN @MERGE = 1 THEN 'INSERT (' + @Q + ') VALUES (' + @Q + ')'
                ELSE
                    'INSERT INTO ' + @DBINTO + '.DBO.' + @INTO + ' (' + @Q + ')
                        SELECT ' + @Q + ' FROM ' + @DBFROM + '.DBO.' + @FROM
            END
        , @Q = REPLACE(@Q, 'TEMPDB.DBO.', '')
        , @SC_OUTPUT += @Q
    if @DIE != '' --Drop (column) If Exists Vathaire 11/03/2022
        if COL_LENGTH('tempdb.dbo.' + @INTO, @die) is not null
            set @SC_OUTPUT += char(13) + char(13) + 'alter table ' + @INTO + ' drop column ' + @die
    IF @EXEC = 1
        EXEC SP_EXECUTESQL @SC_OUTPUT
    ELSE PRINT @SC_OUTPUT
END

Here some simple tests:

1: Just match insert

CREATE TABLE #A (A INT, B INT, C INT, D INT, E INT)
CREATE TABLE #B (E INT, D INT, C INT, B INT, F INT)
 
INSERT INTO #A VALUES (1, 2, 3, 4, NULL)
    
EXEC [PRC_MATCH_INSERT]
        @FROM   = '#A'
        , @INTO     = '#B'
        
SELECT * FROM #A
SELECT * FROM #B

2: Now adding the new columns to the target (Configure the SP_SEND_DBMAIL first or remove it):

EXEC [PRC_MATCH_INSERT]
        @FROM   = '#A'
        , @INTO     = '#B'
        , @CREATE_COLUMNS=1
        
SELECT * FROM #A
SELECT * FROM #B

3: Fill a temporary table with data inside a proc WITHOUT specifying the column names

CREATE PROC #fillATable (@tbl varchar(100)) as begin

select top 100 * into #tbl_in_proc from sys.tables
exec PRC_MATCH_INSERT
    #tbl_in_proc
    , @tbl
    , @create_columns=1
    , @die='dummy_column'
    , @caller='no mail' --> i dont want to get an email from this proc
end
go
CREATE TABLE #external_table ( dummy_column int )
exec #fillATable #external_table
select * 
from #external_table

It is Referenced as follows:

INSERT INTO NEWTABLENAME COL1[,COL2,..COLN]
SELECT COL1[,COL2,..COLN] FROM THE EXISTINGTABLENAME