如何将键（索引）插入临时表？ [英] How do I insert an key (index) into a temp table?

问题描述

我使用以下代码创建了一个临时表：

  if   OBJECT_ID （'  Tempdb ..＃FormerMajor '，'  U'）是 不  null  
  drop   table  #FormerMajor 
  CREATE   TABLE  #FormerMajor 
（ID  int   IDENTITY （ 1 ， 1 ）
，MyID  varchar （ 11 ）
，LastName  varchar （ 9 ） 
，F irstName  varchar （ 9 ）
，PKey  int  
，CurrentInfo  varchar （ 50 ）
，SourceKey  varchar （ 4 ）
，Term  varchar （ 20 ）
）

首先抛出错误以下代码行：

  INSERT   INTO  #FormerMajor 
 
 选择  distinct  
  1    -   此处抛出的错误 
，f.ID 
，ds.LastName 
，ds.FirstName 
，dp.PlanDescription 
，fsps.TermSourceKey 
，dt.Description < span class =code-keyword> as  '  Term' 
，dp.PlanKey 
 来自 CustomFinal。表 f 
  join  Final 。表  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 
 其中 1 = 1 
 和 f.VersionKey =  1  
 和 f.ID = '   1111' 
 
  ORDER   by  dt .Description 

错误读取，表'#FormerMajor'中标识列的显式值只能在列列表中指定使用...我知道我不能在我的id列中硬编码值。通过使用标识，它将自动将不同的行加1。



我想要达到的输出是：



| ID | LastName | FirstName | PlanDescripion |等等所以

| 1 | asdf | joe |描述1 |

| 2 | asdf | joe |描述2 |





基本上我想创建将permentment表的内容移动到临时表中然后插入我的自己的ID，用于按结果分隔结果。



关于如何使用上述代码完成此操作的任何想法？ （最好带）



提前致谢！

解决方案

大多数人都没有将值插入标识列，该值将自动分配。但是要省略一列，您需要指定要插入的列的完整列表：

  INSERT   INTO  #FormerMajor（MyID，LastName，FirstName，CurrentInfo，SourceKey，Term，PKey）
 
 选择  distinct  
 f.ID 
，ds.LastName 
，ds.FirstName 
， dp.PlanDescription 
，fsps.TermSourceKey 
，dt.Description  as  ' < span class =code-string> Term' 
，dp.PlanKey 
  from  ... 

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

解决方案

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
f.ID
,ds.LastName
,ds.FirstName
,dp.PlanDescription
,fsps.TermSourceKey
,dt.Description as 'Term'
,dp.PlanKey
from ...

这篇关于如何将键（索引）插入临时表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！