Click here to Skip to main content
15,887,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I've created a temp table using the following code:

SQL
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:

SQL
INSERT INTO #FormerMajor 

select distinct
 1  -- Error thrown here 
,f.ID
,ds.LastName
,ds.FirstName
,dp.PlanDescription
,fsps.TermSourceKey
,dt.Description as 'Term'
,dp.PlanKey
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!
Posted

1 solution

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:
SQL
INSERT INTO #FormerMajor (MyID, LastName, FirstName, CurrentInfo, SourceKey, Term, PKey)
 
select distinct
f.ID
,ds.LastName
,ds.FirstName
,dp.PlanDescription
,fsps.TermSourceKey
,dt.Description as 'Term'
,dp.PlanKey
from ...
 
Share this answer
 
Comments
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900