Create sequence numbers in multi-record insert ?

[millerk]

I have some sql that selects from multiple tables and does an insert into a single "report" table that I use for generating a crystal report and an electronic file for a client.
So it's something like:
INSERT INTO ReportTable(fields....)
SELECT fields....
FROM Table1 join Table2... etc.
WHERE ...

The report and electronic file need to include record numbers for each item in my report table, starting at 1 and going up. This record number has no relation to the actual data and is basically used to make it easier to match up records in the report to the records in the electronic file.

I could just make sure that I sort the records the same way in the report and in the electronic file generation, then create the record numbers on the fly, but to make sure they match between the report and file, I'd rather include them in the report table.

I could use a cursor to loop through the records after the insert and add this record number value, but I'd rather not if there is a better way to do it.

Is there a way to add some kind of auto-incrementing field to my insert statement?

 

[hmckillop]

Try using the identity function -

IDENTITY ( data_type [ , seed , increment ] ) AS column_name

e.g.

INSERT INTO ReportTable(fields....)
SELECT fields...., IDENTITY(BIGINT, 1, 1) as MyIdent
FROM Table1 join Table2... etc.
WHERE ...

"I'm living so far beyond my income that we may almost be said to be living apart

 

[millerk]

Unless I'm missing something, I don't think that will work.
Each time I do an insert into this table, I need the set of records that's being inserted to have record numbers that start with 1 and go up to whatever number. The record number basically identifies a sequence of records for that particular report. Plus, I need to be able to have multiple records with the same record number because we may have multiple users running reports at the same time.

So the table will look something like:

Username RecordNum ProductID Qty
Bob 1 28376 50
Bob 2 39847 200
Bob 3 28777 35

Then Joe comes along and wants to run a report, so I need to be able to insert Joe's records, also numbered 1 to whatever.

 

[bborissov]

declare @Test Table (FLd1 varchar(20), Fld2 int)
declare @Test1 Table (FLd1 varchar(20))
declare @Test2 Table (FLd1 varchar(20))
INSERT INTO @Test1 VALUES('Bob')
INSERT INTO @Test1 VALUES('Bob')
INSERT INTO @Test1 VALUES('Bob')
INSERT INTO @Test1 VALUES('Bob')
INSERT INTO @Test1 VALUES('Bob')
INSERT INTO @Test1 VALUES('Bob')

INSERT INTO @Test2 VALUES('Joe')
INSERT INTO @Test2 VALUES('Joe')
INSERT INTO @Test2 VALUES('Joe')

DECLARE @i int
SET @i = 0
INSERT INTO @Test (Fld1)
SELECT Fld1 FROM @Test1
UPDATE @Test SET @i = Fld2 = @I+1

SET @i = 0
INSERT INTO @Test (Fld1)
SELECT Fld1 FROM @Test2
UPDATE @Test SET @i = Fld2 = @I+1 WHERE Fld2 IS NULL

SELECT * from @Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP