Inserting a row to a table using query result values from another table

Question

I have a stored procedure:

CREATE PROCEDURE MyProc
(
    @Param1  [datetime],
    @Param2  [nvarchar](20),
    @Param3  [nvarchar](20),
    @Param4  [nvarchar](20),
    @Param5  [nvarchar](20),
)
AS
BEGIN   
INSERT INTO MyTable1
  (
    Field1,
    Field2,
    Field3,
    Field4,
    Field5
  )   
 SELECT         
    @Param1,
    @Param2,
    @Param3,
    Field12,
    'constantValue'         
FROM   MyTable2
WHERE  Field13 = @Param4
END

How could I change the stored proc in order it inserts into the Field5 of MyTable1 not the constantValue but a result of yet another query to MyTable2 with the last parameter (@Param5)?
I.e.:

FROM   MyTable2
WHERE  Field13 = @Param5

I want two values from the same field Field12 but from different records of MyTable2 — rem
– rem, Commented Nov 21, 2011 at 16:04

Mahmoud Gamal · Accepted Answer · 2012-07-11 01:00:17Z

4

INSERT INTO MyTable1
(
  Field1,
  Field2,
  Field3,
  Field4,
  Field5
)   
SELECT         
    @Param1,
    @Param2,
    @Param3,
    Field12,
    (
         select field_name from MyTable2 where Field13 = @Param5
    )
FROM   
    MyTable2
WHERE 
    Field13 = @Param4
END

Or declare a new variable:

SET @newparam = (SELECT field_name FROM MyTable2 WHERE Field13 = @Param5 )

Then insert this new parameter into the insert statement.

edited Jul 11, 2012 at 1:00

answered Nov 21, 2011 at 16:03

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Comments

ean5533 · Accepted Answer · 2011-11-21 16:03:31Z

3

CREATE PROCEDURE MyProc
(
    @Param1  [datetime],
    @Param2  [nvarchar](20),
    @Param3  [nvarchar](20),
    @Param4  [nvarchar](20),
    @Param5  [nvarchar](20),
)
AS
BEGIN   
INSERT INTO MyTable1
  (
    Field1,
    Field2,
    Field3,
    Field4,
    Field5
  )   
 SELECT         
    @Param1,
    @Param2,
    @Param3,
    Field12,
    (SELECT thing_i_care_about FROM MyTable2 WHERE Field13 = @Param5)
FROM   MyTable2
WHERE  Field13 = @Param4
END

answered Nov 21, 2011 at 16:03

ean5533

9,0143 gold badges42 silver badges65 bronze badges

Comments

gbn · Accepted Answer · 2011-11-21 16:04:14Z

2

Assumes one row from each SELECT

SELECT         
    @Param1, @Param2, @Param3, Tp4.Field12, Tp5.Field12
    NULL        
FROM
   MyTable2 Tp4
   CROSS JOIN
   MyTable2 Tp5
WHERE
   Tp4.Field13 = @Param4 AND Tp4.Field13 = @Param5

The JOIN changes depending on what you expect eg FULL OUTER JOIN .. ON 1=1

If either query returns more than 1 row then:

in-line sub-query fails
insert doesn't make sense: what to insert with 4 rows and 3 rows?

answered Nov 21, 2011 at 16:04

gbn

435k84 gold badges602 silver badges690 bronze badges

Comments

Elias Hossain · Accepted Answer · 2011-11-21 17:31:31Z

It can be accomplish in different ways as below, in both cases I think we should consider a thing that is what if the returned data might be more than one.

a.

INSERT INTO MyTable1
(
 Field1,
 Field2,
 Field3,
 Field4,
 Field5
)   

SELECT         
 @Param1,
 @Param2,
 @Param3,
 Field12,
  (SELECT TOP 1 Column_Name FROM MyTable2 WHERE Field13 = @Param5)
FROM   MyTable2
WHERE  Field13 = @Param4

b.

DECLARE @Field13 VARCHAR(20)
SELECT TOP 1 @Field13  = FROM MyTable2 WHERE Field13 = @Param5

INSERT INTO MyTable1
(
 Field1,
 Field2,
 Field3,
 Field4,
 Field5
)   

SELECT         
 @Param1,
 @Param2,
 @Param3,
 Field12,
 @Field13
FROM   MyTable2
WHERE  Field13 = @Param4

In either cases will be success though "Field13 = @Param5" condition may reveal multiple data but will work on first(TOP 1) data.

Collectives™ on Stack Overflow

Inserting a row to a table using query result values from another table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related