CODEWITHSUNDEEP
sqlsql-serverinsert
Eve
Eve

Reputation: 212

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

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

Upvotes: 2

Views: 197

Answers (2)

Deep
Deep

Reputation: 3202

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.

Upvotes: 0

Andrey Korneyev
Andrey Korneyev

Reputation: 26846

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

Upvotes: 2

Related Questions