none
How to insert multiple values in the single INSERT statement?

    Question

  • How to insert multiple values in the single INSERT statement?
    e.g.
    INSERT INTO tblName(FirstName, LastName)
    VALUES( 'Peter','Smith')
    VALUES('Derek','John')
    VALUES('A','B')
    ......

    or I  have to repeat the "INSERT INTO tblName(FirstName, LastName)" ?

    Cheers





    rookie
    Thursday, January 01, 2009 9:57 AM

Answers

  •  You can do it in SQL Server 2008 with the brand new INSERT with multiple VALUES.   I am afraid you have to go one by one for previous versions. Example:

    use tempdb;  
     
    select   FirstName, LastName   
    into  PersonInfo  
    from AdventureWorks2008.Person.Person  
    go  
     
    alter table PersonInfo add ID int identity(1,1) primary key 
    go  
     
    insert PersonInfo (FirstName, LastName)  
    values 
            ('Elvis''Presley'),  
            ('Doris''Day'),  
            ('Frank''Sinatra'),  
            ('Julie''Andrews'),  
            ('Britney''Spears')  
    go  
     
    select top(5) * from PersonInfo order by ID desc 
    go  
     
    /* Results  
     
    FirstName   LastName    ID  
    Britney     Spears      19977  
    Julie       Andrews     19976  
    Frank       Sinatra     19975  
    Doris       Day         19974  
    Elvis       Presley     19973  
    */ 

    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Thursday, January 01, 2009 10:23 AM
  • You have to repeat the INSERT INTO statement every time. You have another possibility though:

     INSERT INTO tblName(FirstName, LastName)
    SELECT 'Peter','Smith' UNION ALL
    SELECT 'Derek','John' UNION ALL
    SELECT 'A','B' 
    Ewald - Please remember to mark the replies as answers if they help.
    Thursday, January 01, 2009 10:14 AM

All replies

  • You have to repeat the INSERT INTO statement every time. You have another possibility though:

     INSERT INTO tblName(FirstName, LastName)
    SELECT 'Peter','Smith' UNION ALL
    SELECT 'Derek','John' UNION ALL
    SELECT 'A','B' 
    Ewald - Please remember to mark the replies as answers if they help.
    Thursday, January 01, 2009 10:14 AM
  •  You can do it in SQL Server 2008 with the brand new INSERT with multiple VALUES.   I am afraid you have to go one by one for previous versions. Example:

    use tempdb;  
     
    select   FirstName, LastName   
    into  PersonInfo  
    from AdventureWorks2008.Person.Person  
    go  
     
    alter table PersonInfo add ID int identity(1,1) primary key 
    go  
     
    insert PersonInfo (FirstName, LastName)  
    values 
            ('Elvis''Presley'),  
            ('Doris''Day'),  
            ('Frank''Sinatra'),  
            ('Julie''Andrews'),  
            ('Britney''Spears')  
    go  
     
    select top(5) * from PersonInfo order by ID desc 
    go  
     
    /* Results  
     
    FirstName   LastName    ID  
    Britney     Spears      19977  
    Julie       Andrews     19976  
    Frank       Sinatra     19975  
    Doris       Day         19974  
    Elvis       Presley     19973  
    */ 

    Kalman Toth, SQL Server 2005 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Thursday, January 01, 2009 10:23 AM
  • Have they really introduced that syntax in 2008?
    :D Yey!

    George
    Friday, January 02, 2009 9:22 AM
  • Yes, it is added feature in Sql Server 2008 something called as Row Value Constructor.

    And it is not only helpful in Insert statement but in update and  where IN clause as well.

    Now you can write a UPDATE statement like

    update Table1  
    set (col1, col2) = (select col3, col4 from Table2 where col3 = Table1.col1)  
     


    Or a where condition as
    SELECT * 
    FROM Table1  
    where (col1, col2) in (select col3, col4 from Table2) 

    Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)
    Friday, January 02, 2009 9:35 AM
  • Mangal,

    Unfortunately, that syntax (Row Value Constructors in UPDATE statement or WHERE clause)  is not in SQL Server 2008.

    Currently (in SQL Server 2008), you can use Row Value Constructors only in the INSERT statement (as specified above) and in the FROM clause, for example:

    SELECT * FROM (VALUES (1,'A'), (2,'B')) AS T(X,Y);

    Razvan
    Friday, January 02, 2009 9:58 AM
  • Oh,

    Actauly I never tried that, but read some time back on http://decipherinfosys.wordpress.com/2007/08/19/row-value-constructor-support-in-sql-server-2008/.  So thought it has been added.
    Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)
    Friday, January 02, 2009 12:02 PM