插入两个表格 [英] Inserting in two tables

问题描述

你好朋友，

i有两个同时插入两张桌子......

我的桌子是：

Hello friends,
i have two do insertion in two tables at a same time...
my tables are:

CREATE TABLE [dbo].[menu](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [itemname] [nvarchar](50) NOT NULL,
    [item_id] [nvarchar](50) NOT NULL,
    [item_img] [nvarchar](max) NULL,
    [activestatus] [int] NULL,
    [entrydate] [date] NULL,
 CONSTRAINT [PK_menu_1] PRIMARY KEY CLUSTERED
)

和

and

CREATE TABLE [dbo].[submenu](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [item_id] [nvarchar](50) NOT NULL,
    [item_descript] [nvarchar](100) NULL,
    [item_rate] [decimal](18, 2) NULL,
    [subitem_name] [nvarchar](50) NULL,
 CONSTRAINT [PK_submenu] PRIMARY KEY CLUSTERED
)

两个都加入了外键约束列（item_id）



我怎么能，插入两个......表中，请帮助..



SHOUTING删除 - OriginalGriff [/ edit]

both are joined with foreign key constraint in column (item_id)

How do i can, insert in both..tables, please help..

[edit]SHOUTING removed - OriginalGriff[/edit]

推荐答案

首先，表之间的关系应如下所示： menu.id = submenu.menu_id in one很多关系。



其次，你需要将数据插入菜单表并获取身份将数据插入子菜单表。

为此，您可以编写存储过程，如：

First of all, relationship between tables should looks like: menu.id = submenu.menu_id in one to many relationship.

Secondly, you need to insert data into menu table and get identity to insert data into submenu table.
To achieve that, you can write stored procedure like:

CREATE PROCEDURE AddMenu()
    @itemname NVARCHAR(50),
    @item_id NVARCHAR(50),
    @item_img NVARCHAR(MAX),
    @activestatus INT,
    @entrydate DATE
AS
BEGIN
    INSERT INTO [dbo].[menu] ([itemname], [item_id], [item_img], [activestatus], [entrydate])
        VALUES (@itemname, @item_id, @item_img, @activestatus, @entrydate)
    RETURN @@IDENTITY
END

现在，当您知道最后一个 menu.id 时，是时候将数据添加到子菜单 table。

Now, when you know the last menu.id, it''s time to add data into submenu table.

CREATE PROCEDURE AddSubmenu()
    @menu_id INT,
    @item_descript NVARCHAR(100),
    @item_rate DECIMAL(18,2),
    @subitem_name NVARCHAR(50)
AS
BEGIN
    INSERT INTO [dbo].[submenu] ([menu_id], [item_descript], [item_rate], [subitem_name])
        VALUES (@menu_id, @item_descript, @item_rate, @subitem_name)
    RETURN @@IDENTITY
END

这篇关于插入两个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！