Link to home
Start Free TrialLog in
Avatar of pstather
pstatherFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Adding Auto Increment to select statement

Hello, I am trying to add an auto increment field to a select statement I have and have found that the only way I can do this is with a temp table.  However, I am having issues with adding the data to the temp table.
Here is my Statement
CREATE TABLE #tempTable (
	[ID] [int]
	,[PERSON ID] [int]
	,[CLASSIFICATION ID] [int]
	,[CLASS DATE] [datetime]
	,[SOURCE] [nvarchar](15)
	,[DESCRIPTION] [nvarchar](150)
	,[PARENT DESCRIPTION] [nvarchar](150)
	,[COUNT] [int] IDENTITY (1, 1)
)
--SET IDENTITY_INSERT #tempTable ON

INSERT INTO #tempTable (
	[ID]
	,[PERSON ID]
	,[CLASSIFICATION ID]
	,[CLASS DATE]
	,[SOURCE]
	,[DESCRIPTION]
	,[PARENT DESCRIPTION]
)
SELECT
	[Contact_Classifications].[ID]
	,[Contact_Classifications].[PERSON ID]
	,[Contact_Classifications].[CLASSIFICATION ID]
	,[Contact_Classifications].[CLASS DATE]
	,[Contact_Classifications].[SOURCE]
	,[Classifications].[DESCRIPTION]
	,[Classifications].[PARENT DESCRIPTION]
FROM
	[Contact_Classifications]
LEFT JOIN
	[Classifications]
ON
	[Contact_Classifications].[CLASSIFICATION ID] = [Classifications].[IRISID]
WHERE
	[PERSON ID] IS NOT NULL
ORDER BY
	[PERSON ID] ASC
	,[PARENT DESCRIPTION] ASC
	,[DESCRIPTION] ASC
	
--SET IDENTITY_INSERT #tempTable OFF

SELECT * FROM #tempTable
DROP TABLE #tempTable

Open in new window


And here is the error I get

Msg 207, Level 16, State 1, Line 20
Invalid column name 'DESCRIPTION'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'PARENT DESCRIPTION'.

However, as you can see in the statement, DESCRIPTION and PARENT DESCRIPTION is clearly defined in the table.

Any help would be appreciated.

Thank you
ASKER CERTIFIED SOLUTION
Avatar of Kobe_Lenjou
Kobe_Lenjou
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pstather

ASKER

Thank you for this, I need to remember the rownumber syntax.

Thank you again