我使用Azure SQL数据库(Microsoft SQL Azure(RTM) - 12.0.2000.8 2016年12月5日21:15:30),我正在尝试配置一个表(CHART),以便我不会得到任何重复的记录。 表结构如下:
Chart_ID Primary Key (Identity Key) Chart_Date Date Chart_Code VarChar (This is a unique value) Chart_Value Int我正在使用更新或插入新查询。 目前该表有以下记录:
1, 2016-12-10, 12Dec10-00TAM00, 10如果我Upsert以下记录:
0, 2016-12-10, 12Dec10-00TAM00, 10 (Note I use a zero for the primary key so the database will assign the correct primary key)数据库将插入一个Chart_Id = 2的新记录,但我不希望它。 对我来说,这是一个重复的记录。
我也做了一些研究,发现了一些关于Unique Constraints的信息。
从我发现我相信我可以做的事情
Use TestDB; Go Alter Table CHART ADD CONSTRAINT AK_UNIQUECHART_CODE UNIQUE (CHART_CODE);实现无重复记录目标的最佳实践是什么?
感谢您的输入。
I working with an Azure SQL Database ( Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 5 2016 21:15:30) and I'm trying to configure a table (CHART) so that I will not get any duplicate records. The table structure is as follows:
Chart_ID Primary Key (Identity Key) Chart_Date Date Chart_Code VarChar (This is a unique value) Chart_Value IntI'm using an Update or Insert if New query. Currently the table has the following record:
1, 2016-12-10, 12Dec10-00TAM00, 10If I Upsert the following record:
0, 2016-12-10, 12Dec10-00TAM00, 10 (Note I use a zero for the primary key so the database will assign the correct primary key)The data base will insert an new record with a Chart_Id = 2, but I don't want it to. To me this is a duplicate record.
I also did a little research and found some information on Unique Constraints.
From what I found I believe I could do something like
Use TestDB; Go Alter Table CHART ADD CONSTRAINT AK_UNIQUECHART_CODE UNIQUE (CHART_CODE);What's the best practice for accomplishing the goal of no duplicate records.
Thanks for your input.
最满意答案
您可以使用某些版本的Sam Saffron的upsert方法 。
粗略草案看起来像这样:
create procedure dbo.Chart_upsert ( @Chart_ID int /* not used in this draft */ , @Chart_Date date , @Chart_Code varchar(32) , @Chart_Value int ) as begin set nocount on; set xact_abort on; begin tran update dbo.Chart with (serializable) set Chart_Value = @Chart_Value where Chart_Code = @Chart_Code if @@rowcount = 0 begin; insert dbo.Chart (Chart_Date, Chart_Code, Chart_Value) values (@Chart_Date, @Chart_Code, @Chart_Value); end; commit tran end;You could use some version of Sam Saffron's upsert method.
A rough draft would look something like this:
create procedure dbo.Chart_upsert ( @Chart_ID int /* not used in this draft */ , @Chart_Date date , @Chart_Code varchar(32) , @Chart_Value int ) as begin set nocount on; set xact_abort on; begin tran update dbo.Chart with (serializable) set Chart_Value = @Chart_Value where Chart_Code = @Chart_Code if @@rowcount = 0 begin; insert dbo.Chart (Chart_Date, Chart_Code, Chart_Value) values (@Chart_Date, @Chart_Code, @Chart_Value); end; commit tran end;更多推荐
发布评论