none
Insert rows from one table into another table

    Question

  • I need to insert all columns of all rows in one table that do not exist in another table. How can this be achieved without specifying all individual columns.

    Example:

    Instead of:

    insert

     

    into Table1 col1,col2,col3
    select col1,col2,col3 from Table2
    where Table2_Key not in (select Table1_Key from Table1)

    I am looking for something like

    insert into Table1 *
    select * from Table2
    where Table2_Key not in (select Table1_Key from contact
    )

     

     

     

     

     

    Any advice?


    Baruch Barness
    Monday, February 21, 2011 8:13 PM

Answers

  • You do not need to specify column names in insert into unless you have an identity columns that you want to exclude

     for e.g.

    create table t1
    (
    	 
     col1 varchar(10) not null, 
     col2 varchar(10) not null
    )
    Go
    insert into t1
    select '1', '1'
    union
    select '2', '2'
    
     
    
    create table t2
    (
    	 
    	col1 varchar(10) not null, 
     col2 varchar(10) not null
    )
    Go
    
     
     
    insert into t2
    select * from t1 
    
    
    • Proposed as answer by Naomi NModerator Monday, February 21, 2011 8:36 PM
    • Marked as answer by Barucho1 Monday, February 21, 2011 10:13 PM
    Monday, February 21, 2011 8:25 PM

All replies

  • You do not need to specify column names in insert into unless you have an identity columns that you want to exclude

     for e.g.

    create table t1
    (
    	 
     col1 varchar(10) not null, 
     col2 varchar(10) not null
    )
    Go
    insert into t1
    select '1', '1'
    union
    select '2', '2'
    
     
    
    create table t2
    (
    	 
    	col1 varchar(10) not null, 
     col2 varchar(10) not null
    )
    Go
    
     
     
    insert into t2
    select * from t1 
    
    
    • Proposed as answer by Naomi NModerator Monday, February 21, 2011 8:36 PM
    • Marked as answer by Barucho1 Monday, February 21, 2011 10:13 PM
    Monday, February 21, 2011 8:25 PM
  • If both tables have identical structures and you don't have identity columns, then you can use *. Otherwise there is no other alternative than list explicitly all columns. In fact, listing all columns always is considered a best practice.

    You can generate the INSERT statement in SSMS.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, February 21, 2011 8:37 PM