在 SQL 服务器中将记录从现有版本复制到新版本

Question

我有一个表，其中包含一些版本号为 1 的行，这些行具有它们的 各自的父 ID 如果父 ID 为空，则它是父级其他明智的 具有父 ID 的孩子，我想编写查询来复制所有行 从版本号 1 到 2，然后插入具有新相应父级 ID 的新行。 下面是我的表格和一些数据脚本。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Menu](
[MenuId] [bigint] IDENTITY(1,1) NOT NULL,
[MenuName] [nvarchar](50) NULL,
[ParentId] [bigint] NULL,
[VersionNumber] [bigint] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
(
[MenuId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Menu] ON 
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber])     VALUES (1, N'Customer', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (2, N'Home', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (3, N'About', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (4, N'Add Customer', 1, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (5, N'Administration', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (6, N'Edit Customer', 1, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (7, N'Tenant', 5, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (8, N'Manage Contact', 5, 1)
 INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (9, N'Users', 5, 1)
 INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (10, N'Customer List', 1, 1)
 INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (11, N'Contact', NULL, 1)`enter code here`
   SET IDENTITY_INSERT [dbo].[Menu] OFF

提前谢谢。

Answer 1

我有一种感觉，这个问题比我正在阅读的要多，但是如果您只想复制数据并将版本号从 1 更改为 2，那么这将起作用。

INSERT  INTO dbo.Menu
        ( MenuName,
          ParentId,
          VersionNumber )
        SELECT  m.MenuName,
                m.ParentId,
                2
        FROM    Menu AS m
        WHERE   m.VersionNumber = 1

Answer 2

这个有点棘手。

但下面是一个可行的解决方案。

带有输出值和原始值的"合并"（将两种类型的值放入 #Holder 表中）是诀窍。

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = N'dbo' and TABLE_NAME = N'Menu' and TABLE_TYPE = N'BASE TABLE' ) 
BEGIN 
DROP TABLE [dbo].[Menu] 
END 
GO

CREATE TABLE [dbo].[Menu](
[MenuId] [bigint] IDENTITY(1,1) NOT NULL,
[MenuName] [nvarchar](50) NULL,
[ParentId] [bigint] NULL,
[VersionNumber] [bigint] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
(
[MenuId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Menu] ON 
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber])     VALUES (1, N'Customer', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (2, N'Home', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (3, N'About', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (4, N'Add Customer', 1, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (5, N'Administration', NULL, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (6, N'Edit Customer', 1, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (7, N'Tenant', 5, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (8, N'Manage Contact', 5, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (9, N'Users', 5, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (10, N'Customer List', 1, 1)
INSERT [dbo].[Menu] ([MenuId], [MenuName], [ParentId], [VersionNumber]) VALUES (11, N'Contact', NULL, 1)

   SET IDENTITY_INSERT [dbo].[Menu] OFF
   select * from [dbo].[Menu] 

    IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
    begin
            drop table #Holder
    end
    /* The "mapping values" holder/tracking table */
    CREATE TABLE #Holder
    ( 
    --SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
    OriginalMenuId [bigint]  NULL,
    OriginalParentId [bigint] NULL,
    NewMenuSurrogateKey int null ,
    NewParentIdSurrogateKey int null
    )

/* This is the key trick.  You need to do the INSERT and keep track of old and new surrogate keys.  The MERGE trick is the only way to get values into the #holder...combining "output" values and original values */
MERGE INTO [dbo].[Menu] pc
USING [dbo].[Menu] AS sourceTable 
ON  1 = 0 
WHEN NOT MATCHED THEN
  INSERT([MenuName], [VersionNumber])
   Values([MenuName] , [VersionNumber] + 1 ) /* Here is your "plus one" for the Version */
Output sourceTable.MenuId , sourceTable.ParentId , inserted.MenuID INTO 
  #Holder (OriginalMenuId , [OriginalParentId] , NewMenuSurrogateKey );
Select 'Before the NewParentIdSurrogateKey is Updated Values' as Lab1 ,  * from #Holder

Update holderAsParent Set NewParentIdSurrogateKey = holderAsChild.NewMenuSurrogateKey
/* You can comment out the previous update and uncomment the next select statement to see what the update is trying to do */
/* Select 'Here1' as Lab1 ,  holderAsChild.NewMenuSurrogateKey, '----' as Sep1, holderAsChild.* , '----' as Sep2 , holderAsParent.* */
from
#Holder holderAsChild 
join #Holder holderAsParent on holderAsChild.OriginalMenuId = holderAsParent.OriginalParentId

Select 'ShouldHaveAllTheMappingValuesInTheHolderNow' as Lab1 ,  * from #Holder

Update dbo.Menu Set [ParentId] = holder.NewParentIdSurrogateKey
from 
dbo.Menu men join #Holder holder on men.MenuId = holder.NewMenuSurrogateKey
where holder.NewParentIdSurrogateKey iS NOT NULL
Select 'Final Results' as Lab1 ,  * from dbo.Menu

IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
        drop table #Holder
end
/* Sanity Check */

Select COALESCE(parent.MenuName + ' -- ', '') + child.MenuName as ParentThenChildMenuName, child.VersionNumber
from
    [dbo].[Menu] child
    left join
    [dbo].[Menu] parent
    on child.ParentId = parent.MenuId
where child.VersionNumber = 1
order by parent.MenuName , child.MenuName

Select COALESCE(parent.MenuName + ' -- ', '') + child.MenuName as ParentThenChildMenuName, child.VersionNumber
from
    [dbo].[Menu] child
    left join
    [dbo].[Menu] parent
    on child.ParentId = parent.MenuId
where child.VersionNumber = 2
order by parent.MenuName , child.MenuName

以下是参考。 这是导致在"from_table_name"中使用MERGE以应对这种特定情况的小提示。

http://msdn.microsoft.com/en-us/library/ms177564.aspx"from_table_name"一个列前缀，它指定包含在 DELETE、UPDATE 或 MERGE 语句的 FROM 子句中的表，该语句用于指定要更新或删除的行。