将多个选择插入到一个表中

Question

我需要将这些选择框的值插入到一张表中。它们会返回多行，对于每行，我需要将一列插入到新表中。

例如：

select (select id from X where name=Contacts.Company) as IDClient, 
FirstName + right(' '+ cast(LastName as varchar(20)), 20) as NewName, 
(select id from Y where newid=8 and description=Contacts.JobTitle) as IDRole, 
Initials
from Contacts

我需要针对这个选择语句的每一行执行类似这样的操作：

insert into TableX (IDClient,NewName,IDRole,'0','0','0',Initials,'XXX',GETDATE(),NULL,NULL)

Ty

编辑过的

insert into TableX (IDClient,NewName,IDRole,'0','0','0',Initials,'XXX',GETDATE(),NULL,NULL)
select 
(select id from X where nAme=Contacts.Company) as IDClient, 
FirstName + right(' '+ cast(LastName as varchar(20)), 20) as NewName, 
(select id from Y where newid=8 and description=Contacts.JobTitle) as IDRole,
Initials
from Contacts

当我执行时，出现了这个错误：

"在'0'附近有语法错误。"

- Hélder Gonçalves

Answer 1

除非我漏掉了什么，否则你应该能够做到这一点：

insert into Table (IDclient,NewName,IDRole,Initials)
select (select id from X where name=Contacts.Company) as IDClient, 
  FirstName + right(' '+ cast(LastName as varchar(20)), 20) as NewName, 
  (select id from Y where newid=8 and description=Contacts.JobTitle) as IDRole, 
  Initials
from Contacts

你只需使用INSERT INTO...SELECT...FROM..语法。

如果没有表，你可以使用SELECT..INTO创建一个新的临时表：

select (select id from X where name=Contacts.Company) as IDClient, 
  FirstName + right(' '+ cast(LastName as varchar(20)), 20) as NewName, 
  (select id from Y where newid=8 and description=Contacts.JobTitle) as IDRole, 
  Initials
into #table
from Contacts

或者如果您想使用联接，则可以这样：

insert into Table (IDclient,NewName,IDRole,Initials)
select x.id as IDClient, 
  c.FirstName + right(' '+ cast(c.LastName as varchar(20)), 20) as NewName, 
  y.id as IDRole, 
  c.Initials
from Contacts c
inner join x
  on x.name=c.Company
inner join y
  on y.description=c.JobTitle
  and y.newid=8

现在您的原始帖子显示了这个插入内容：

 insert into Table (IDclient,NewName,'',IDRole,Initials,NULL)

你有两个没有名称的字段，这不是正确的语法。在插入语句中，要么没有列名并插入所有内容，要么命名你想要插入值的列。你不能使用空字符串''或null作为列名。如果你想要插入这些值，那么你必须为它们提供名称：

insert into Table (IDclient,NewName,col3,IDRole,Initials,col5)
select x.id as IDClient, 
  c.FirstName + right(' '+ cast(c.LastName as varchar(20)), 20) as NewName, 
  '' as col3
  y.id as IDRole, 
  c.Initials,
  null as col5
from Contacts c
inner join x
  on x.name=c.Company
inner join y
  on y.description=c.JobTitle
  and y.newid=8

根据您的编辑，您需要使用以下内容：

-- this insert line should state column names not '0', '0', etc
-- replace these with the names of your columns you are inserting into like the others,
-- then place these values that you want to insert in the select list
insert into TableX (IDClient,NewName,IDRole,'0','0','0',Initials,'XXX',GETDATE(),NULL,NULL)
select 
  (select id from X where nAme=Contacts.Company) as IDClient, 
  FirstName + right(' '+ cast(LastName as varchar(20)), 20) as NewName, 
  (select id from Y where newid=8 and description=Contacts.JobTitle) as IDRole,
  '0',
  '0',
  '0',
  Initials,
  'XXX',
  getdate(),
  null,
  null
from Contacts

- Taryn

---

当我使用上述代码时，我会收到“附近的语法不正确'0'”的错误提示。 - Hélder Gonçalves

在你的INSERT语句中，必须使用列名而不是 getdate() 等值，这些值应该放在查询语句的选择部分。请参考我上次使用的查询。 - Taryn