insert with select query

Question

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

marc_s · Accepted Answer · 2013-09-19 08:11:03Z

4

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

edited Sep 19, 2013 at 8:11

answered Sep 19, 2013 at 5:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ram Prakash Over a year ago

This works fine..but i want get the "master_id" as scope_identity() value from "master" table, and insert that with "check" table column values...can u help me..?

Ram Prakash Over a year ago

yes, exactly First am inserting some data into the Master table. 2.getting the master_id by using Scope_identity(). 3.insert that scope_identity with check table columns such as (master_id, element_id,check_id,check_name,description). Final output of the "check" table should be Master_id(from master table),element_id(from element table),check_id,check_name,description. Is that understandable..?

Collectives™ on Stack Overflow

insert with select query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related