c# - 从用户定义的表更新多个表

Question

您好，我有以下存储过程，使用用户定义的表类型可以正常工作。

ALTER procedure [dbo].[wl]
@test [dbo].[testType] readonly 
 as 
Begin
merge into t_values as Target
using @test as Source 
on Target.Id =Source.Id
when matched then 
update set target.Num=Source.Num
when not matched then 
insert (Id,Num)
values (Source.Id,  Source.Num);
End

我还有两个表 test2 和 test3，我需要从源更新一些值。我不确定我该怎么做。基本上我的目标是当条件匹配时更新或插入值到所有三个表中，因为外键关系。请让我知道我该怎么做。谢谢

Answer 1

您需要使用“输出”子句来捕获插入的行的“键”......在它们被插入之后。

这是一个例子……不是完全匹配……但展示了这个想法。

http://granadacoder.wordpress.com/2008/12/10/sqlserver20052008-output-clause-in-insertupdatedelete-statements/

编辑:

这是我做的一个例子......我插入一个“人”(父实体)和一些电子邮件地址(子实体)。我切碎了 xml，你的数据表应该是相似的。

关键是我“审核”(使用输出子句)生成的 IDENTITY，我插入一个人......但我将该信息与自然唯一标识符(本例中的 SSN)保持一致.当我插入电子邮件地址时，我有我插入的人的身份。

我的#TempTables 将是您最终解决方案中的“真实表格”。我的@VariableTable 是“审计”表，用于收集和存储“输出”子句收集的信息。

IF OBJECT_ID('tempdb..#DestinationPersonParentTable') IS NOT NULL
begin
        drop table #DestinationPersonParentTable
end



IF OBJECT_ID('tempdb..#DestinationEmailAddressPersonChildTable') IS NOT NULL
begin
        drop table #DestinationEmailAddressPersonChildTable
end



CREATE TABLE #DestinationPersonParentTable
(
PersonParentSurrogateIdentityKey int not null identity (1001, 1), 
SSNNaturalKey int, 
HireDate datetime
)



declare @PersonOutputResultsAuditTable table
(
SSNNaturalKey int, 
PersonParentSurrogateIdentityKeyAudit int
)





CREATE TABLE #DestinationEmailAddressPersonChildTable
(
DestinationChildSurrogateIdentityKey int not null identity (3001, 1), 
PersonParentSurrogateIdentityKeyFK int, 
EmailAddressValueNaturalKey varchar(64),
EmailAddressType int
)





-- Declare XML variable

DECLARE @data XML;

-- Element-centered XML

SET @data = N'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Person>
        <SSN>222222222</SSN>
        <HireDate>2002-02-02</HireDate>
    </Person>

    <Person>
        <SSN>333333333</SSN>
        <HireDate>2003-03-03</HireDate>
    </Person>

    <EmailAddress>
        <SSNLink>222222222</SSNLink>
        <EmailAddressValue>g@g.com</EmailAddressValue>
        <EmailAddressType>1</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>222222222</SSNLink>
        <EmailAddressValue>h@h.com</EmailAddressValue>
        <EmailAddressType>2</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>333333333</SSNLink>
        <EmailAddressValue>a@a.com</EmailAddressValue>
        <EmailAddressType>1</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>333333333</SSNLink>
        <EmailAddressValue>b@b.com</EmailAddressValue>
        <EmailAddressType>2</EmailAddressType>
    </EmailAddress>

</root>

';




INSERT INTO #DestinationPersonParentTable ( SSNNaturalKey ,  HireDate )

output inserted.SSNNaturalKey , inserted.PersonParentSurrogateIdentityKey  into @PersonOutputResultsAuditTable ( SSNNaturalKey , PersonParentSurrogateIdentityKeyAudit)

SELECT T.parentEntity.value('(SSN)[1]', 'INT') AS SSN,
       T.parentEntity.value('(HireDate)[1]', 'datetime') AS HireDate
FROM @data.nodes('root/Person') AS T(parentEntity)
/* add a where not exists check on the natural key */
where not exists (
    select null from #DestinationPersonParentTable innerRealTable where innerRealTable.SSNNaturalKey = T.parentEntity.value('(SSN)[1]', 'INT') )
;

/* Optional.  You could do a UPDATE here based on matching the #DestinationPersonParentTableSSNNaturalKey = T.parentEntity.value('(SSN)[1]', 'INT')
You could Combine INSERT and UPDATE using the MERGE function on 2008 or later.
 */


select 'PersonOutputResultsAuditTable_Results' as Label, * from @PersonOutputResultsAuditTable


INSERT INTO #DestinationEmailAddressPersonChildTable (  PersonParentSurrogateIdentityKeyFK ,  EmailAddressValueNaturalKey , EmailAddressType )
SELECT  par.PersonParentSurrogateIdentityKeyAudit , 
        T.childEntity.value('(EmailAddressValue)[1]', 'varchar(64)') AS EmailAddressValue,
        T.childEntity.value('(EmailAddressType)[1]', 'INT') AS EmailAddressType
FROM @data.nodes('root/EmailAddress') AS T(childEntity)
/* The next join is the "trick".  Join on the natural key (SSN)....**BUT** insert the PersonParentSurrogateIdentityKey into the table */
join @PersonOutputResultsAuditTable par on par.SSNNaturalKey = T.childEntity.value('(SSNLink)[1]', 'INT')
where not exists (
    select null from #DestinationEmailAddressPersonChildTable innerRealTable where innerRealTable.PersonParentSurrogateIdentityKeyFK = par.PersonParentSurrogateIdentityKeyAudit AND  innerRealTable.EmailAddressValueNaturalKey = T.childEntity.value('(EmailAddressValue)[1]', 'varchar(64)'))
;



print '/#DestinationPersonParentTable/'
select * from #DestinationPersonParentTable


print '/#DestinationEmailAddressPersonChildTable/'
select * from #DestinationEmailAddressPersonChildTable


select SSNNaturalKey , HireDate , '---' as Sep1 , EmailAddressValueNaturalKey , EmailAddressType , '---' as Sep2, par.PersonParentSurrogateIdentityKey as ParentPK , child.PersonParentSurrogateIdentityKeyFK as childFK from #DestinationPersonParentTable par join #DestinationEmailAddressPersonChildTable child
on par.PersonParentSurrogateIdentityKey = child.PersonParentSurrogateIdentityKeyFK



IF OBJECT_ID('tempdb..#DestinationPersonParentTable') IS NOT NULL
begin
        drop table #DestinationPersonParentTable
end


IF OBJECT_ID('tempdb..#DestinationEmailAddressPersonChildTable') IS NOT NULL
begin
        drop table #DestinationEmailAddressPersonChildTable
end