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
,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!