这个存储过程有什么问题 [英] What's wrong with this stored procedure

问题描述

大家好。

我正在尝试使用通过c＃代码传递的数据表来填充sql 2012表，使用存储过程和用户​​定义的表作为类型。



我认为数据表以及c＃代码都没问题，因为如果我的结束表是空的，那么一切都像魅力一样。



但是如果我已经在结束表中有一些记录，那么存储过程会更新它们但它不会插入新的记录。



这是我的存储过程：

Hi everybody.
I'm trying to populate an sql 2012 table using a datatable passed via c# code, using a stored procedure and a user defined table as type.

I assume that the datatable, as well as the c# code are ok, because if my end table is empty everything works like a charm.

But if I already have some records in the end table, the stored procedure does update them but it doesn't insert the new ones.

This is my stored procedure:

USE [FTBL]
GO
/****** Object:  StoredProcedure [dbo].[usp_insCountries]    Script Date: 31/08/2014 11:49:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_insCountries] @dt AS dbo.udt_InsCountries READONLY
AS
BEGIN
	UPDATE dbo.tbl_countries_feed
	SET country_Id = dt.country_Id,
	country_Name = dt.country_Name,
	country_Fifa_Code = dt.country_Code
	FROM dbo.tbl_countries_feed AS countries INNER JOIN @dt AS dt
	ON countries.country_ID = dt.country_Id;

	/*WITH countries AS
	(SELECT country_Id, country_Name, country_Code FROM @dt AS dt 
	WHERE NOT EXISTS (SELECT country_ID FROM dbo.tbl_countries_feed))
	*/
  INSERT dbo.tbl_countries_feed(country_Id, country_Name, country_Fifa_Code)
	SELECT country_Id, country_Name, country_Code FROM @dt
	WHERE NOT EXISTS (SELECT country_Id, country_Name, country_Code FROM dbo.tbl_countries_feed);
END

我认为not exists条款有问题..

当你我可以看到，我也尝试过使用CTE在第一步中过滤记录，但没有任何改变。



有什么建议吗？



Thx提前。

I think there's something wrong with the not exists clause..
As you can see, I also tried using a CTE to filter the records in a first step, but nothing changed.

Any suggestion?

Thx in advance.

推荐答案

Gotcha！

Gotcha!

USE [FTBL]
GO
/****** Object:  StoredProcedure [dbo].[usp_insCountries]    Script Date: 31/08/2014 11:49:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_insCountries] @dt AS dbo.udt_InsCountries READONLY
AS
BEGIN
	UPDATE dbo.tbl_countries_feed
	SET country_Id = dt.country_Id,
	country_Name = dt.country_Name,
	country_Fifa_Code = dt.country_Code
	FROM dbo.tbl_countries_feed AS countries INNER JOIN @dt AS dt
	ON countries.country_ID = dt.country_Id;

	/*WITH countries AS
	(SELECT country_Id, country_Name, country_Code FROM @dt AS dt 
	WHERE NOT EXISTS (SELECT country_ID FROM dbo.tbl_countries_feed))
	*/
  INSERT dbo.tbl_countries_feed(country_Id, country_Name, country_Fifa_Code)
	SELECT country_Id, country_Name, country_Code FROM @dt as dt
	WHERE country_Id NOT IN(SELECT country_Id FROM dbo.tbl_countries_feed)
END

我只需要让我的T-SQL知识更深入..



周日愉快！

I just need to make my T-SQL knowledge deeper..

Have a nice Sunday!

这篇关于这个存储过程有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！