Drop Identity column from temp table

Question

Post reply

Drop Identity column from temp table

Viewing 15 posts - 31 through 45 (of 52 total)

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

hoseam SSCertifiable Points: 5533 More actions · Answer 1

hoseam

SSCertifiable

Points: 5533

May 12, 2014 at 12:25 am

#1712535

Any luck Chris??

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

hoseam (5/12/2014)
Any luck Chris??

No. Running your script generates the following error.

Msg 110, Level 15, State 1, Line 22

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Fix the script and we'll take it from there.

^{“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 3

hoseam

SSCertifiable

Points: 5533

May 12, 2014 at 2:06 am

#1712546

Which script, the ones I sent first or last..

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

CREATE TABLE [dbo].[Hosea_tblDef_RETURNS](

[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,

[REPORT_ID] [varchar](50) NOT NULL,

[COMPANY] [varchar](150) NULL,

[PRODUCT_TYPE] [varchar](150) NULL,

[PRODUCT_ID] [varchar](150) NULL,

[PRODUCT_DESC] [varchar](150) NULL,

[FUND_ID] [varchar](150) NULL,

[FUND_INCEPTION_DATE] [varchar](6) NULL,

[RETURNS_TYPE_KEY] [varchar](150) NULL,

[RETURNS_TYPE_FILTER] [varchar](150) NULL,

[RETURNS_MONTH_FILTER] [varchar](150) NULL,

CONSTRAINT [PK_tblDef_RETURNS] PRIMARY KEY CLUSTERED

(

[RETURNS_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 [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])

VALUES (1, 'ABC', 'MYCOM', NULL, 'AGP1', 'Scheme', 'E016', '200704', 'SS', 'RETURNS_TYPE_CD = 16', NULL)

--------------------------------------------------------------------------

--DROP TABLE Hosea_tblDef_Cloning_Table

CREATE TABLE 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

)

INSERT INTO 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', 'Fund_Id', 'E016', 'D061')

^{“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

CREATE TABLE [dbo].[Hosea_tblDef_RETURNSSSS](
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL,
[FUND_INCEPTION_DATE] [varchar](6) NULL,
[RETURNS_TYPE_KEY] [varchar](150) NULL,
[RETURNS_TYPE_FILTER] [varchar](150) NULL,
[RETURNS_MONTH_FILTER] [varchar](150) NULL,
CONSTRAINT [PK_tblDef_RETURNSSSS] PRIMARY KEY CLUSTERED
(
[RETURNS_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 [Hosea_tblDef_RETURNSSSS]
([REPORT_ID]
,[COMPANY]
,[PRODUCT_TYPE]
,[PRODUCT_ID]
,[PRODUCT_DESC]
,[FUND_ID]
,[FUND_INCEPTION_DATE]
,[RETURNS_TYPE_KEY]
,[RETURNS_TYPE_FILTER]
,[RETURNS_MONTH_FILTER])
VALUES (1, 'ABC', 'MYCOM', NULL, 'AGP1', 'Scheme', 'E016', '200704', 'SS', NULL)

hoseam SSCertifiable Points: 5533 More actions · Answer 6

Can I also mention my Code has changed to this:

declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name IN (select [COLUMN_NAME] from [Hosea_tblDef_Cloning_Table]) THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
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('')
) [source] (ColumnList)
WHERE t.table_name = @TableName

Which is wrong because it gives me this end results:

[dbo].[Hosea_tblDef_RETURNS] ([REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID] = 'D061', [PRODUCT_DESC], [FUND_ID] = 'D061', [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER])

And what I need is to put 'PIC1' next to [PRODUCT_ID] in my end results.. I know my error it's in CASE statement in the query, I'm having a problem to correcting it.

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

hoseam (5/12/2014)
Can I also mention my Code has changed to this:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name IN (select [COLUMN_NAME] from [Hosea_tblDef_Cloning_Table]) THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
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('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
Which is wrong because it gives me this end results:
[dbo].[Hosea_tblDef_RETURNS] ([REPORT_ID], [COMPANY], [PRODUCT_TYPE], [PRODUCT_ID] = 'D061', [PRODUCT_DESC], [FUND_ID] = 'D061', [FUND_INCEPTION_DATE], [RETURNS_TYPE_KEY], [RETURNS_TYPE_FILTER], [RETURNS_MONTH_FILTER])
And what I need is to put 'PIC1' next to [PRODUCT_ID] in my end results.. I know my error it's in CASE statement in the query, I'm having a problem to correcting it.

Is this the statement you are attempting to generate;

INSERT INTO [dbo].[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 [dbo].[Hosea_tblDef_RETURNS]

WHERE [FUND_ID] = 'E016'

AND [PRODUCT_ID] = 'AGP1'

^{“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 8

hoseam

SSCertifiable

Points: 5533

May 12, 2014 at 2:32 am

#1712554

Yes that's what I'm tempting.

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

And you will only ever change FundID, ProductID or both?

^{“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 10

Product_ID and FundID are from Hosea_tblDef_Cloning_Table, from the column COLUMN_NAME, so any value in that column will always be there in the WHERE CLAUSE. on this case is (Product_ID and FundID)

hoseam SSCertifiable Points: 5533 More actions · Answer 11

Product_ID and FundID are from Hosea_tblDef_Cloning_Table, from the column COLUMN_NAME, so any value in that column will always be there in the WHERE CLAUSE. on this case is (Product_ID and FundID)

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

hoseam (5/12/2014)
Product_ID and FundID are from Hosea_tblDef_Cloning_Table, from the column COLUMN_NAME, so any value in that column will always be there in the WHERE CLAUSE. on this case is (Product_ID and FundID)

So any combination of columns will be valid? IIRC it was originally Product_ID and FundID.

^{“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 13

Yes, any columns in the Hosea_tblDef_Cloning_Table, under the column [COLUMN_NAME], will be the combination.. In our select list selecting new values and where clause comparing with old values..

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

hoseam (5/12/2014)
Yes, any columns in the Hosea_tblDef_Cloning_Table, under the column [COLUMN_NAME], will be the combination.. In our select list selecting new values and where clause comparing with old values..

Just to be absolutely sure:

hoseam (4/23/2014)
The scenario is this..
I have a product AGP with Fund E01, and other attributes
then later I want to creat another product with the same attributes as AGP product, except this new product will have to be new Product name(PCC) and Fund name(E33)
So I thought I can get that row, AGP row, put it in the temp, and update only Product and Fund, then put back the new Product back into perm table

^{“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 15

That Scenario True: old product_Id would be AGP and old Fund_Id E01..

then later I want to creat another product with the same attributes as AGP product, except this new product will have to be new Product name(PCC) and Fund name(E33)

The new Product_D will be 'PCC' and new Fund_Id E33

So now these values will be inserted in Hosea_tblDef_Cloning_Table

INSERT INTO Hosea_tblDef_Cloning_Table ([CLONE_ID],[TABLE_NAME],[COLUMN_NAME],[OLD_VALUE],[NEW_VALUE])
VALUES (1, 'Hosea_tblDef_RETURNS', 'Product_Id', 'AGP', 'PCC'),
(1, 'Hosea_tblDef_RETURNS', 'Fund_Id', 'E01', 'E33')

So what's happening here, we took Product(AGP) from Hosea_tblDef_RETURNS that has Fund(E01) and clone it to new Product(PCC) and Fund(E33). in other cases it might happen that Product(AGP) doesn't have a Fund.. so the column Fund in Hosea_tblDef_RETURNS and Hosea_tblDef_Cloning_Table would be NULL

In other cases is can be totally different table, [Hosea_tblDATA_Reports] for instance, that doesn't have Product or Fund, but Header_ID and/Or Group_ID, or even more than two columns...

It doesn't matter, what matter is that those Identifying table and columns will be in the table Hosea_tblDef_Cloning_Table, [TABLE_NAME] telling us which table we are going to clone, [COLUMN_NAME] telling us which are Identifying columns, (which are also part on our WHERE CLAUSE) and the obvious [OLD_VALUE] and [NEW_VALUE]