62,072
社区成员
发帖
与我相关
我的任务
分享
mainId mainName
----------- --------------------
1 main1
subId mainId subName
----------- ----------- --------------------
1 1 sub1
2 1 sub2
3 1 sub3
--1 创建主表
IF OBJECT_ID('Main') IS NOT NULL
BEGIN
DROP TABLE Main
END
GO
CREATE TABLE Main(
mainId INT PRIMARY KEY IDENTITY(1,1),
mainName NVARCHAR(20)
)
GO
--2 创建子表
IF OBJECT_ID('Sub') IS NOT NULL
BEGIN
DROP TABLE Sub
END
GO
CREATE TABLE Sub(
subId INT PRIMARY KEY IDENTITY(1,1),
mainId INT NOT NULL,
subName NVARCHAR(20)
)
GO
--3 创建插入数据的存储过程
IF OBJECT_ID('proc_InsertMainAndSub') IS NOT NULL
BEGIN
DROP PROCEDURE proc_InsertMainAndSub
END
GO
CREATE PROC proc_InsertMainAndSub
AS
BEGIN
BEGIN TRAN tran1
BEGIN TRY
DECLARE @mainId INT
INSERT INTO Main VALUES ('main1')
SELECT @mainId = @@identity
INSERT INTO Sub VALUES (@mainId, 'sub1')
INSERT INTO Sub VALUES (@mainId, 'sub2')
INSERT INTO Sub VALUES (@mainId, 'sub3')
COMMIT TRAN tran1
END TRY
BEGIN CATCH
ROLLBACK TRAN tran1
END CATCH
END
GO
--执行存储过程
EXEC proc_InsertMainAndSub
--查看数据
SELECT * FROM Main m
SELECT * FROM Sub s
--删除测试
DROP TABLE Main
DROP TABLE Sub
DROP PROCEDURE proc_InsertMainAndSub