SQL 插入多选

Insert into multiple selects

提问by Hélder Gon?alves

I need to insert the values of these selects into a table. They return many rows and for each row I need to insert a column into a new table.

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

Ex:

前任：

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

And I need to do something like this for each row of this select:

我需要为这个选择的每一行做这样的事情：

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

Ty

泰

EDITED

已编辑

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

When i execute this I'm getting this error:

当我执行此我收到此错误：

"Incorrect syntax near '0'."

“'0' 附近的语法不正确。”

回答by Taryn

Unless I am missing something you should be able to do this:

除非我遗漏了一些东西，否则你应该能够做到这一点：

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

You will just use the INSERT INTO...SELECT...FROM..syntax.

您将只使用INSERT INTO...SELECT...FROM..语法。

Now if you do not have a table, then you can SELECT..INTOa new temp table:

现在，如果您没有表，那么您可以创建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

Or if you want to use joins for this then:

或者，如果您想为此使用连接，则：

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

Now your original post shows an insert of this:

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

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

You have two fields that do not have names with them, this is not correct syntax. In your insert statement to either have to names no columns and insert into all or name the columns that you want to insert values into. You cannot use an empty string ''or nullas column names. If you want these values to be inserted then you have to provide the names for them:

您有两个没有名称的字段，这是不正确的语法。在您的插入语句中，要么不命名任何列并插入所有列，要么命名要插入值的列。您不能使用空字符串''或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

Based on your edit, you need to use the following:

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

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