Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting n number of records with T-SQL

I want to add a variable number of records in a table (days)

And I've seen a neat solution for this:

SET @nRecords=DATEDIFF(d,'2009-01-01',getdate())
SET ROWCOUNT @nRecords
INSERT int(identity,0,1) INTO #temp FROM sysobjects a,sysobjects b
SET ROWCOUNT 0

But sadly that doesn't work in a UDF (because the #temp and the SET ROWCOUNT). Any idea how this could be achieved?

At the moment I'm doing it with a WHILE and a table variable, but in terms of performance it's not a good solution.

like image 335
David Aleu Avatar asked Jun 24 '09 21:06

David Aleu


People also ask

How do I insert N rows in SQL?

If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.

How do I insert multiple rows at a time in SQL?

INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.


2 Answers

If you're using SQL 2005 or newer, you can use a recursive CTE to get a list of dates or numbers...

with MyCte AS
    (select   MyCounter = 0
     UNION ALL
     SELECT   MyCounter + 1
     FROM     MyCte
     where    MyCounter < DATEDIFF(d,'2009-01-01',getdate()))
select MyCounter, DATEADD(d, MyCounter, '2009-01-01')
from   MyCte 
option (maxrecursion 0)


/* output...
MyCounter   MyDate
----------- -----------------------
0           2009-01-01 00:00:00.000
1           2009-01-02 00:00:00.000
2           2009-01-03 00:00:00.000
3           2009-01-04 00:00:00.000
4           2009-01-05 00:00:00.000
5           2009-01-06 00:00:00.000
....
170         2009-06-20 00:00:00.000
171         2009-06-21 00:00:00.000
172         2009-06-22 00:00:00.000
173         2009-06-23 00:00:00.000
174         2009-06-24 00:00:00.000

(175 row(s) affected)

*/
like image 71
Scott Ivey Avatar answered Sep 27 '22 22:09

Scott Ivey


You can use a WHILE statement for that:

declare @i int
declare @rows_to_insert int
set @i = 0
set @rows_to_insert = 1000

while @i < @rows_to_insert
    begin
    INSERT INTO #temp VALUES (@i)
    set @i = @i + 1
    end
like image 31
Andomar Avatar answered Sep 27 '22 20:09

Andomar