Tutorial :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.


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)    */  


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  


this is the approach I'm using and works best for my purposes and using SQL 2000. Because in my case is inside an UDF, I can't use ## or # temporary tables so I use a table variable. I'm doing:

DECLARE @tblRows TABLE (pos int identity(0,1), num int)   DECLARE @numRows int,@i int      SET @numRows = DATEDIFF(dd,@start,@end) + 1  SET @i=1    WHILE @i<@numRows  begin      INSERT @tblRows SELECT TOP 1 1 FROM sysobjects a        SET @i=@i+1  end  


Overall much faster to double the amount of rows at every iteration

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)  GO  DECLARE @i INT;  SET @i = 1;  INSERT INTO dbo.Numbers(n) SELECT 1;  WHILE @i<128000 BEGIN    INSERT INTO dbo.Numbers(n)      SELECT n + @i FROM dbo.Numbers;    SET @i = @i * 2;  END;   

I deliberately did not SET NOCOUNT ON, so that you see how it inserts 1,2,4,8 rows


you can use a cross join

select top 100000 row_number() over(order by t1.number)-- here you can change 100000 to a number you want or a variable  from   master.dbo.spt_values t1         cross join master.dbo.spt_values t2  


When you have a pre-built numbers table, just use that:

SELECT *  FROM numbers  WHERE number <= DATEDIFF(d,'2009-01-01',getdate())  

There are any number of techniques for building the numbers table in the first place (using techniques here), but once it's built and indexed, you don't build it again.


You could do what PinalDave suggests:

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  


How about:

DECLARE @nRecords INT    SET @nRecords=DATEDIFF(d,'2009-01-01',getdate())    SELECT TOP (@nRecords)      ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) - 1  FROM sys.objects a, sys.objects b  

If you don't want it zero-indexed, remove the " - 1"

Requires at least SQL Server 2005.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »