CODEWITHSUNDEEP
sql-serverperformanceinsert
Stan
Stan

Reputation: 38255

Comparison to insert into query is better

Which one of below query has better performance? What's the upside/downside for both query? I want to discuss the scenario for inserting 1000 rows and 100k rows Thanks.

1st

INSERT INTO table_name (col1,col2) VALUES (value1, value2);
INSERT INTO table_name (col1,col2) VALUES (value3, value4);

2nd

INSERT INTO table_name
SELECT (col1,col2) 
FROM (
    SELECT value1 as col1, value2 as col2 UNION ALL
    SELECT value3 as col1, value4 as col2
) A

Upvotes: 3

Views: 130

Answers (2)

josephj1989
josephj1989

Reputation: 9709

The second query will perform better because the DB engine has to analyze and execute only one statement.But the difference will be insignificant and will matter only for large inserts (more than 100) But the best approach would be

INSERT INTO table_name (col1,col2) VALUES (value1, value2),(value3, value4);

but your syntax for query 2 should be INSERT INTO table_name SELECT col1,col2 FROM ( SELECT value1 as col1, value2 as col2 UNION ALL SELECT value3 as col1, value4 as col4 ) A

Upvotes: 2

arena-ru
arena-ru

Reputation: 1020

I think you need to use Bulk Insert. Here is example in .net http://www.dotnetcurry.com/ShowArticle.aspx?ID=323&AspxAutoDetectCookieSupport=1

Upvotes: 2

Related Questions