Tutorial :Inserting n number of records with T-SQL



Question:

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.


Solution:1

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


Solution:2

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  


Solution:3

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  


Solution:4

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


Solution:5

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  


Solution:6

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.


Solution:7

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  


Solution:8

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
Previous
Next Post »