Insert in SQL table using select with column values from another select

Question

I have an insert statement in MSSQL as follows:

INSERT INTO A(KeyId, KeyName, Name)
SELECT ('?','?',BName)FROM B

The value of KeyId and KeyName are retreived from another table D

Table B- Table C mapping is BName= XName Table C- Table D mapping is Xname = XName There are like 1000 records from B which need to be inserted into A. how can i write an SP for the same?

Table A Columns - KeyId,KeyName,Name Table B Columns - Id,BName Table C Columns - XName Table D Columns - KeyId,KeyName, XName

You write "SELECT KeyId,KeyName FROM C WHERE Name = 'SomeNameFromTableB'", but there's no NAME column in C... — jarlh
– jarlh, Commented Dec 17, 2014 at 11:23

Andrey Korneyev · Accepted Answer · 2014-12-17 11:23:13Z

2

In SQL Server 2005 and later you can use outer apply:

insert into A(KeyId, KeyName, Name)
select 
    CALC.KeyId,
    CALC.KeyName,
    B.Name
from B
   outer apply (select KeyId,KeyName from C where C.Name = 'SomeNameFromTableB') as CALC

For prior versions you can declare variables, fill it with values and use that variables:

declare @KeyId bigint, @KeyName nvarchar(max)
select @KeyId = KeyId, @KeyName = KeyName FROM C WHERE Name = 'SomeNameFromTableB'

insert into A(KeyId, KeyName, Name)
select 
    @KeyId,
    @KeyName,
    B.Name
from B

UPDATE As from OP's comment:

The value of Name='SomeNamefromTableB' is actually the value i am inserting i.e. B.Name

So, in this case you just can use join:

insert into A(KeyId, KeyName, Name)
select 
    C.KeyId,
    C.KeyName,
    B.BName
from B
   left outer join C on C.XName = B.BName

edited Dec 17, 2014 at 11:23

answered Dec 17, 2014 at 11:14

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

6 Comments

Eve Over a year ago

Hi The value of Name='SomeNamefromTableB' is actually the value i am inserting i.e. B.Name. Will it work if i replace it?

Deep Over a year ago

2nd option can have problem if there are more then one rows returned by select from C. one more thing : as per question Name column is not in table C but used in where clause.

Eve Over a year ago

Updated the question. The column in C is XName. It contains some value from BName in table B

Andrey Korneyev Over a year ago

@Eve in this case you just can use join. I've updated the answer.

Eve Over a year ago

@AndyKorneyev i cannot use a simple join. Its a little more complex than that. I have elaborated the table structure . Updated the question

|

Deep · Accepted Answer · 2014-12-17 11:30:57Z

0

try this :

INSERT INTO A(KeyId,KeyName,Name)
SELECT c.KeyId,
       c.KeyName,
       c.XName
FROM   C
       JOIN B
         ON c.XName = b.Name

This will insert those rows from C where c.XName is present in b.Name.

answered Dec 17, 2014 at 11:30

Deep

3,2021 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

Insert in SQL table using select with column values from another select

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related