CODEWITHSUNDEEP
sql-server-2005insert
Myaw
Myaw

Reputation: 61

Insert multiple rows into SQL Server

I have two tables

Product (product_id, productName)
ProductSerialNumber (ProductSerialNumber_id, product_id, serialNumber, status)

I have serial numbers 12345679000 to 123456790100 (quantity: 90) for product : MILK

Is there way to do this without using multiple inserts ie

$Sn = 12345679000;
while ($Sn <= 123456790100 )
{
  INSERT INTO ProductSerialNumber VALUES(...,...,$Sn,...)
  $Sn++;

}

Upvotes: 0

Views: 1152

Answers (2)

Myaw
Myaw

Reputation: 61

here another method to insert multiple rows with procedure

CREATE PROCEDURE autoInsert
@SerialNumberStart bigint,
@SerialNumberEnd bigint,
@status int,
@productID int
AS   
while @SerialNumberStart <= @SerialNumberEnd
begin
BEGIN TRAN

INSERT INTO ProductSerialNumber VALUES(@SerialNumberStart)
--print @SerialNumberStart

COMMIT TRAN;
set @SerialNumberStart=@SerialNumberStart+ 1
end

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You can do this:

WITH Temp
AS
(
   SELECT n
   FROM(VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS Temp(n)
), nums
AS
(
   SELECT id = t1.n * 10 + t2.n + 1
   FROM temp AS T1
   CROSS JOIN temp AS t2
)
INSERT INTO ProductSerialNumber(serialnumber)
SELECT 12345679000 + id AS Serialnumber  -- You can insert into other columns too
FROM nums;

SQL Fiddle Demo.

Note that: This syntax FROM(VALUES(0), (1), ..., (9)) AS Temp(n) is new to SQL Server-2008, for old versions you can use SELECT ... UNION ALL SELECT ... instead.

However, if possible, you can alter this table and make this column SerialNumber an IDENTITY(12345679000, 1) and it will be auto incremental.

Update 1

For SQL Server 2005, try this:

WITH Temp
AS
(
  SELECT 1 AS id
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
  UNION ALL
  SELECT 4
), nums
AS
(
  SELECT ROW_NUMBER() OVER(ORDER BY t1.id) AS id
  FROM temp t1, temp t2, temp t3, temp t4
)
INSERT INTO ProductSerialNumber(serialnumber)
SELECT 12345679000 + id AS Serialnumber
FROM nums
WHERE id <= 100;

Updated SQL Fiddle Demo.

Update 2

*How does this query work? *

Firstly, I define a virtual table with only four values:

  SELECT 1 AS id
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
  UNION ALL
  SELECT 4

I defined it inside a Common table expression(CTE), to reuse it later.

Then in the following CTE, I used:

FROM temp t1, temp t2, temp t3, temp t4

This will join the table temp four times with it self, therefore it will give you: 44 = 256 rows. Then I used the ranking function ROW_NUMBER() as a work a round to generate a sequence number of number from 1 to 265.

The last thing is the syntax of INSERT INTO ... SELECT ... to select the numbers <= 100 of the numbers that we already generated from the previous step and inserting them to the table.

Hope this makes sense.

Upvotes: 1

Related Questions