CODEWITHSUNDEEP
sqlsql-server
Ram Prakash
Ram Prakash

Reputation: 37

insert with select query

I have 3 tables master, element, check.

master 

master_id,           
master_name,

element

element_id,                                
element_name,

check

(master_id)
(element_id)
check_id
check_name
description

I want to insert values into check table such as check_id,check_name,description with master_id from master table and element_id from element table.

I tried the following query. 

insert 
into CHECK values((select e.ELEMENT_ID,m.MASTER_ID from MASTER as m,ELEMENT as e where m.MASTER_ID=3 and ELEMENT_ID=3),'M001','chkitem1','Check description',)

It throws me the following error.

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

can anyone give an idea for this..?

Upvotes: 2

Views: 168

Answers (1)

marc_s
marc_s

Reputation: 754478

You can EITHER have something like

INSERT INTO dbo.Yourtable(list of columns)
VALUES (list of scalar, atomic values)

but then you need to provide atomic, scalar values - literals or variables.

OR you use this syntax

INSERT INTO dbo.YourTable(list of columns)
  SELECT (list of columns) 
  FROM dbo.YOurSourceTable ......

but in this case, you don't specify the VALUES keyword.

So in your case, try:

INSERT INTO dbo.CHECK(master_id, element_id, check_id, check_name, description)
  SELECT 
      e.ELEMENT_ID, m.MASTER_ID, 'M001', 'chkitem1', 'Check description'
  FROM 
      dbo.MASTER AS m
  INNER JOIN
      dbo.ELEMENT AS e ON m.MASTER_ID = e.ELEMENT_ID
  WHERE
       m.Master_ID = 3

Also: don't use the old-style JOIN of just comma-separating a list of tables in the FROM clause - that's been deprecated with the SQL-92 standard, more than 20 years ago! Use the proper ANSI JOIN with INNER JOIN or LEFT OUTER JOIN instead.

Update: ok, so you seem to be inserting something into the Master table first (wasn't clear from your question) and then you want to use that SCOPE_IDENTITY in the subsequent insert - try this:

INSERT INTO dbo.Master(list-of-columns) VALUES(.........)

DECLARE @MasterID INT
SELECT @MasterID = SCOPE_IDENTITY()

INSERT INTO dbo.CHECK(master_id, element_id, check_id, check_name, description)
  SELECT 
      e.ELEMENT_ID, @MasterID, 'M001', 'chkitem1', 'Check description'
  FROM 
      dbo.MASTER AS m
  INNER JOIN
      dbo.ELEMENT AS e ON m.MASTER_ID = e.ELEMENT_ID
  WHERE
       m.Master_ID = @MasterID

Upvotes: 4

Related Questions