如果两个表相互依赖，如何使用两个查询在两个表(产品，颜色)中插入数据 [英] How can i insert data in two table (products, color) using single query.if both table are dependent to each other

问题描述

我的数据库中有两个表(产品，颜色)

-products表具有三个字段(id，名称，类型).

-color表也具有三个字段(color_id，prod_id，color).

-两个表之间存在关系(id是prod_id中"products"中的主键，而color中则是prod_id中的外键).

如何使用单个查询在两个表(产品，颜色)中插入数据.
我正在使用SQL 2005

I have two tables in my database (products, color)

-products table has three fields (id, name, type).

-color table has three fields too (color_id, prod_id, color).

-there are relation between two tables (id is a primary key in "products" and foreign key in "color" as prod_id).

How can i insert data in two table (products, color) using single query.
I am using SQL 2005

推荐答案

有几种方法可以做到这一点. 1.是通过使用存储过程来完成的，因为您并未真正提供有关正在执行哪种类型的插入的大量数据.我假设您的主键是identity类型(自动生成).因此，Im使用@@identity系统功能从产品表中返回最后插入的ID..

There are several ways of doing this..
1. is by the use of a stored procedure and since you didnt really provide much data on what type of insert you are doing. I assumed your primary keys were identity types (auto-generate). so Im using the @@identity system function to return the last inserted id from your product table..

create procedure sp_insertData
@prod_name varchar(100),
@prod_type varchar(50),
@color_name varchar(50)
as
begin
   insert into products(name,type)
   values( @prod_name,
           @prod_type
         )

   declare @id int
   select @id = @@identity

   insert into color(prod_id, color)
   values(@id,
          @color_name
         )
end

2.您还可以使用Triggers ..，这样就必须在第二个表上设置外键约束以引用第一个表.然后，触发器将根据您要插入的内容自动将您添加到第二张表中.

2. You could also use Triggers..and in this way you would have to set a foreign key constraint on your second table to reference the first table. A trigger would then automatically do your addition to your second table based on what you want to insert.

所以我不清楚您一次要插入哪个对象

但是我认为在查询中您可以通过使用``;''分隔每个插入查询来传递两个插入查询，您可以一次插入.

So I am not clearly understanding by which you want to insert at once

But I think in the Query you can pass both insert query by separating each using '';'', You can insert at once.

这篇关于如果两个表相互依赖，如何使用两个查询在两个表(产品，颜色)中插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！