Drop Identity column from temp table

Question

Post reply

Drop Identity column from temp table

Viewing 8 posts - 46 through 52 (of 52 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 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 ORDER BY table_name) 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

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('')

) [source] (ColumnList)

CROSS APPLY (

SELECT

[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

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)

Result

------

INSERT INTO Hosea_tblDef_RETURNS

([REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID], [PRODUCT_DESC], [FUND_ID], [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER])

SELECT [REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID] = 'PIC1', [PRODUCT_DESC], [FUND_ID] = 'D061', [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER]

FROM Hosea_tblDef_RETURNS

WHERE [PRODUCT_ID] = 'AGP1' AND [FUND_ID] = 'E016'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hoseam SSCertifiable Points: 5533 More actions · Answer 2

hoseam

SSCertifiable

Points: 5533

May 12, 2014 at 5:07 am

#1712607

Thank you. So much.

hoseam SSCertifiable Points: 5533 More actions · Answer 3

I edited the code to the following.

Firstly I have this table:

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]

GO

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')

So, as I want to Clone the data on table Hosea_tblDef_Cloning, this table,(Hosea_tblDef_Cloning_Ref_Table), plays a role. We can only clone a row if the table is active and unprocessed. After processing it we the PROCESS status to 'Y'.

I have this changes to Code below:

declare @CloneID int,
@Active char(1),
@Processed char(1)
SET @CloneID = 2
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 DISTINCT 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)

And this is the results I get:

INSERT INTO Hosea_tblDATA_Product_Reports ([Product_Id], [Product_Desc], [Fund_Id], [Fund_Desc], [Header_Desc], [Group_Id], [Report_Id], [Product_Colour])
SELECT [Product_Id] = 'PIC1', [Product_Id] = 'PIC2', [Product_Desc], [Fund_Id] = 'D061', [Fund_Id] = 'E052', [Fund_Desc], [Header_Desc], [Group_Id], [Report_Id], [Product_Colour]
FROM Hosea_tblDATA_Product_Reports WHERE [Fund_Id] = 'E042' AND [Product_Id] = 'AGT4'

My code still pick both Product_Id and Fund_Id. I expect it to pick one, since I specified @CloneID.

Please help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Instead of attempting to integrate this control/logging table into an already complex script, try this: write a query which joins the cloning table and the control table, outputting only the rows you want to process into a #temp table.

Then change the main query so that it references the #temp table instead of the cloning table. The end result will be much simpler.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hoseam SSCertifiable Points: 5533 More actions · Answer 5

Hi Chris

This is what I tried and I'm still getting the same error

DECLARE @CloneID int,
@Active char(1),
@Processed char(1)
SET @CloneID = 1
SELECT TOP 1 ct.table_name,ct.Clone_ID, crt.ACTIVE, crt.PROCESSED
INTO #Temp1
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'
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 ct.table_name
FROM Hosea_tblDef_Cloning_Table ct
JOIN #Temp1 tmp
ON ct.Clone_ID= @CloneID
AND tmp.ACTIVE = 'Y' AND tmp.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 #Temp1 tmp
ON ct.Clone_ID= @CloneID
AND tmp.ACTIVE = 'Y' AND tmp.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('')
) [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)
drop table #Temp1

hoseam SSCertifiable Points: 5533 More actions · Answer 6

hoseam

SSCertifiable

Points: 5533

June 18, 2014 at 4:44 am

#1722686

I managed to get it.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 7

Cool. How'd you do it?

Also, please don't erase the original post you posted. It's good to see a better description of the problem than only that which is offered in a title. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101122 More actions · Answer 8

If you basically just want to copy/clone one table but without the identity, why not just "cancel" the identity as part of the SELECT ... INTO the new table? Would that be simpler in your situation?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".