检查记录是否存在后,同时更新两个表.sqlserver 2012 [英] Update two tables at the same time after checking if the record exist.sqlserver 2012
问题描述
我正在开发使用user_defined_Table_types同时更新两个表的aquery
(Tbl_addition_Store和Tbl_item_Journals)
tbl_addition_store(add_id, date,product_id,Supplier_id,Quanty,price,value)
Tbl_Journals(journal_id,add_id,date,product_id,Supplier_id,Quanty,price,value)
i创建了两个user_defined_Table_types
第一个名为import_products
CREATE TYPE [dbo]。[Import_Products] AS TABLE (
[add_num] [ bigint ] NULL ,
[add_Date] [ date ] NULL ,
[product_id] [ int ] NULL ,
[Supp_id] [ int ] NULL ,
[数量] [十进制]( 18 , 2 ) NULL ,
[price] [ decimal ]( 18 , 2 ) NULL ,
[value] [ decimal ]( 18 , 2 ) NULL
)
并调用第二种类型Insert_Items_Journals
CREATE TYPE [dbo]。[Insert_Items_Journals] AS 表(
[Add_Num] [ bigint ] NULL ,
[ date ] [ date ] < span class =code-keyword> NULL ,
[Product_Id] [ int ] NULL ,
[Supp_Id] [ int ] NULL ,
[数量] [十进制]( 18 , 0 ) NULL ,
[Price] [ decimal ]( 18 , 0 ) NULL ,
[Value] [< span class =code-keyword> decimal ]( 18 , 0 ) NULL
)
之后我创建了一个名为import_All_Items的存储过程。我想保存我的值带有此存储过程的两个表内的datagridview。我希望Query首先检查值是否存在。如果它们是exis查询将更新数量。如果它不存在,查询将添加新记录。问题是查询将数据从第一行保存在tbl_addition_store中但在tbl_item_Journals中保存第二行中的数据
我尝试了什么:
create proc [dbo]。[import_All_Items]
@ import_product Import_Products readonly,
@ Insert_Items_Journals Insert_Items_Journals readonly
as
声明 @ adition cursor
set @ adition = cursor for (选择 add_num,add_Date,product_i d,Supp_id,supp_name,数量,价格,值来自 @ import_product )
打开 @ adition
声明 @ add_num bigint , @ add_date < span class =code-keyword> date , @ productid int , @ Supp_id int , @ supp_name nvarchar ( 150 ), @ quantity decimal ( 18 , 2 ), @ price decimal ( 18 , 2 ), @ value 十进制( 18 , 2 )
fetch @ adition into < span class =code-sdkkeyword> @ add_num , @ add_date , @ productid , @ Supp_id , @ supp_name , @ quantity , @ price , @ value
while @@ FETCH_STATUS = 0
开始
声明 @ insert_Journal cursor
set @ insert_Journal = cursor for (选择 Add_Num,日期,Product_Id,Supp_Id,数量,价格,价值来自 @ Insert_Items_Journals )
open @ insert_Journal
fetch 首先来自 @ insert_Journal 进入 @ add_num , @ add_date , @ productid , @ Supp_id , @ quantity , @ price , @ value
而 @@ FETCH_STATUS = 0
开始
如果 存在(选择 Add_Id 来自 Tbl_Addition_Store 其中日期= @ add_date 和 Product_Id = @ productid)
开始
update Tbl_Addition_Store set 数量=数量+ @数量,价值=价值+ @价值
其中 Add_Id =(选择 Add_Id 来自 Tbl_Addition_Store 其中日期= @ add_date 和 Product_Id = @ productid)
update Tbl_Item_Journals set 数量=数量+ @数量,V alue = Value + @ value
其中 Add_Sarf_Id =( select Add_Sarf_Id 来自 Tbl_Item_Journals 其中 journal_date = @add_date 和 product_id = @ productid)
end
else
开始
INSERT INTO [dbo]。[Tbl_Addition_Store]
([Add_Num]
,[日期]
,[Product_Id]
,[Supp_Id]
,[Supp_Name]
,[数量]
,[价格]
,[价值])
值( @ add_num , @ add_date , @ productid , @ Supp_id , @ supp_name , @ quantity , @ price , @ value )
fetch next 从 @ adition 进入 @ add_num , @ add_date , @ productid , @ Supp_id , @ supp_name , @ quantity , @价格, @ value
INSERT INTO [dbo]。[Tbl_Item_Journals]
(Add_Sarf_Id,
[journal_date]
,[product_id]
,[supp_id]
,[数量]
,[价格]
,[value])
VALUES
( @ add_num , @ add_date , @ productid , @ supp_id , @ quantity , @ price , @ value )
fetch next 来自 @ insert_Journal < span class =code-keyword> into @ add_num , @ add_date , @ productid , @ Supp_id , @ quantity ,< span class =code-sdkkeyword> @ price , @ value
end
fetch @ adition into @ add_num , @ add_date , @ productid , @ Supp_id , @ supp_name , @ quantity , @ price , @ value
fetch @ insert_Journal < span class =code-keyword> into @ add_num , @ add_date , @ productid , @ Supp_id , @ quantity ,< span class =code-sdkkeyword> @ price , @ value
end
fetch @ adition 进入 @ add_num , @ add_date , @ productid , @ Supp_id , @ supp_name , @ quantity , @ price , @ value
end
正如评论中所提到的,你会想要做一些更简单的事情,比如
IF EXISTS ( SELECT 1 FROM table WHERE id = @ id )
BEGIN
- 进行更新
END
ELSE
BEGIN
- 执行插入
END 跨度>
i am developing aquery that Update two Tables at the Same Time with user_defined_Table_types
(Tbl_addition_Store and Tbl_item_Journals)
tbl_addition_store (add_id,date,product_id,Supplier_id,Quanty,price,value)
Tbl_Journals(journal_id,add_id,date,product_id,Supplier_id,Quanty,price,value)
i have created two user_defined_Table_types
the first called import_products
CREATE TYPE [dbo].[Import_Products] AS TABLE(
[add_num] [bigint] NULL,
[add_Date] [date] NULL,
[product_id] [int] NULL,
[Supp_id] [int] NULL,
[quantity] [decimal](18, 2) NULL,
[price] [decimal](18, 2) NULL,
[value] [decimal](18, 2) NULL
)
and the second type called Insert_Items_Journals
CREATE TYPE [dbo].[Insert_Items_Journals] AS TABLE(
[Add_Num] [bigint] NULL,
[date] [date] NULL,
[Product_Id] [int] NULL,
[Supp_Id] [int] NULL,
[Quantity] [decimal](18, 0) NULL,
[Price] [decimal](18, 0) NULL,
[Value] [decimal](18, 0) NULL
)
after that i created a stored procedure called import_All_Items .i want to save values from my datagridview inside the two tables with this stored procedure.I want the Query to check first if the values are existed .if they are existed the query will update the quantity.if it was not existed the query will add new record .the problem is the query saves the data inside tbl_addition_store from the first row but in the tbl_item_Journals it saves the data from the second row
What I have tried:
create proc [dbo].[import_All_Items]
@import_product Import_Products readonly,
@Insert_Items_Journals Insert_Items_Journals readonly
as
declare @adition cursor
set @adition =cursor for(select add_num, add_Date,product_id,Supp_id,supp_name,quantity,price,value from @import_product)
open @adition
declare @add_num bigint,@add_date date,@productid int,@Supp_id int,@supp_name nvarchar(150),@quantity decimal(18,2),@price decimal(18,2),@value decimal(18,2)
fetch @adition into @add_num,@add_date,@productid,@Supp_id,@supp_name,@quantity,@price,@value
while @@FETCH_STATUS=0
begin
declare @insert_Journal cursor
set @insert_Journal =cursor for(select Add_Num,date,Product_Id,Supp_Id,Quantity,Price,Value from @Insert_Items_Journals)
open @insert_Journal
fetch first from @insert_Journal into @add_num,@add_date,@productid,@Supp_id,@quantity,@price,@value
while @@FETCH_STATUS=0
begin
if exists (select Add_Id from Tbl_Addition_Store where Date=@add_date and Product_Id=@productid)
begin
update Tbl_Addition_Store set Quantity=quantity+@quantity,Value=Value+@value
where Add_Id=(select Add_Id from Tbl_Addition_Store where Date=@add_date and Product_Id=@productid)
update Tbl_Item_Journals set Quantity=quantity+@quantity,Value=Value+@value
where Add_Sarf_Id=(select Add_Sarf_Id from Tbl_Item_Journals where journal_date=@add_date and product_id=@productid)
end
else
begin
INSERT INTO [dbo].[Tbl_Addition_Store]
([Add_Num]
,[Date]
,[Product_Id]
,[Supp_Id]
,[Supp_Name]
,[Quantity]
,[Price]
,[Value])
values (@add_num,@add_date,@productid,@Supp_id,@supp_name,@quantity,@price,@value)
fetch next from @adition into @add_num,@add_date,@productid,@Supp_id,@supp_name,@quantity,@price,@value
INSERT INTO [dbo].[Tbl_Item_Journals]
(Add_Sarf_Id,
[journal_date]
,[product_id]
,[supp_id]
,[quantity]
,[price]
,[value])
VALUES
(@add_num,@add_date,@productid,@supp_id,@quantity,@price,@value)
fetch next from @insert_Journal into @add_num,@add_date,@productid,@Supp_id,@quantity,@price,@value
end
fetch @adition into @add_num,@add_date,@productid,@Supp_id,@supp_name,@quantity,@price,@value
fetch @insert_Journal into @add_num,@add_date,@productid,@Supp_id,@quantity,@price,@value
end
fetch @adition into @add_num,@add_date,@productid,@Supp_id,@supp_name,@quantity,@price,@value
end
As mentioned in the comments, you'll want to do something much simpler like
IF EXISTS (SELECT 1 FROM table WHERE id = @id) BEGIN -- do an update END ELSE BEGIN -- do an insert END
这篇关于检查记录是否存在后,同时更新两个表.sqlserver 2012的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!