我想知道在将一个数据表传递给SqlBulkCopy时,是否可以忽略已经存在于SQL数据库中的重复记录。如果可以,请告诉我如何操作;如果不行,还有其他什么选择。
正如之前的帖子所说,这不是内置的。我使用以下方法实现类似的功能:
SQL存储过程接受一个包含所需数据的TableValuedParameter。
在存储过程中,我将所有记录插入到临时表中。一旦数据到达那里,您可以在存储过程中使用SQL的MERGE语句来插入尚不存在的数据。
因此,让我们假设我们的数据仅是存储在名为“people”的表中的人名。我们只保留ID和名称。我还假设此表称为“people”。
下面是我创建的Table Valued Parameter类型(在SQL Server中创建):
CREATE TYPE udt_person AS TABLE(
[id] [INT] NOT NULL,
[name] [nvarchar(50)] NULL
)
GO
CREATE PROCEDURE SaveNewPeople @pPeople udt_Person
AS
BEGIN
-- Create Temp table
CREATE TABLE #tmpPeople (id INT, name VARCHAR 50)
-- We will stage all data passed in into temp table
INSERT INTO #tmpPeople
SELECT id, name FROM @pPeople
-- NB: you will need to think about locking strategy a bit here
MERGE people AS p
USING #tmpPeople AS t
ON p.id = t.id
WHEN NOT MATCHED BY TARGET THEN
-- We want to insert new person
INSERT (id, name) VALUES (t.id, t.name)
WHEN MATCHED THEN
-- you may not need this, assume updating name for example
UPDATE SET p.name = t.name
END
现在我们已经有了SQL。
让我们用C#创建大量数据:
DataTable ppl = new DataTable();
ppl.Columns.Add("id", typeof(int));
ppl.Columns.Add("name", typeof(string));
// table is created, let's add some people
var bob = ppl.NewRow();
bob["id"] = 1;
bob["name"] = "Bob";
ppl.Rows.Add(bob);
var jim = ppl.NewRow();
jim["id"] = 2;
jim["name"] = "Jim";
ppl.Rows.Add(jim);
// that's enough people for now, let's call the stored procedure
using(var conn = new SqlConnection("YouConnStringHere"))
{
using(var cmd = new SqlCommand("SaveNewPeople", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
var tvp = new SqlParameter
{
ParameterName = "@pPeople",
SqlDbType = SqlDbType.Structured,
Value = ppl,
TypeName = "udt_person"
}
cmd.Parameters.Add(tvp);
conn.Open();
cmd.ExecuteNonQuery();
}
}
CREATE TABLE [dbo].[TempTable] (
[Id] INT IDENTITY PRIMARY KEY,
[Val] NVARCHAR(20)
)
GO
CREATE OR ALTER TRIGGER [IgnoreDuplicates] ON [dbo].[TempTable]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[TempTable]([Val])
SELECT [Val] FROM [INSERTED] WHERE [Val] NOT IN (
SELECT [Val] FROM [dbo].[TempTable]
)
END
GO