Insert into multiple selects

Question

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'."

Answer 1

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.

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

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 null as column names. If you want these values to be inserted then you have to provide the names for them:

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