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'.

Nikos

Your above code is fine just need to add ',' after values statement – Cliffhanger

INSERT INTO @blah (ID, Name), VALUES (123, 'Timmy'), VALUES (124, 'Jonny'), VALUES (125, 'Sally') – Cliffhanger

Just a caution: you can insert up to 1000 rows only by this method. INSERT INTO #Test (LWPurchaseOrderID )VALUES ( 935791 ), ( 935933 ) – Foretime

2005 is no longer supported. For 2008, 2012 and 2016 you can almost use what you put INSERT INTO @blah (ID, Name) VALUES (123, 'Timmy'), (124, 'Jonny'), (125, 'Sally') "VALUES" only appears once and you need commas between the sets. – Although

Answer 1

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)...

Harlamert

I think that it's better to write more ugly rather than less effective. I mean unnecessary load of database engine – Closure

Is this any more efficient than using multiple INSERT statements? – Dufour

@Code Commander: no, in that it is longer to compile. Yes, in that you have one insert only. But it answers the question: no repeat of the INSERT table (columnlist) – Harlamert

@Harlamert Thanks for the answer, it seems to be the only way to perform multiple INSERT statements at once in SQL Server CE. I am very curious, though, after reading up on what UNION ALL does, how this actually works, at all. I see the SELECT, the UNION ALL, and of course, the original INSERT INTO statement, but I don't see how this is actually pushing multiple row's values like it is. I am curious because I would like to append UPDATE statements, as well, and wanted to test if this was possible, but before I can do that, I need to understand how this much of it works. – Sacristan

@Sacristan I know this comes half a year later and you might have figured this out yet long ago, but it's really quite simple. By using select you create a set with columns and rows, and by design these rows can be inserted into another table with an equal amount of columns. You can even use a mixture of literals and values. For example, using insert with select 'A', ID from ATable would insert 'A' in the first column every time and the ID column value of the corresponding row of ATable in the second column. – Particularism

This also workes with DB2 which is an important sidenote for those of us stuck in outdated technology. The values seperated by comma answer is better in my mind for those of you working in SQL Server 2008 or newer. The OP can remove all "values" except the first and replace with a , – Convexoconcave

Is this possible with a select statement? I'm trying to avoid explicitly creating a #table (temporary table). – Bannock

While this works, it took twice as long to load our current database as multiple values statements. – Sugden

@Sugden you would not do that after version SQL Server 2008. As mentioned... – Harlamert

Answer 2

Your syntax almost works in SQL Server 2008 (but not in SQL Server 2005¹):

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       

---

¹ 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.

Tameshatamez

Works in SQL Server 2012 – Lohman

Server 2008 doesn't allow more than 1000 rows inserted this way. – Deration

What happens, if one value set is faulty? Will all inserts rolled back or just the faulty row? – Serow

@Serow I just tested that only faulty rows are not inserted (all of the all the others are inserted correctly) – Zoologist

@Michael It could be lifted https://mcmap.net/q/45766/-insert-multiple-rows-without-repeating-the-quot-insert-into-quot-part-of-the-statement – Stationer

Answer 3

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)

Zionism

Answer 4

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+

Stationer

Can I have a link to an article about this 'sub' syntax. – Papagena

@Papagena learn.microsoft.com/en-us/sql/t-sql/queries/… section: C. Specifying multiple values as a derived table in a FROM clause – Stationer

The advantage of this answer is that it provides a way to specify identical values without repeating them (which is what I was looking for). E.g. with a third column that is identical, you wouldn't need to repeat it for a thousand times. – Scandian

@VadimBerman Yes, that is good scenario when there is no default defined on table. – Stationer

Answer 5

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

Nicotiana

Answer 6

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

Contusion

Answer 7

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)

Corina

Answer 8

This looks OK for SQL Server 2008. For SS2005 & 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')  

Unless

Answer 9

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 = '<topic><dialog id="1" answerId="41">
        <comment>comment 1</comment>
        </dialog>
    <dialog id="2" answerId="42" >
    <comment>comment 2</comment>
        </dialog>
    <dialog id="3" answerId="43" >
    <comment>comment 3</comment>
        </dialog>
    </topic>'

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)

Dumortierite

Answer 10

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)

Halfon

Answer 11

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.

Faxan

Answer 12

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,

Closure

Answer 13

In PostgreSQL, you can do it as follows;

A generic example for a 2 column table;

INSERT INTO <table_name_here>
    (<column_1>, <column_2>)
VALUES
    (<column_1_value>, <column_2_value>),
    (<column_1_value>, <column_2_value>),
    (<column_1_value>, <column_2_value>),
    ...
    (<column_1_value>, <column_2_value>);

See the real world example here;

A - Create the table

CREATE TABLE Worker
(
    id serial primary key,
    code varchar(256) null,
    message text null
);

B - Insert bulk values

INSERT INTO Worker
    (code, message)
VALUES
    ('a1', 'this is the first message'),
    ('a2', 'this is the second message'),
    ('a3', 'this is the third message'),
    ('a4', 'this is the fourth message'),
    ('a5', 'this is the fifth message'),
    ('a6', 'this is the sixth message');

Incipit

Answer 14

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 < 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();
        }

Alamode

Answer 15

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.

Uthrop

Answer 16

I suggest using json for the original data and no temp table is needed.

DECLARE @json varchar(max) = '[
  {
    "ID": 123,
    "Name": "Timmy"
  },
  {
    "ID": 124,
    "Name": "Jonny"
  },
  {
    "ID": 125,
    "Name": "Sally"
  }
]';


DECLARE @blah TABLE
(
    ID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

INSERT INTO @blah (ID, Name)
SELECT * FROM OPENJSON(@json)
WITH (ID int, Name varchar(100))

SELECT * FROM @blah

Flexible

Answer 17

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);

Rounded

Answer 18

Created a table to insert multiple records at the same.

CREATE TABLE TEST 
(
    id numeric(10,0),
    name varchar(40)
)

After that created a stored procedure to insert multiple records.

CREATE PROCEDURE AddMultiple
(
    @category varchar(2500)
)
as
BEGIN

declare @categoryXML xml;
set @categoryXML = cast(@category as xml);

    INSERT INTO TEST(id, name)
    SELECT
        x.v.value('@user','VARCHAR(50)'),
        x.v.value('.','VARCHAR(50)')
    FROM @categoryXML.nodes('/categories/category') x(v)
END
GO

Executed the procedure

EXEC AddMultiple @category = '<categories> 
                                  <category user="13284">1</category> 
                                  <category user="132">2</category>
                              </categories>';

Then checked by query the table.

select * from TEST;

Indochina