CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kamran
Kamran

Reputation: 4100

SQL Server taking values from 2 different tables with no matching and insert into 3rd table

I have two tables BusinessSector5 and SubCategory and I want to insert their IDs into a third table Match_Subcategory_BusinessSector5. This 3rd table contains two columns SubCategoryID and BusinessSector5ID

I am using this query but it's not working 

Insert into Match_Subcategory_BusinessSector5(SubCategoryID, BusinessSector5ID)
values(
select SubCategory.ID,[BusinessSector5].ID

from [BusinessSector5],SubCategory

where Description_DE = 'Abbrucharbeiten' and Kategorie = 'Abbruch / Entsorgung')

I am getting this error:

Incorrect syntax near the keyword select.

Upvotes: 1

Views: 90

Answers (2)

AdamL
AdamL

Reputation: 13141

You don't use "values" with select:

Insert into Match_Subcategory_BusinessSector5 (
    SubCategoryID,
    BusinessSector5ID
)
select 
    SubCategory.ID,
    [BusinessSector5].ID
from [BusinessSector5],SubCategory
where Description_DE = 'Abbrucharbeiten' 
    and Kategorie = 'Abbruch / Entsorgung'

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You don't need to use VALUES when you are inserting using INSERT...SELECT:

INSERT INTO Match_Subcategory_BusinessSector5 (SubCategoryID, BusinessSector5ID)
SELECT SubCategory.ID, [BusinessSector5].ID
FROM [BusinessSector5], SubCategory
WHERE
  Description_DE = 'Abbrucharbeiten'
  AND Kategorie = 'Abbruch / Entsorgung'

but are you sure you don't need a JOIN between BusinessSector5 and SubCategory? Maybe you need this:

INSERT INTO Match_Subcategory_BusinessSector5 (SubCategoryID, BusinessSector5ID)
VALUES
((SELECT SubCategory.ID FROM SubCategory WHERE ....),
 (SELECT [BusinessSector5].ID FROM BusinessSector5 WHERE ....));

Upvotes: 3

Related Questions