CODEWITHSUNDEEP
sqlsql-servert-sql
sakir
sakir

Reputation: 3502

bulky insert record to database

I need to insert approximatlly 10 records.I am thinking foreach loop,do u think is it the best way to do that.whenn searching what is the best method to do that,I find something like this,

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

what is the differences between the this and foreach loop.which one is the better. thank you all

Upvotes: 0

Views: 97

Answers (3)

hnf1991
hnf1991

Reputation: 97

Just Create a single for inserting all the record.. Because foreach loop will hit the database 10 times and u will take more time and resources..

So better to make single query for all the 10..

INSERT INTO Table ( FirstCol, SecondCol) VALUES
( Value1, Value2 ), ( Value1, Value2 )...

Upvotes: 1

mvp
mvp

Reputation: 116167

If you have MSSQL 2008 or later, you can use multi-valued insert:

INSERT INTO mytable (id, name)  
VALUES (1, 'One'),
       (2, 'Two'),  
       (3, 'Three')

Unfortunately, this syntax is not supported by MSSQL 2005 and earlier.

Upvotes: 1

Raj
Raj

Reputation: 10843

INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
GO

Both the foreach loop and the union all methods would be what is called "Row by Agonizing Row" approach

Upvotes: 1

Related Questions