June 18, 2014 at 3:19 am
Hi
I have two tables, Hosea_tblDef_Cloning_Table and Hosea_tblDef_Cloning_Ref_Table.
Hosea_tblDef_Cloning_Ref_Table holds all the table to be cloned and their ACTIVE and PROCESSED status.
CREATE TABLE [dbo].[Hosea_tblDef_Cloning_Ref_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLONE_ID] [int] NOT NULL,
[DESCRIPTION] [varchar](50) NOT NULL,
[ACTIVE] [char](1) NULL,
[PROCESSED] [char](1) NULL,
CONSTRAINT [un_clone_id] UNIQUE NONCLUSTERED
(
[CLONE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Hosea_tblDef_Cloning_Ref_Table]([CLONE_ID],[DESCRIPTION],[ACTIVE],[PROCESSED])
VALUES(1, 'Hosea_tblDef_RETURNS', Y, N), (2, 'Hosea_tblDATA_Product_Reports', Y, N)
Hosea_tblDef_Cloning_Ref_Table has all the table names, columns and column values to be updated.
CREATE TABLE [dbo].[Hosea_tblDef_Cloning_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLONE_ID] [int] NOT NULL,
[TABLE_NAME] [varchar](150) NULL,
[COLUMN_NAME] [varchar](150) NULL,
[OLD_VALUE] [varchar](150) NULL,
[NEW_VALUE] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Hosea_tblDef_Cloning_Table]([CLONE_ID],[TABLE_NAME],[COLUMN_NAME],[OLD_VALUE],[NEW_VALUE])
VALUES(1, 'Hosea_tblDef_RETURNS', 'Product_Id', 'AGP1', 'PIC1'), (1, 'Hosea_tblDef_RETURNS', 'Product_Id', 'E016', 'D061'),
(2, 'Hosea_tblDATA_Product_Reports', 'Product_Id', 'AGT4', 'PIC2'),(2, 'Hosea_tblDATA_Product_Reports', 'Product_Id', 'E042', 'E052')
I have this code below I'm trying to clone a row from CloneID 1 if the table is ACTIVE and UNPROCESSED
DECLARE @CloneID int,
@Active char(1),
@Processed char(1)
SET @CloneID = 1
SELECT
'INSERT INTO ' + t2.table_name + CHAR(10) +
' (' + STUFF([target].ColumnList,1,2,'') + ')' + CHAR(10) +
'SELECT ' + STUFF([source].ColumnList,1,2,'') + CHAR(10) +
'FROM ' + t2.table_name + CHAR(10) +
'WHERE ' + STUFF([filter].ColumnList,1,4,'')
--FROM(SELECT TOP 1 table_name FROM Hosea_tblDef_Cloning_Table) t2
FROM (SELECT TOP 1 ct.table_name
FROM Hosea_tblDef_Cloning_Table ct
JOIN Hosea_tblDef_Cloning_Ref_Table crt
ON ct.Clone_ID= @CloneID
AND crt.ACTIVE = 'Y' AND crt.PROCESSED = 'N') t2
INNER JOIN information_schema.tables t
ON t2.table_name = t.table_name
CROSS APPLY (
SELECT
[text()] = ', [' + c.COLUMN_NAME + ']'
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [target] (ColumnList)
CROSS APPLY (
SELECT
[text()] = ', [' + c.COLUMN_NAME + ']' + CASE
WHEN ct.NEW_VALUE IS NULL THEN ''
ELSE ' = ' + QUOTENAME(ct.NEW_VALUE,'''') END
FROM information_schema.columns c
LEFT JOIN Hosea_tblDef_Cloning_Table ct
ON ct.COLUMN_NAME = c.COLUMN_NAME
--left JOIN Hosea_tblDef_Cloning_Ref_Table crt
--ON ct.Clone_ID= @CloneID
--AND (crt.ACTIVE = 'Y' AND crt.PROCESSED = 'N')
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
--AND (crt.ACTIVE = 'Y' AND crt.PROCESSED = 'N')
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
CROSS APPLY (
SELECT DISTINCT
[text()] = ' AND [' + c.COLUMN_NAME + ']' + CASE
WHEN ct.NEW_VALUE IS NULL THEN '' ELSE ' = ' + QUOTENAME(ct.OLD_VALUE,'''') END
FROM information_schema.columns c
INNER JOIN Hosea_tblDef_Cloning_Table ct
ON ct.COLUMN_NAME = c.COLUMN_NAME
INNER JOIN Hosea_tblDef_Cloning_Ref_Table crt
ON ct.Clone_ID= @CloneID
AND crt.ACTIVE = 'Y' AND crt.PROCESSED = 'N'
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [filter] (ColumnList)
Now I get this error from [dbo].[Hosea_tblDef_Cloning_Table], My results return both CloneID 1 and 2. please help
June 18, 2014 at 3:33 am
Resolved.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply