Insert multiple rows WITHOUT repeating the INSERT INTO part of the statement

Question

I know I ve done this before years ago  but I can t remember the syntax  and I can t find it anywhere due to pulling up tons of help docs and articles about  bulk imports    Here s what I want to do  but the syntax is not exactly right    please  someone who has done this before  help me out     INSERT INTO dbo MyTable  ID  Name  VALUES  123   Timmy         124   Jonny         125   Sally     I know that this is close to the right syntax  I might need the word  BULK  in there  or something  I can t remember  Any idea   I need this for a SQL Server 2005 database  I ve tried this code  to no avail   DECLARE  blah TABLE       ID INT NOT NULL PRIMARY KEY      Name VARCHAR 100  NOT NULL    INSERT INTO  blah  ID  Name      VALUES  123   Timmy       VALUES  124   Jonny       VALUES  125   Sally    SELECT   FROM  blah   I m getting Incorrect syntax near the keyword  VALUES

User · Answer

Using INSERT INTO     VALUES syntax like in Daniel Vassallo s answer there is one annoying limitation      From MSDN      The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000   The easiest way to omit this limitation is to use derived table like   INSERT INTO dbo Mytable ID  Name  SELECT ID  Name  FROM      VALUES  1   a               2   b              --              -- more than 1000 rows  sub  ID  Name     LiveDemo   This will work starting from SQL Server 2008

User · Answer

Others here have suggested a couple multi-record syntaxes  Expounding upon that  I suggest you insert into a temp table first  and insert your main table from there   The reason for this is loading the data from a query can take longer  and you may end up locking the table or pages longer than is necessary  which slows down other queries running against that table   -- Make a temp table with the needed columns select top 0   into  temp from MyTable  nolock   -- load data into it at your leisure  nobody else is waiting for this table or these pages  insert  temp  ID  Name  values  123   Timmy     124   Jonny     125   Sally    -- Now that all the data is in SQL  copy it over to the real table  This runs much faster in most cases  insert MyTable  ID  Name  select ID  Name from  temp  -- cleanup drop table  temp   Also  your IDs should probably be identity 1 1  and you probably shouldn t be inserting them  in the vast majority of circumstances  Let SQL decide that stuff for you

User · Answer

You could do this  ugly but it works    INSERT INTO dbo MyTable  ID  Name   select   from    select 123   Timmy    union all  select 124   Jonny     union all  select 125   Sally         x

User · Answer

You can use a union   INSERT INTO dbo MyTable  ID  Name   SELECT ID  Name FROM       SELECT 123   Timmy      UNION ALL     SELECT 124   Jonny      UNION ALL     SELECT 125   Sally    AS X  ID  Name

User · Answer

Corresponding to INSERT  Transact-SQL   SQL Server 2005  you can t omit INSERT INTO dbo Blah and have to specify it every time or use another syntax approach

User · Answer

USE YourDB GO INSERT INTO MyTable  FirstCol  SecondCol  SELECT  First   1 UNION ALL SELECT  Second   2 UNION ALL SELECT  Third   3 UNION ALL SELECT  Fourth   4 UNION ALL SELECT  Fifth   5 GO   OR YOU CAN USE ANOTHER WAY  INSERT INTO MyTable  FirstCol  SecondCol  VALUES    First  1     Second  2     Third  3     Fourth  4     Fifth  5

User · Answer

Your syntax almost works in SQL Server 2008  but not in SQL Server 20051    CREATE TABLE MyTable  id int  name char 10     INSERT INTO MyTable  id  name  VALUES  1   Bob     2   Peter     3   Joe     SELECT   FROM MyTable   id    name --- --------- 1     Bob        2     Peter      3     Joe            1 When the question was answered  it was not made evident that the question was referring to SQL Server 2005  I am leaving this answer here  since I believe it is still relevant

User · Answer

If your data is already in your database you can do   INSERT INTO MyTable ID  Name  SELECT ID  NAME FROM OtherTable   If you need to hard code the data then SQL 2008 and later versions let you do the following     INSERT INTO MyTable  Name  ID  VALUES   First  1     Second  2     Third  3     Fourth  4     Fifth  5

User · Answer

