FIX: ???? MERGE ?? ?? ??? ??? ????? ????? ??? ????? ??????? ???? ????? ???? ??? ????? ?? ????? ???? ???????? ????? ?? ???? ????? ????? ?? SQL Server 2008

?????? ????????? ?????? ?????????
???? ???????: 956718 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???: 50003167 (??????? ?????? SQL)
????? ?? ????????? ??? ????? ?????? ????????? ???? ?? ??????? ??? ????? SQL ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
957826??? ????? ?????? ??? ???? ?? ????????? ??? SQL Server 2008 ???? ???? ?? ??????? ??? SQL Server 2008 ? ???? SQL Server 2005 ???? ?? ??????? ??? SQL Server 2005 ?????? ????? ?????? SP2
????? ???? | ?? ????

???????

?? Microsoft SQL Server 2008 ??? ????? ????? ?? ?? ??? ??? ??? ?????? ???????:
  • ??? ????? ???? MERGE.
  • ????? ???? ????? ??????? nonclustered ???? ????.
???? ??? ????????? ??????. ????? ??????? ?????? ?????? ???? ???? Column1 ???? ???? Table1. ??? ??????? Table1 ??? ????? ????? ?? ???? ?????? Table2.

???? ??????? ????? ?????? ?? Table1 ??? ??? ?? ?? ???. ???????? ??? ???? ????? Table2 ?????? ???? ????? ??? ????? dangling Table1.

???? ??? ??????? ?? ??? ????????? ?????? ???????:
  • ???? Column1 ?????? ???? ?? Table1 ??? ????? ?? ????? ???? ???????? Table1.
  • ???? ????? ???? ????? ????? ??? ??? ?????? Column1. ??? ???? ??????? ??? ???????????? ??????? ????:
    • ???? ??? ?? ???? ?? ????????. ??? ???? ??????? ???? ??? ?? ???? ???????? ??? ???????:
      • select <ConstantValues>
      • select <Parameters>
      ?????? ??? ??? ????????? ????????? ?????? ??????.
    • ???? ??? ?? ???? ?? ???????? ??????. ??? ???? ??????? ???? ??? ?? ???? ???????? ??? ???????:
      • select <ColumnName> from <TableName> where <TableName>.<ColumnName> = 1
        ??????<TableName>. <ColumnName> ????? ?????? ?????? ????????? ?? ???? ???? ?????.
      • select top 1 <ColumnName> from <TableName>
    • ??? ??? ???? ??? ?????? ??? ????? ???? ????? ???? ???? ????? ?? ????.
    • ????? ???? ????? ?????? Column1 ???? ????? ??? ????? ?? ???? ???.
  • ?? ??? ????? ?????? ????? ????? ????? ??? ??? ??????? ??????? ?? Table2.
?????? ???? ?????? ??? ??????? ?????? ??? ??? ?????? ???? MERGE ?????? ??????? ???? ??? nonclustered ??????? ??????? ???? ??? ?????? ????? ?????? ???? ???????? ????????.

????

?? ????? ??????? ????? ???? ??????? ????? ?? ??????? ???????? 1. ????? ?? ????????? ??? ????? ?????? ??? ???? ??????? ???????? ??? ?? SQL Server 2008 ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
956717???? ??????? ???????? 1 ?? SQL Server 2008
?????? ??? ????????? ???????? ????? ?? ????? ????? ???? ???? ????????? ??????? ????? ???? ??????? ?????? ???? ?? ??????? ?? SQL Server 2008 ??????? ???????. ???? ???? ???????? ????? ???? ????? ??????? ???? ????? ??? ??? ??????? ??????. ????? ?? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
956909???? SQL Server 2008 ???? ?? ??????? ??? ????? SQL Server 2008

???? ??????

