CODEWITHSUNDEEP
sql-serversql-insert
user1480192
user1480192

Reputation: 863

SQL Server insert multiple rows efficiently

I have an old classic ASP application which needs to insert many thousand rows into a SQL Server 2008 table. Currently the application is sending an INSERT command for each row separately, which takes a long time and meanwhile locks the table.

Is there a better way to do this? For example maybe:

Insert all rows into a temp table

and then do 

SELECT INTO from the temp table

?

Upvotes: 1

Views: 1343

Answers (1)

3BK
3BK

Reputation: 1348

If you're generating the list of dates in the application itself, then you could probably generate them with the necessary additions to make this work.

In SQL Server 2008, you can insert multiple rows in a single command, which is a bit better than inserting row-by-row.

Here are a couple of examples of how you could do it, using a table variable for dummy data, and using GETDATE() to generate a few different dates (which you would obviously be generating in your application):

DECLARE @TABLE AS TABLE
(
      RowID         INT IDENTITY
     ,MyDate        DATETIME
)
;

INSERT INTO @TABLE (MyDate) 
VALUES 
     (GETDATE())
    ,(GETDATE()+1)
    ,(GETDATE()+2)
    ,(GETDATE()+3)
    ,(GETDATE()+4)
    ,(GETDATE()+5)
    ,(GETDATE()+6)

SELECT * FROM @TABLE
;

Returns:

RowID  | MyDate
1      | 26/11/2017 10:51:49
2      | 27/11/2017 10:51:49
3      | 28/11/2017 10:51:49
4      | 29/11/2017 10:51:49
5      | 30/11/2017 10:51:49
6      | 01/12/2017 10:51:49
7      | 02/12/2017 10:51:49

You can also use this format:

INSERT INTO @TABLE (MyDate)
SELECT GETDATE()
    UNION ALL
SELECT GETDATE() + 1
    UNION ALL
SELECT GETDATE() + 2
    UNION ALL
SELECT GETDATE() + 3
    UNION ALL
SELECT GETDATE() + 4
    UNION ALL
SELECT GETDATE() + 5
    UNION ALL
SELECT GETDATE() + 6
;

SELECT * FROM @TABLE
;

Returns:

RowID  | MyDate
1      | 26/11/2017 10:51:49
2      | 27/11/2017 10:51:49
3      | 28/11/2017 10:51:49
4      | 29/11/2017 10:51:49
5      | 30/11/2017 10:51:49
6      | 01/12/2017 10:51:49
7      | 02/12/2017 10:51:49

Not an ASP expert, but if you're concatenating the string in your application, you should be able to concatenate the string continuously rather than recreating it as a whole new INSERT statement for each date.

Upvotes: 1

Related Questions