This is working very fast and efficient in SQL  Suppose you have Table Sample with 4 column a b c d where a b d are int and c column is Varchar 50    CREATE TABLE  dbo   Sample    a   int  NULL   b   int  NULL   c   varchar  50  COLLATE SQL Latin1 General CP1 CI AS NULL   D   int  NULL     So you cant inset multiple records in this table using following query without repeating insert statement   DECLARE  LIST VARCHAR MAX  SET  LIST  SELECT 1  1    Charan Ghate   11      SELECT 2 2    Mahesh More   12      SELECT 3 3   Mahesh Nikam   13      SELECT 4 4    Jay Kadam   14  INSERT SAMPLE  a  b  c d  EXEC  LIST    Also With C  using SqlBulkCopy bulkcopy   new SqlBulkCopy con   You can insert 10 rows at a time      DataTable dt   new DataTable            dt Columns Add  a            dt Columns Add  b            dt Columns Add  c            dt Columns Add  d            for  int i   0  i  lt  10  i                          DataRow dr   dt NewRow                dr  a     1              dr  b     2              dr  c      Charan               dr  d     4              dt Rows Add dr                     SqlConnection con   new SqlConnection  Connection String            using  SqlBulkCopy bulkcopy   new SqlBulkCopy con                         con Open                bulkcopy DestinationTableName    Sample               bulkcopy WriteToServer dt               con Close

User · Answer

This looks OK for SQL Server 2008   For SS2005  amp  earlier  you need to repeat the VALUES statement   INSERT INTO dbo MyTable  ID  Name    VALUES  123   Timmy     VALUES  124   Jonny      VALUES  125   Sally       EDIT   My bad  You have to repeat the  INSERT INTO  for each row in SS2005   INSERT INTO dbo MyTable  ID  Name    VALUES  123   Timmy     INSERT INTO dbo MyTable  ID  Name    VALUES  124   Jonny      INSERT INTO dbo MyTable  ID  Name    VALUES  125   Sally

User · Answer

This will achieve what you re asking about      INSERT INTO table1  ID  Name      VALUES  123   Timmy                 124   Jonny                 125   Sally      For future developers  you can also insert from another table   INSERT INTO table1  ID  Name      SELECT           ID            Name      FROM table2   Or even from multiple tables   INSERT INTO table1  column2  column3      SELECT           t2 column            t3 column     FROM table2 t2          INNER JOIN table3 t3          ON t2 ID   t3 ID

User · Answer

Oracle SQL Server Insert Multiple Rows   In a multitable insert  you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables   Unconditional INSERT ALL - To add multiple rows to a table at once  you use the following form of the INSERT statement   INSERT ALL    INTO table name  column list  VALUES  value list 1     INTO table name  column list  VALUES  value list 2     INTO table name  column list  VALUES  value list 3            INTO table name  column list  VALUES  value list n  SELECT 1 FROM DUAL  -- SubQuery     Specify ALL followed by multiple insert into clauses to perform an unconditional multitable insert  Oracle Database executes each insert into clause once for each row returned by the subquery      MySQL Server Insert Multiple Rows   INSERT INTO table name  column list  VALUES      value list 1        value list 2                value list n       Single Row insert Query  INSERT INTO table name  col1 col2  VALUES val1 val2

User · Answer

INSERT INTO dbo MyTable  ID  Name  SELECT 123   Timmy  UNION ALL SELECT 124   Jonny  UNION ALL SELECT 125   Sally    For SQL Server 2008  can do it in one VALUES clause exactly as per the statement in your question  you just need to add a comma to separate each values statement

User · Answer

It would be easier to use XML in SQL Server to insert multiple rows otherwise it becomes very tedious   View full article with code explanations here http   www cyberminds co uk blog articles how-to-insert-multiple-rows-in-sql-server aspx  Copy the following code into sql server to view a sample   declare  test nvarchar max   set  test     lt topic gt  lt dialog id  1  answerId  41  gt           lt comment gt comment 1 lt  comment gt           lt  dialog gt       lt dialog id  2  answerId  42   gt       lt comment gt comment 2 lt  comment gt           lt  dialog gt       lt dialog id  3  answerId  43   gt       lt comment gt comment 3 lt  comment gt           lt  dialog gt       lt  topic gt    declare  testxml xml set  testxml   cast  test as xml  declare  answerTemp Table dialogid int  answerid int  comment varchar 1000    insert  answerTemp SELECT  ParamValues ID value   id   int     ParamValues ID value   answerId   int     ParamValues ID value   comment  1    VARCHAR 1000    FROM  testxml nodes  topic dialog   as ParamValues ID

User · Answer

I ve been using the following   INSERT INTO  TableName   ID  Name  values  NEWID    NEWID    GO 10   It will add ten rows with unique GUIDs for ID and Name   Note  do not end the last line  GO 10  with     because it will throw error  A fatal scripting error occurred  Incorrect syntax was encountered while parsing GO

[sql-server] Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to insert