?
Solved

Adding Auto Increment to select statement

Posted on 2011-05-05
2
Medium Priority
?
1,077 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:pstather
2 Comments
 
LVL 8

Accepted Solution

by:
Kobe_Lenjou earned 500 total points
ID: 35696499
Why not use the ROW_NUMBER statement?
SELECT
	[Contact_Classifications].[ID]
	,[Contact_Classifications].[PERSON ID]
	,[Contact_Classifications].[CLASSIFICATION ID]
	,[Contact_Classifications].[CLASS DATE]
	,[Contact_Classifications].[SOURCE]
	,[Classifications].[DESCRIPTION]
	,[Classifications].[PARENT DESCRIPTION]
, ROW_NUMBER() OVER ( ORDER BY [PERSON ID] ASC ,[PARENT DESCRIPTION] ASC ,[DESCRIPTION] ASC )

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

Open in new window

0
 

Author Closing Comment

by:pstather
ID: 35696580
Thank you for this, I need to remember the rownumber syntax.

Thank you again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question