Click here to Skip to main content
12,304,228 members (69,833 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
I had two tables in same db named as Tablel1 one Tablel2.
Tablel1 is main table.


Tabel1 columns
(Id int not null,
Name nvarchar not null,
State nvarchar not null,
City nvarchar not null,......)

Tabel2 columns
(Reg_id int not null,
C_name nvarchar not null,
State nvarchar not null,
City nvarchar not null,......)

then i created a store procedure named as "insert_details"

Tabel1 column "Id" is auto generate
create procedure insert_details
(@Name nvarchar(50), @State nvarchar(50), @City nvarchar(50),.......)
as begin
insetr intto Tabel1(Name,State,City,....)values(@Name,@State,@City,....)
-- for below insert i need Id value in Tabel2 ---
-- this procedure not works--
insert into Tabel2(Reg_id ,C_name,State,City,...)values(@Reg_id, @C_name,@State,@City,...)



---- Please some one help me Sir -----
Posted 28-Nov-12 19:26pm
Comments
__TR__ 29-Nov-12 1:39am
   
Use SCOPE_IDENTITY()[^] to capture the auto generated ID and store it in a variable. Then use that variable to insert the ID of Table1 in Table2.
sathishyadhuv 29-Nov-12 5:54am
   
Thanks Soooo much........!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi sathish,

try this,

Create procedure insert_details (@Name nvarchar(50), @State nvarchar(50), @City nvarchar(50) )
as
insert into Table_1 values(@Name,@State,@City);
declare @id int;
set @id = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]);
insert into Table_2 values(@id,@Name,@State,@City);

--SJ
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Give identity insert for the table1 then only you can get the id column value
below is code to do
CREATE TABLE Tabel1
 (Id int not null Identity(1,1),
 Name nvarchar not null,
 State nvarchar not null,
 City nvarchar not null)
  
 CREATE TABLE  Tabel2
 (Reg_id int not null,
 C_name nvarchar not null,
 State nvarchar not null,
 City nvarchar not null)
 
 GO
 
 CREATE procedure insert_details
 (@Name nvarchar(50), @State nvarchar(50), @City nvarchar(50))
 as begin
 DECLARE @ID BIGINT
 insert into Tabel1(Name,State,City)values(@Name,@State,@City) 
 
 SET @ID=@@IDENTITY 
 insert into Tabel2(Reg_id ,C_name,State,City)values(@ID, @Name,@State,@City)
 END
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160530.1 | Last Updated 29 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100