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

Question

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.

Khalid

Answer 1

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.

Snuggle

The second part of this statement is incorrect - MSSQL matches the column names. – Nagle

Keep in mind when you're commenting on a 12 year old answer that there may have been some changes over the intervening versions. :) – Snuggle

Answer 2

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

Octaviooctavius

That's code duplication I want to get rid of in favour of convention-driven approach (columns with same names should just map to each other) – Khalid

SQL is generally a verbose language. For Example the group by clause is redundant in aggregatations, INTO is redundant. I think you just have to accept that. – Ramonitaramos

This is a good practice because database systems cannot track dependencies in client code. One can add a column to T2, or it can reorganize T2 to change column order, or it can remove a column, all operations resulting in breaking your code. – Octaviooctavius

This is not code duplication. Code duplication would be INSERT INTO T1 (C1, C2) SELECT C1 C1, C2 C2 FROM T2 (column aliases). I also thought that SQL would try to match columns based on the column aliases I defined, but instead it only cares about the order of the columns in your SELECT. You do the bindings in the INTO (destCol1, destCol2) part. – Knucklebone

@Ramonitaramos The GROUP BY clause in aggregations is not necessarily redundant. The GROUP BY can contain columns that aren't in the select list at all. If I had a table of with FirstName, LastName, State I could have the following query: SELECT FirstName, COUNT(*) FROM MyTable GROUP BY FirstName, State – Fridge

Answer 3

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.

Horology

Is there any way to insert all columns except the identity key without specifying all column names? Tables I work with contain dozens of columns and listing them every time is really boring. – Khalid

Maybe you could create a view with all the columns you want and select * from it. – Ramonitaramos

@Konstantin: No, there is no simple way to specify all fields except the identity field. It's all or nothing. – Horology

This works as long as the columns are exactly the same – Kab

Answer 4

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.

Implied

Answer 5

Why not simply

INSERT INTO t1
SELECT * FROM T2

Anacoluthon

I need to skip one column (the one which is identity primary key) – Khalid

Answer 6

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

Salo

Is it possible to have alias for a number of columns? For example, insert into T1 ALL_COLUMNS_ALIAS select (C1, C2, C3) as ALL_COLUMNS_ALIAS from T2 ? – Khalid

Answer 7

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

Statuette

Answer 8

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

Sachikosachs

Answer 9

DECLARE @ColumnList AS NVarChar(MAX) = '' ; DECLARE @SQL NVarChar(MAX) = '' ;

SET @ColumnList = ( SELECT * FROM ( SELECT DISTINCT '[' + COLUMN_NAME + '], ' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE_ONE' AND COLUMN_NAME NOT IN ('OMIT_SOMETHING') FOR XML PATH ('') ) c (Columns) ) ;

SET @ColumnList = SUBSTRING(@ColumnList, 1, LEN(@ColumnList)-1) -- TRIM THE LAST COMMA

SET @SQL = CONCAT ( 'INSERT INTO TABLE_ONE (', @ColumnList, ')', ' SELECT ', @ColumnList, ' FROM TABLE_TWO WITH (NOLOCK)', ' WHERE YOUR_LIMITING_COLUMN = ', CHAR(39), 'SOMETHING', CHAR(39) ) ;

SELECT @SQL ; -- THIS WILL SHOW YOU WHAT HAS BEEN BUILT -- EXEC (@SQL); -- THIS WILL EXECUTE

Lait

This will allow you to use metadata to match columns by name or in same order. You will need to shape to your needs; but this will give you the framework to do any number of functions within your code. – Lait

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Knobkerrie

Answer 10

It is Referenced as follows:

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

Jonette