Click here to Skip to main content
14,639,700 members
Rate this:
Please Sign up or sign in to vote.
See more:
I've created a temp table using the following code:

if OBJECT_ID('Tempdb..#FormerMajor','U') is not null
	drop table #FormerMajor
CREATE TABLE #FormerMajor 
(ID int IDENTITY(1,1)
,MyID varchar(11)
,LastName varchar(9)
,FirstName varchar(9)
,PKey int
,CurrentInfo varchar(50)
,SourceKey varchar(4)
,Term varchar(20)

Error thrown in first line of code below:

INSERT INTO #FormerMajor 

select distinct
 1  -- Error thrown here 
,dt.Description as 'Term'
from CustomFinal.Table f
	join Final.Table 2 ds
		on f.eID = ds.SourceKey 
	join Final.Table3 dp
		on f.PlanKey = dp.PlanKey
	join Final.Table4 dt
		on f.TermKey = dt.TermKey
where 1=1
and f.VersionKey = 1
and f.ID = '1111'

ORDER by dt.Description

Error reads, " An explicit value for the identity column in table '#FormerMajor' can only be specified when a column list is used..." I understand that I can't hard code a value within my id column. And by using the identity it will auto increment distinct rows by one.

The output I'd like to achieve is:

|ID |LastName| FirstName |PlanDescripion| etc. & so forth
|1 |asdf | joe |Description 1 |
|2 |asdf | joe |Description 2 |

Essentially I'd like to create move the contents of permentment tables into a temp table and then insert my own ID which separates the results by description.

Any ideas on how to accomplish this with or with the code above? (Preferably with)

Thanks in advance!

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You most not insert values into the identity column, this value will be assigned automatically. But to leave out the one column you need to specify the full list of columns you want to insert:
INSERT INTO #FormerMajor (MyID, LastName, FirstName, CurrentInfo, SourceKey, Term, PKey)
select distinct
,dt.Description as 'Term'
from ...
Member 11820531 17-Dec-15 16:56pm
I should've known that, thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100