Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
INSERT INTO syntax

已答复 INSERT INTO syntax

  • Thursday, February 14, 2013 3:02 PM
     
     
    Sign In to Vote
    0

    Hello all,

    If the table structures are same, can I use the following for importing from one table to another,

    INSERT INTO table1

    SELECT * FROM table2

    Thanks in advance.

     

All Replies

  • Thursday, February 14, 2013 3:05 PM
     
     Answered
    Sign In to Vote
    0

    That should work.  if you identity columns you have to set identity insert on and list all the columns.

    if you do not have table already, you can select * into <<NewTable>> from Table2. 

    Hope it Helps!!

    • Marked As Answer by Ryan_XH Thursday, February 14, 2013 3:51 PM
    •  
     
  • Thursday, February 14, 2013 3:11 PM
    Moderator
     
     
    Sign In to Vote
    0

    You need to show your table DDL to get appropriate answer. 

    You can find detailed examples from this link:

    http://msdn.microsoft.com/en-us/library/ms174335.aspx

     
  • Thursday, February 14, 2013 3:12 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    If there's any Identity column on Table1, then it may fail.It's a good practice to include column names while inserting..

    INSERT INTO DB.SchemaName.Table1
(Col1,Col2...)
SELECT Col1,Col2...
FROM DB.SchemaName.Table2

    Narsimha

     
  • Thursday, February 14, 2013 3:14 PM
     
     
    Sign In to Vote
    0
    Thanks for the replies. There is no identity column, so I think its fine then.
     
  • Thursday, February 14, 2013 3:14 PM
     
     Answered
    Sign In to Vote
    0

    I'd suggest it would be better to spcify the colums

    Insert Into Table1 (col1, col2, col3)

    Select col1, col2, col3 From Table2

    Cheers

    Rui

    • Marked As Answer by Ryan_XH Thursday, February 14, 2013 3:51 PM
    •