Ttrying to add an id number to table during insert with no luck

[vba317]

I have trying to determine a way of adding an incrementing number into a new table I have created with no luck. I have been bouncing back and forth between identity and row_number. My current code is using select identity. The table field is called cnt and the data type is int. I would like the number to start at 1 for the first record and increment by 1 for the rest of the records. Any help is appreciated.
Tom

DECLARE @entered datetime,@id int
SET @entered = Convert(varchar, getdate(), 110)
SET @id =SELECT IDENTITY(INT, 1, 1) 

--INSERT INTO rptdata_monthly.dbo.rpt_EncounterCnts_1
--(cnt,year,month,uci,enc_cnt,cpt_cnt,procedures,entered)
SELECT @id,PER.yr as year,PER.monasdt as month,CS.uci,Sum(CS.enccnt),Sum(CS.cptcnt),Sum(CS.adjunits) as Procedures,@entered as entered
INTO rptdata_monthly.dbo.rpt_EncounterCnts_1
FROM rptdata_monthly.dbo.rpt_dat_CSDetail CS 
JOIN rptdata_monthly.dbo.dic_Period PER ON PER.pd = CS.rptpd
WHERE CS.uci='ASO'
GROUP BY PER.yr,PER.monasdt,CS.uci
ORDER BY PER.yr,PER.monasdt,CS.uci

 

[Olaf Doschke]

What works for me is using IDENTITY(int,1,1) directly in the SELECT part instead of first declaring a variable @id.
The INTO clause then needs to specify the name of a non existing table, which will be created.

Bye, Olaf.

 

[vba317]

OK, so I added the identity to the select statement. I get an incorrect syntax near ',' error.

DECLARE @entered datetime,@id int
SET @entered = Convert(varchar, getdate(), 110)
--SET @id =SELECT IDENTITY(INT,1,1) 

--INSERT INTO rptdata_monthly.dbo.rpt_EncounterCnts_1
--(cnt,year,month,uci,enc_cnt,cpt_cnt,procedures,entered)
SELECT IDENTITY(INT,1,1),PER.yr as year,PER.monasdt as month,CS.uci,Sum(CS.enccnt),Sum(CS.cptcnt),Sum(CS.adjunits) as Procedures,@entered as entered
INTO rptdata_monthly.dbo.rpt_EncounterCnts_1
FROM rptdata_monthly.dbo.rpt_dat_CSDetail CS 
JOIN rptdata_monthly.dbo.dic_Period PER ON PER.pd = CS.rptpd
WHERE CS.uci='ASO'
GROUP BY PER.yr,PER.monasdt,CS.uci
ORDER BY PER.yr,PER.monasdt,CS.uci

 

[Olaf Doschke]

You have to give the identity column a name.

SELECT IDENTITY(INT,1,1) as ID, ...

Bye, Olaf.

 

[Olaf Doschke]

To clarify why that is needed another example without INTO clause, a normal SELECT query:

SELECT RAND(), * FROM Sometable

The result shown in SSMS will have no caption on the RAND() column, the header will show "(no column name)".

In Tables you can't have any column without a name. So with SELECT INTO you can't use expressions without AS clause to name the result and thereby name the column of the created table.

Bye, Olaf.

 

[vba317]

I fixed that but now I get an error rptdata_monthly.dbo.rpt_EncounterCnts_1 already exists. This is a table that has to exist. I can't create a new one every month.

Tom

 

[Olaf Doschke]

You can't define an IDENTITY without creating a new table. If your table already exists and already has an IDENTITY field, that will create further IDs automatically, you just leave it out in your field list. You then write an INSERT INTO rptdata_monthly.dbo.rpt_EncounterCnts_1 (field2,field3,field4) SELECT ... FROM. With field2 I indicate leaving out the first field, the ID field.

Bye, Olaf.