存储过程验证帮助 [英] Stored procedure validation help

问题描述

我有一个.net应用程序，用于更新输入SQL DBDispatchDB和表dbo.dps_data_new的信息。数据验证完成在这个表上有一个存储过程 IF存在更新，否则插入 PFB。我还有两个名为dbo.HC_File和dbo.DPS_Types的表。我加入所有这3个表和输出都插入到一个名为dbo.dps_data_new_intermediate的中间表中，该表充当Excel中的主文件，但是在这一点上，我正在做的是为了避免这个中间表中的重复值，使用截断语句，首先截断这个中间表，然后插入数据（参见代码的最后2行）。



现在我的问题是1）有没有办法，我可以避免截断并让中间表中的数据更新行如果存在，则插入吗？2）我之所以想要这样做，是因为表dbo.HC_File中的数据每周都会更新，如果是截断语句用来当插入发生时，连接发生在最近的HC_File上，前几周的数据不会保持完整，这会导致我的报告中出现很多错误。





我试图解释这个问题，尽可能简单，如果有人可以帮助我，很乐意提供应用程序的源代码。这非常紧急。





存储过程：

Hi,

I have a .net application that updates the info entered into a SQL DB "DispatchDB" and Table "dbo.dps_data_new. Data validation is done on this table with a stored procedure IF Exists Update else insert PFB. I, have two more tables named "dbo.HC_File" & "dbo.DPS_Types". I join all these 3 tables and the output is inserted into an intermediate table called "dbo.dps_data_new_intermediate", which acts as a masterfile in Excel, however at this point, what I am doing to avoid duplicate values in this intermediate table is, using a truncate statement, to truncate this intermediate table first and then insert the data (see last 2 lines of the code).

Now my question is 1) Is there a way, where I can avoid truncate and let the data in the intermediate table update rows if exists else insert? 2) The reason why I, want to do this, is because the data in table dbo.HC_File is updated every week, and if truncate statement is used, when insert happens the joins happen from the recent HC_File, and the previous weeks data would not stay intact that would cause a lot of errors in my reporting.


I have tried to explain this question, as simple as I could, Would be happy to provide the source code of the application, if someone could help me. This is pretty urgent.


Stored Procedure:

CREATE PROCEDURE [dbo].[dpssp_InsertOrUpdate]
(
    @Dispatcher char(30), 
	@DPSNum int, 
	@DPSType char(10), 
	@DT datetime, 
	@DPSID int
)
AS BEGIN

IF EXISTS (SELECT * FROM dbo.dps_data_new WHERE [Dispatch_Number]=@DPSNum)

BEGIN

    UPDATE dbo.dps_data_new SET [Dispatcher_Name]=@Dispatcher, [Dispatch_Number]=@DPSNum, [DPS_Type]=@DPSType,[DateTime]=@DT,[DPS_ID]=@DPSID
    WHERE Dispatch_Number=@DPSNum

END

ELSE

BEGIN

    INSERT INTO dps_data_new (Dispatcher_Name, Dispatch_Number, DPS_Type, DateTime, DPS_ID) VALUES( @Dispatcher, @DPSNum, @DPSType, @DT, @DPSID)
    SET @DPSNum=SCOPE_IDENTITY()

END
	
	Truncate Table dbo.dps_data_new_intermediate
	Insert into [dbo].[dps_data_new_intermediate] ([Dispatcher_Name],[Dispatch_Number],[DPS_Type],[Date_Time],[DPS_ID],[Agent_Name],[LoB],[TM],[Dispatch_Type]) Select a.*,b.Name,b.LoB,b.TM_HR,c.Dps_Name from dbo.dps_data_new A Inner join dbo.HC_File B on A.DPS_ID=B.DPS_ID Left Join dbo.DPS_Types C on A.DPS_Type=C.Dps_Type

END

推荐答案

对于核心，你应该使用合并 [ ^ ]声明。



对于其他人来说，完全不清楚任何人都希望如何帮助你，但 http://technet.microsoft.com/en-us/library/dd776381（v = SQL.105）的.aspx＃OtherTables [ ^ ]显示了如何根据选择进行插入，这似乎是你想要的。

For the core, you should use the merge[^] statement.

For the rest, it's not at all clear how anyone can hope to help you, but http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx#OtherTables[^] shows how to do inserts based on selects, which seems to be what you want.

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