关于迷你项目的sql查询 [英] about sql query for my mini project

问题描述

我在同一个数据库中有两个表，分别命名为Tablel1和一个Tablel2.
Tablel1是主表.


Tabel1列
(Id int不为null，
名称nvarchar不为空，
状态nvarchar不为null，
城市nvarchar不能为null，......)

Tabel2列
(Reg_id int不为null，
C_name nvarchar不为null，
状态nvarchar不为null，
城市nvarchar不能为null，......)

然后我创建了一个存储过程，名为"insert_details"

Tabel1列"Id"是自动生成的
创建过程insert_details
(@Name nvarchar(50)，@ State nvarchar(50)，@ City nvarchar(50)，.......)
作为开始
insetr intto Tabel1(Name，State，City，....)值(@ Name，@ State，@ City，....)
-对于下面的插入，我需要在Tabel2中添加Id值---
-此程序不起作用-
插入Tabel2(Reg_id，C_name，State，City，...)值(@Reg_id，@ C_name，@ State，@ City，...)



----主席先生，请有人帮我-----

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 -----

推荐答案

您好，sathish，

试试这个，

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

--SJ

为表1插入标识，那么只有您可以获取id列值
下面是要做的代码

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

这篇关于关于迷你项目的sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！