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