在C#和Sql Server中批量上传 [英] Bulk Upload in c# and Sql server

问题描述

DB Guy，

我将大量数据插入具有主键作为Char + Identity Column
的表中 因此，在SP中，我编写了一个脚本，例如获取最大的Identity并添加Char值，然后将其插入DB.
但是问题是实时项目流量更多
所以我的脚本在Prod中不起作用
引发错误主键冲突.

我希望你们能理解我的问题，你们已经面临这个问题.
因此，请为此提供一些解决方案.

问候，
Sabbi

Hi DB Guys,

I am inserting a bulk data into table which had a primary key as Char + Identity Column
So in SP i wrote a script like getting max of Identity and adding Char value and then i am inserting in to DB.
But the problem it''s a live project traffic is more
so my script is not working in Prod
It''s throwing error Primary key violation.

I hope you guys understand my issue and u guys already faced this issue.
So please provide some solution for this.

Regards,
Sabbi

推荐答案

您不能仅基于增加身份来添加记录.您的主键适用于char和int(identity)的组合
说例如:
A1
A2
A3
A4

您也可以拥有
B1，
C1等

因此，仅仅通过增加身份的最大值是行不通的，因为当它在数据库中找到chat和int的组合时，它将违反主键.我认为您正在使用某种逻辑而不是使用数据库中的标识列来自动增加PK.如果您的PK字段带有Char + Identity Column，则您需要输入的char的最大int(identity)，然后递增该数字.

因此，如果您要使用char A输入新记录，则首先找到所有pk的最大值，例如以"A"开头，然后将其递增.

您的查询将是这样的.

从table_name组中按字符类型为``c％的字符''选择``c''+ cast((CAST(SUBSTRING(MAX(字符)，2，LEN(字符))作为整数)+1)作为varchar(IDENT_CURRENT)) ''

You cannot add the record just based on increasing the identity. Your primary key works on combination of char and int(identity)
Say for eg:
A1
A2
A3
A4

You can also have
B1,
C1, etc.

So just by increasing the max of identity will not work as it will violate the primary key when it finds the combination of chat and int already in DB. I think you are auto-incrementing you PK using some logic not using the identity column from DB. if your PK one field with Char + Identity Column then you need the max int(identity) of char you are entering then increment that number.

so if you are entering new record with char A then first find max of like all pk starts with ''A'' then increment it.

Your query will be something like this.

select ''c'' + cast(( CAST( SUBSTRING(MAX(Character),2, LEN(Character)) as integer) + 1) as varchar(IDENT_CURRENT)) from table_name group by Character having Character like ''c%''

这篇关于在C#和Sql Server中批量上传的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！