???? ??????? ?????? ??? ???????. ???? ??????? ???? MERGE ?? ????????? ??????? ?? ??? "???????" ??? ???? ??? ?????? ????? ??????? ?????? ???? ??????? ??????? ??? ??? ???????:
  1. ????? ????? ???? MERGE ???? ???? ??? ???? ??? ?? ?????? ?? ?????? ?????? ????? ?????? ????? ?? ???? ??- ????? ?? ?????????.
  2. ?????? ??????? ?????? 8790. ???? ??? ??????? ?????? ?????? ??? ?? ????? ???? ???? ??? ????? ????. ?? ??? ??? ??????? ???? ???????. ??? ?????? ???? ??????? ?????? ?????? ??? ???? ???????? DML. ????? ??? ???? ??????? ??? ?????? ??? ??? ???? ??????? ?????? ???????.
???? ???????? ????? Transact-SQL ?????? ????? ????? ?????? ???????? ????? ????? ?? ??? ??? ??????? ??? ??? ?? ????? ????? ??? ??????? ??????.

??? ???? ??????? ???? ?????? ??? ?????? ??? ???:
use tempdb;

drop table sale, product;
create table product(pno int not null primary key, name char(30), pAlternateKey char(6) not null unique);
create table sale(sno int not null primary key, pAlternateKey char(6) not null references product(pAlternateKey));
insert product values(1, 'Office Chair', 'ochair');
insert sale values(1, 'ochair')

-- No violation of foreign key constraint is detected. However, one should be.
merge into product
using (select 'Office Chair2' as name, 1 as pno, 'oxx' as pAlternateKey) as src
on product.pno = src.pno
when matched then
   update set product.pAlternateKey = src.pAlternateKey, 
              product.name = src.name
when not matched then
   insert values(src.pno, src.name, src.pAlternateKey);
????? ???????? ????? ???? ??? ??? ???:
insert product values(1, 'Office Chair', 'ochair');
insert sale values(1, 'ochair')
-- A foreign key constraint violation is detected, and the update fails.
declare @source table 
   (name nchar(30), pno int, pAlternateKey nchar(30));
insert into @source values('Office Chair2',1,'oxx');

merge into product
using @source as src
on product.pno = src.pno
when matched then
   update set product.pAlternateKey = src.pAlternateKey, 
              product.name = src.name
when not matched then
   insert values(src.pno, src.name, src.pAlternateKey);

?????

???? Microsoft ?? ??? ????? ?? ?????? Microsoft ??????? ?? ??? "????? ???".

??????? ????

?????? ??? ???? ?? ????????? ??? ????? ?? ?? ??????? ? ?????? ??? ??????? ??? ??? ??????? ????? ?????? ???? ??????? ???????? ???? ????? ??? ??????? ?????? ?????? ?? ????? "????? ??????? ?? Microsoft" ??? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
956717???? ??????? ???????? 1 ?? SQL Server 2008

?????

?????? ??? ???? ?? ????????? ??? ??????? ????????? ???? ????? ??? ????? SQL Server 2008 ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
956909???? SQL Server 2008 ???? ?? ??????? ??? ????? SQL Server 2008


????? ?? ????????? ??? "????? ????? ??????" ?? SQL Server ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
935897????? "????? ????? ??????" ?? ???? SQL Server ?????? ????????? ??????? ??????? ?? ??????? ????


????? ?? ????????? ??? ???? ??????? ???????? SQL Server "? ???? ??? ??? ??????? ?????? ?????? ??" ????? ??????? ?? Microsoft:
822499???? ????? ????? ??? ??????? ????? Microsoft SQL Server


????? ?? ????????? ??? ??????? ????? ??????? "? ???? ??? ??? ??????? ?????? ?????? ??" ????? ??????? ?? Microsoft:
824684??? ????????? ???????? ????????? ?? ??? ??????? ????? Microsoft

?????

????? ?? ????????? ??? ??????? nonclustered ?? SQL Server 2008 ?? ?????? ???? ???? ????? Microsoft (MSDN) ?????? ??? ?????:
http://msdn.microsoft.com/en-us/library/ms179325(SQL.100).aspx

???????

???? ???????: 956718 - ????? ??? ??????: 19/?????/1429 - ??????: 4.0
????? ???
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
????? ??????: 
kbmt kbautohotfix kbhotfixserver kbqfe kbpubtypekc kbfix KB956718 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????956718

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com