无法插入到具有 SQL Server 类型的表中

本文介绍了无法插入到具有 SQL Server 类型的表中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个 C# 应用程序，我使用类型插入到 SQL Server 表中:

I have a C# application and I use type to insert into a SQL Server table:

CREATE TYPE [dbo].[taradodType] AS TABLE ( [IDP] [int] NULL, [date] [datetime] NULL, [day] [nvarchar](max) NULL, [nobatkari] [nvarchar](max) NULL, [code] [nvarchar](max) NULL )

C# 代码:

SqlConnection sqlconn = new SqlConnection(DBsetting.Connstring); sqlconn.Open(); using (sqlconn) { try { SqlCommand cmd = new SqlCommand("InsertTaradod", sqlconn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter dtparam = cmd.Parameters.AddWithValue("@taradodType", dtreadd); dtparam.SqlDbType = SqlDbType.Structured; cmd.ExecuteNonQuery(); MessageBox.Show("Saved!"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

存储过程:

ALTER PROCEDURE [dbo].[InsertTaradod] @taradodType dbo.taradodType READONLY AS BEGIN INSERT INTO dbo.taradod SELECT * FROM @taradodType END

而且我不想在表格中插入重复的行.所以我定义了一个索引来不插入重复数据.但是这里有一个问题.如果数据集合中只有 1 个重复行.没有插入任何数据，但我想插入其他没有重复的数据

And I don't want to insert duplicate rows into the table. So I defined an index not to insert duplicate data. But there is a problem here. If there is only 1 duplicated row in data collection. None of the data is inserted, but I want to insert other data which is not duplicated

数据:

1 2017-06-20 13:28:44 1 0 1 0 4 2017-06-22 12:18:13 1 0 1 0 2 2017-06-22 12:49:41 1 0 1 0 3 2017-06-22 13:15:24 1 0 1 0 1 2017-06-22 13:50:07 1 0 1 0 3 2017-06-24 06:56:05 1 0 1 0 1 2017-06-24 07:02:47 1 0 1 0 5 2017-06-24 07:29:37 1 0 1 0 7 2017-06-24 13:18:57 1 0 1 0 6 2017-06-24 13:19:00 1 0 1 0 8 2017-06-24 13:19:03 1 0 1 0 5 2017-06-24 13:19:07 1 0 1 0 3 2017-06-24 14:35:14 1 0 1 0 1 2017-06-24 14:38:04 1 0 1 0 3 2017-06-25 06:45:24 1 0 1 0 2 2017-06-25 06:54:48 1 0 1 0 1 2017-06-25 06:56:02 1 0 1 0 5 2017-06-25 07:51:32 1 0 1 0 6 2017-06-25 07:57:25 1 0 1 0 7 2017-06-25 07:57:30 1 0 1 0 8 2017-06-25 07:57:34 1 0 1 0 2 2017-06-25 13:03:55 1 0 1 0 5 2017-06-25 13:26:34 1 0 1 0 6 2017-06-25 13:32:56 1 0 1 0 8 2017-06-25 13:33:07 1 0 1 0 7 2017-06-25 13:33:10 1 0 1 0 1 2017-06-25 14:38:51 1 0 1 0 3 2017-06-25 14:39:21 1 0 1 0 4 2017-06-28 06:44:48 1 0 1 0 3 2017-06-28 06:45:48 1 0 1 0 1 2017-06-28 06:59:51 1 0 1 0 5 2017-06-28 07:21:28 1 0 1 0 9 2017-06-28 07:38:38 1 0 1 0 8 2017-06-28 07:49:19 1 0 1 0 6 2017-06-28 08:11:29 1 0 1 0 7 2017-06-28 08:11:34 1 0 1 0 8 2017-06-28 13:32:17 1 0 1 0 9 2017-06-28 13:32:20 1 0 1 0 7 2017-06-28 13:32:23 1 0 1 0 6 2017-06-28 13:32:27 1 0 1 0 5 2017-06-28 13:32:42 1 0 1 0 4 2017-06-28 14:17:00 1 0 1 0 3 2017-06-28 14:43:18 1 0 1 0 1 2017-06-28 15:27:57 1 0 1 0 4 2017-06-29 06:28:16 1 0 1 0 1 2017-06-29 06:55:45 1 0 1 0 3 2017-06-29 06:55:53 1 0 1 0 5 2017-06-29 07:34:44 1 0 1 0 8 2017-06-29 07:55:54 1 0 1 0 6 2017-06-29 07:55:57 1 0 1 0 9 2017-06-29 07:56:01 1 0 1 0 7 2017-06-29 08:00:26 1 0 1 0 9 2017-06-29 12:57:04 1 0 1 0 7 2017-06-29 12:57:12 1 0 1 0 8 2017-06-29 12:57:15 1 0 1 0 6 2017-06-29 12:57:33 1 0 1 0 5 2017-06-29 12:57:54 1 0 1 0 4 2017-06-29 13:01:06 1 0 1 0 3 2017-06-29 13:31:41 1 0 1 0 1 2017-06-29 13:31:50 1 0 1 0 4 2017-07-01 06:27:33 1 0 1 0 2 2017-07-01 06:50:55 1 0 1 0 3 2017-07-01 06:51:52 1 0 1 0 1 2017-07-01 07:02:29 1 0 1 0 5 2017-07-01 07:18:49 1 0 1 0 9 2017-07-01 07:27:00 1 0 1 0 8 2017-07-01 07:27:03 1 0 1 0 7 2017-07-01 07:52:45 1 0 1 0 6 2017-07-01 07:52:47 1 0 1 0 2 2017-07-01 12:54:23 1 0 1 0 7 2017-07-01 13:32:05 1 0 1 0 8 2017-07-01 13:32:15 1 0 1 0 9 2017-07-01 13:32:37 1 0 1 0 6 2017-07-01 13:32:52 1 0 1 0 4 2017-07-01 13:32:58 1 0 1 0 5 2017-07-01 13:33:07 1 0 1 0 1 2017-07-01 14:32:01 1 0 1 0 3 2017-07-01 14:32:32 1 0 1 0 4 2017-07-02 06:27:25 1 0 1 0 3 2017-07-02 06:47:46 1 0 1 0 1 2017-07-02 07:00:37 1 0 1 0 2 2017-07-02 07:00:42 1 0 1 0 5 2017-07-02 07:11:05 1 0 1 0 8 2017-07-02 07:23:47 1 0 1 0 9 2017-07-02 07:23:55 1 0 1 0 7 2017-07-02 07:34:37 1 0 1 0 6 2017-07-02 07:34:39 1 0 1 0 2 2017-07-02 13:00:14 1 0 1 0 8 2017-07-02 13:22:30 1 0 1 0 9 2017-07-02 13:22:34 1 0 1 0 6 2017-07-02 13:23:09 1 0 1 0 7 2017-07-02 13:23:23 1 0 1 0 5 2017-07-02 13:26:15 1 0 1 0 4 2017-07-02 14:05:00 1 0 1 0 3 2017-07-02 14:56:15 1 0 1 0 1 2017-07-02 15:27:23 1 0 1 0 4 2017-07-03 06:33:45 1 0 1 0 1 2017-07-03 07:04:39 1 0 1 0 5 2017-07-03 07:15:34 1 0 1 0 6 2017-07-03 08:37:04 1 0 1 0 7 2017-07-03 08:37:07 1 0 1 0 8 2017-07-03 09:59:10 1 0 1 0 9 2017-07-03 09:59:14 1 0 1 0 8 2017-07-03 13:59:10 1 0 1 0 9 2017-07-03 13:59:25 1 0 1 0 5 2017-07-03 13:59:33 1 0 1 0 7 2017-07-03 13:59:42 1 0 1 0 6 2017-07-03 13:59:46 1 0 1 0 4 2017-07-03 14:05:22 1 0 1 0 1 2017-07-03 14:35:43 1 0 1 0 4 2017-07-04 06:28:15 1 0 1 0 2 2017-07-04 06:46:19 1 0 1 0 1 2017-07-04 07:06:26 1 0 1 0 3 2017-07-04 07:10:29 1 0 1 0 5 2017-07-04 07:18:38 1 0 1 0 8 2017-07-04 07:55:10 1 0 1 0 9 2017-07-04 07:55:14 1 0 1 0 1 2017-07-04 07:55:42 1 0 1 0 7 2017-07-04 07:57:51 1 0 1 0 6 2017-07-04 07:57:54 1 0 1 0 1 2017-07-04 08:57:36 1 0 1 0

索引位于 ID 和 datetime.

推荐答案

您的插入命令像批处理一样执行.

Your insert command is executing like a batch.

您的整个类型被传递到 SQL Server 存储过程，它作为要插入的批处理执行.

Your entire type is passed to the SQL Server stored procedure and it executes as a batch to insert.

在您的存储过程的 INSERT 语句中，添加一个 NOT EXISTS 检查，如下所示:

In your stored procedure's INSERT statement, add a NOT EXISTS check like this:

INSERT INTO dbo.taradod SELECT Distinct * FROM @taradodType a WHERE NOT EXISTS (SELECT 1 FROM dbo.taradod WHERE IDP = a.IDP AND Code = a.Code ...and other conditions which decide duplicate rows for you.)

或者，您可以从 @taradodType 中删除 dbo.taradod 中已经存在的行，然后执行插入语句.

Alternatively, you can delete rows from @taradodType which already exist in dbo.taradod and then execute your insert statement.