about sql query for my mini project

Question

2.50/5 (2 votes)

See more:

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

sathish4303

Add a Solution

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.

sathish4303 29-Nov-12 5:54am

Thanks Soooo much........!

2 solutions

Add a Solution

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

codeninja-C# · Accepted Answer · 2012-11-28T20:33:00

Hi sathish,

try this,

SQL

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

bopannavb · Accepted Answer · 2012-11-28T20:09:00

Give identity insert for the table1 then only you can get the id column value
below is code to do

SQL

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

about sql query for my mini project

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

about sql query for my mini project

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us