SELECT INTO fails for tables with IDENTITY

[TheDrider]

I have an RCCC table with RCCC_ID being an IDENTITY column. The following SQL fails:

	SELECT 
		RCCC_ID,
		RCCC_TX,
		RCCC_TITLE_TX,
		R.DIR_PROGRAM_ID,
		R.ACTIVE_CD,
		CAST(0 AS int) AS RECORD_COUNT_QY,
		IDENTITY(int,1,1) AS SORT_ID

	INTO #TMP_RCCCS
	FROM RCCC R
	WHERE 1 = 2

with this error message:
[red]Cannot add identity column, using the SELECT INTO statement, to table '#TMP_RCCC', which already has column 'RCCC_ID' that inherits the identity property.[/red]

I can understand that part, but the query runs if I add a join to the FROM clause:
[blue] LEFT JOIN DIRECTORATE_PROGRAMS DP ON R.DIR_PROGRAM_ID = DP.DIR_PROGRAM_ID[/blue]

Why wouldn't the query fail under that second condition? A SQL-ism of some sort?


Also... This is part of a stored proc to implement a paging scheme (only return rows 51 to 75...) since SQL Server doesn't support LIMIT. What is the cleanest way to implement this functionality? I would pass in the page number and rowsPerPage and want the query to only return the appropriate records.

 

[gmmastros]

You have a select into where 1=2. This will create a temp table with the structure you want without actually copying any records.

Instead, you could 'manually' create the temp table, like so...

[tt][blue]
Create Table #TMP_RCCCS
(
RCCC_ID Int,
RCCC_TX VarChar(10) ???,
....
Sort_Id Integer Identity(1,1)
)

[/blue][/tt]

Assuming you get the data types right, this should work out well for you.

You may also want to consider using a table variable instead. Performance may be better. Also, Instead is inserting all that data in to a temp table, try inserting just the primary key for the table in to the temp table. Then, join the temp table back to the original table. Something like this...

[tt][blue]
Select OriginalTable.Field1, OriginalTable.Field2, etc...
From OriginalTable
Inner Join #TMP_RCCCS
On OriginalTable.PrimaryKeyField = #TMP_RCCC.PrimaryKeyField
Where #TMP_RCCCS.SortId Between 51 and 75
Order By #TMP_RCCCS.SortId
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom