CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedurescursor
hozefam
hozefam

Reputation: 1040

Insert Table parameter to 2 different tables within a stored procedure

I have a table-valued parameter @supplierData in a stored procedure which has columns as follows

sName | sLocation | currYearExpt | currYearImpt | prevYearExpt | prevYearImpt
----------
XXX   | USA       | 1000         | 2000         | 1000         | 2050
YYY   | U.K       | 8000         | 12000        | 10000        | 29000

The table needs to be inserted to the below 2 tables

SUPPLIER table:

sId        | sName | sLocation |
(Identity) |       |           |
----------
 1         | XXX   | USA       |
 2         | YYY   | U.K       |

and

SUPPLIERRECORD table: 

YearId | sId | currYearExpt | currYearImpt | prevYearExpt | prevYearImpt|
----------
 1     | 1   | 1000         | 2000         | 1000         | 2050        |
 2     | 2   | 8000         | 12000        | 10000        | 29000       |

Where in the 2nd table is having reference to the sId created as part of the same stored procedure execution.

Should I insert record by record from @supplierData parameter?

Upvotes: 0

Views: 695

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

You could use the DENSE_RANK function (https://msdn.microsoft.com/en-us/library/ms173825.aspx) with OVER to specify the order and a partitioning criteria. So you'll get numeric IDs for your suppliers. You could insert them into a table parameter first and than use this for your two INSERTs

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Yes, you can use insert into .. select from construct like below

insert into SUPPLIER(sName , sLocation)
select sName , sLocation 
from @supplierData;

insert into SUPPLIERRECORD (currYearExpt , currYearImpt , prevYearExpt , prevYearImpt)
select currYearExpt , currYearImpt , prevYearExpt , prevYearImpt
from @supplierData;

Upvotes: 0

hungndv
hungndv

Reputation: 2141

Here you are. Let use insert into select or select into, they are created to do the kind of this:

DECLARE @supplierData TABLE
(
  sName nvarchar(50),
  sLocation nvarchar(50),
  currYearExpt int,
  currYearImpt int,
  prevYearExpt int,
  prevYearImpt int
);

DECLARE @SUPPLIER TABLE
(
  sId int,
  sName nvarchar(50),
  sLocation nvarchar(50)
);
insert into @SUPPLIER values(1,'XXX','USA');
insert into @SUPPLIER values(2,'YYY','U.K');

DECLARE @SUPPLIERRECORD TABLE
(
  YearId int,
  sId int,
  currYearExpt int,
  currYearImpt int,
  prevYearExpt int,
  prevYearImpt int
);
insert into @SUPPLIERRECORD values(1,1,1000,2000,1000,2050);     
insert into @SUPPLIERRECORD values(2,2,8000,12000,10000,29000);

insert into @supplierData
select a.sName, a.sLocation, b.currYearExpt, b.currYearImpt, b.prevYearExpt, b.prevYearImpt
from @SUPPLIER a inner join @SUPPLIERRECORD b on a.sId = b.sId

select * from @supplierData

Hope this helps.

Upvotes: 0

Related Questions