代码之家 › 专栏 › 技术社区 › imdadhusen

如何使用MS SQL CTE将记录从父级复制到同一表中的子级

common-table-expression cursor recursion sql-server

imdadhusen · 技术社区 · 9 年前

我有这样的要求复制相同的父记录到N子相同的表。 ,我必须将父记录原样复制到只有ParentId和ClientId值更改的子记录。 -子记录(重复1到1000次)不同的子项。

我使用以下代码将父记录复制到每个客户:

DECLARE @id bigint;
--Get list of client which will repeat the rows from Master entries
--This will return three rows Id:1,5,7
DECLARE client_cursor CURSOR FOR SELECT Id FROM Client WHERE ClientName like 'info%' 
OPEN client_cursor
    FETCH NEXT FROM client_cursor INTO @id;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        /*....*/
        /* Other business logic performed here */
        /*....*/
        INSERT INTO Resources (ParentId,ClientId,Location,Title,[Status],Alert)
        --This will return two rows Id:1,2
        SELECT Id,@id,Location,Title,[Status],Alert FROM Resources WHERE ParentId IS NULL AND ClientId IS NULL  

        FETCH NEXT FROM client_cursor INTO @id
    END 
CLOSE client_cursor;
DEALLOCATE client_cursor;

以下是使用上述代码段将批量父记录添加到子记录之前和之后的屏幕截图。

-- Table Creation
CREATE TABLE [dbo].[Client](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ClientName] [varchar](50) NULL,
 CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED 
(
    [Id] 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

CREATE TABLE [dbo].[Resources](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ParentId] [bigint] NULL,
    [ClientId] [bigint] NULL,
    [Location] [varchar](50) NULL,
    [Title] [varchar](50) NULL,
    [Status] [varchar](50) NULL,
    [Alert] [varchar](50) NULL,
 CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED 
(
    [Id] 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

-- Default Data Insertion
SET IDENTITY_INSERT [dbo].[Client] ON 
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (1, N'Infosstretch')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (2, N'Microsoft')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (3, N'Sun System')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (4, N'IBM')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (5, N'Infosys')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (6, N'TCS')
INSERT [dbo].[Client] ([Id], [ClientName]) VALUES (7, N'Infomatica')
SET IDENTITY_INSERT [dbo].[Client] OFF

SET IDENTITY_INSERT [dbo].[Resources] ON 
INSERT [dbo].[Resources] ([Id], [ParentId], [ClientId], [Location], [Title], [Status], [Alert]) VALUES (1, NULL, NULL, N'India', N'Master A', N'New', N'Issue with location')
INSERT [dbo].[Resources] ([Id], [ParentId], [ClientId], [Location], [Title], [Status], [Alert]) VALUES (2, NULL, NULL, N'Australia', N'Master B', N'Updated', N'No major issue')
SET IDENTITY_INSERT [dbo].[Resources] OFF

1 回复 | 直到 9 年前

Jason W 9 年前

为了简洁起见,这里可能删除了您的业务规则中的一些原因,但为什么不这样做呢?

INSERT #Resources
SELECT
    R.Id AS ParentId,
    C.Id AS ClientId,
    R.Location,
    R.Title,
    R.Status,
    R.Alert
FROM #Resources R
    CROSS JOIN #Client C
WHERE R.ParentId IS NULL AND R.ClientId IS NULL -- Root level
    AND NOT EXISTS (SELECT * FROM #Resources WHERE ParentId = R.Id) -- Not already populated
    -- AND /* Other business logic performed here */