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

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

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

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)

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

Corresponding to [INSERT (Transact-SQL)][1] (SQL Server 2005) you can't omit `INSERT INTO dbo.Blah` and have to specify it every time or use another syntax/approach,


[1]:

[To see links please register here]

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

[To see links please register here]

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)

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)

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)

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

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.