I need help building an SQL INSERT statement in which I populate records into SQL Server database table based on a starting and ending sequential number. Example, start number = 1 and end number = 10, so the record needs to populate as follows:
Sequential number column
1
2
3
4
5
6
7
8
9
10
Help would be greatly appreciated.
sfjtraps
I would use a Tally Table to provide the incrementing number range (or a function that generates a Tally Table "just in time")
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/
a sequence object would do this nicely.
gbritton,
Thank you for your reply!
I gave the sequence object a try, however when I manually execute the SQL statements I wrote in SSMS it only writes the first number, "1". Then, I need to DROP the sequence prior to manually executing it a second time in SSMS, because I get the following error "There is already an object named 'serial_number_seq' in the database." I pasted my SQL statements below. Can you help me make so it will enter all sequence entries at once (in the code below I'm expecting 11 records, since the MAXVALUE is 11)?
CREATE SEQUENCE serial_number_seq
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 11
NO CYCLE
INSERT INTO Serial_Data
(Serial_Number, Serial_State, Case_Counter, Date_Time)
Values
(NEXT VALUE FOR serial_number_seq, 'unused', 0, GETDATE());
thank you,
sfjtraps
The sequence object worked nicely. My code is as follows:
CREATE SEQUENCE serial_number_seq
AS BIGINT
START WITH 000000000001
INCREMENT BY 1
MINVALUE 000000000001
MAXVALUE 000000000010
NO CYCLE
DECLARE @cnt INT;
SET @cnt = 000000000001
WHILE @cnt <= 000000000008
BEGIN
INSERT INTO Serial_Data
(Serial_Number, Serial_State, Case_Counter, Date_Time)
Values
(NEXT VALUE FOR serial_number_seq, 'unused', 0, GETDATE());
SET @cnt = @cnt + 1;
END;