Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

Your syntax almost works in SQL Server 2008 (but not in SQL Server 2005<sup>1</sup>):

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


---

<sup>1</sup> 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.

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

Using `INSERT INTO ... VALUES` syntax like in [Daniel Vassallo's][1] answer
there is one annoying limitation:

> From [MSDN](

[To see links please register here]

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


<kbd>**[`LiveDemo`](

[To see links please register here]

;


[1]:

[To see links please register here]

<hr>
*This will work starting from SQL Server 2008+*

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

> [Oracle SQL Server Insert Multiple Rows](

[To see links please register here]

)

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:
```lang-sql
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
```

![](

[To see links please register here]

)

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](

[To see links please register here]

)

```lang-sql
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);
```

---

Single Row insert Query

```lang-sql
INSERT INTO table_name (col1,col2) VALUES(val1,val2);
```

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.

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;

<H3>A - Create the table</H3>

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

<h3>B - Insert bulk values </h3>

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

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;

[![enter image description here][1]][1]


[1]:

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