CODEWITHSUNDEEP
c#sql
user3134342
user3134342

Reputation: 13

How to use sub Query in insert statement

I have tried but I get error:

SubQuery are not allowed in this context message comes.

I have two tables Product and Category and want to use categoryId base on CategoryName.

The query is

Insert into Product(Product_Name,Product_Model,Price,Category_id) 
values(' P1','M1' , 100, (select CategoryID from Category where Category_Name=Laptop))

Please tell me a solution with code.

Upvotes: 1

Views: 532

Answers (3)

Ankush Madankar
Ankush Madankar

Reputation: 3834

Try this:

DECLARE @CategoryID BIGINT  = (select top 1 CategoryID from Category where Category_Name='Laptop')
Insert into Product(Product_Name,Product_Model,Price,Category_id) 
values(' P1','M1' , 100, @CategoryID)

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try like this

Insert into Product
(
Product_Name,
Product_Model,
Price,Category_id
)
Select 
'P1',
'M1' , 
100, 
CategoryID 
From 
Category 
where Category_Name='Laptop'

Upvotes: 3

marc_s
marc_s

Reputation: 754498

(you didn't clearly specify what database you're using - this is for SQL Server but should apply to others as well, with some minor differences)

The INSERT command comes in two flavors:

(1) either you have all your values available, as literals or SQL Server variables - in that case, you can use the INSERT .. VALUES() approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
VALUES(Value1, Value2, @Variable3, @Variable4, ...., ValueN)

Note: I would recommend to always explicitly specify the list of column to insert data into - that way, you won't have any nasty surprises if suddenly your table has an extra column, or if your tables has an IDENTITY or computed column. Yes - it's a tiny bit more work - once - but then you have your INSERT statement as solid as it can be and you won't have to constantly fiddle around with it if your table changes.

(2) if you don't have all your values as literals and/or variables, but instead you want to rely on another table, multiple tables, or views, to provide the values, then you can use the INSERT ... SELECT ... approach:

INSERT INTO dbo.YourTable(Col1, Col2, ...., ColN)
   SELECT
       SourceColumn1, SourceColumn2, @Variable3, @Variable4, ...., SourceColumnN
   FROM
       dbo.YourProvidingTableOrView

Here, you must define exactly as many items in the SELECT as your INSERT expects - and those can be columns from the table(s) (or view(s)), or those can be literals or variables. Again: explicitly provide the list of columns to insert into - see above.

You can use one or the other - but you cannot mix the two - you cannot use VALUES(...) and then have a SELECT query in the middle of your list of values - pick one of the two - stick with it.

So in your concrete case, you'll need to use:

INSERT INTO dbo.Product(Product_Name, Product_Model, Price, Category_id) 
   SELECT 
      ' P1', 'M1', 100, CategoryID 
   FROM 
      dbo.Category 
   WHERE 
      Category_Name = 'Laptop'

Upvotes: 4

Related Questions