January 23, 2017 at 1:54 pm
Hello everyone
I need to check for the presence of a column in a temporary table and add them if they don’t exist already.
The problem seems to lie in the fact that the temporary table (#answer) can exist multiple times in tempdb. The attempts below are not working for me in circumstances where there is more than one instance of #Answer%.
If not exists (Select * from tempdb.information_schema.columns
where table_name like'#Answer%' andcolumn_name = 'OriginalGuarantorKey')
-- create the column
If not exists (select * from tempdb.sys.tables t inner join tempdb.sys.columns c
on t.object_id = c.object_id
where t.Name like '#Answer%'
AND c.Name = 'clientTypeKey')
-- create the column
I’ve been looking on-line for some time now. If anyone can point me in the right directionI would be very grateful
January 23, 2017 at 2:00 pm
Why are you adding columns to a temporary table instead of just including them when you create the table?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2017 at 2:03 pm
drew.allen - Monday, January 23, 2017 2:00 PMWhy are you adding columns to a temporary table instead of just including them when you create the table?Drew
Thank you for responding. I don't get to choose. Multiple procs call the same table name and construct it differently depending on needs.
January 23, 2017 at 2:45 pm
jshahan - Monday, January 23, 2017 2:03 PMdrew.allen - Monday, January 23, 2017 2:00 PMWhy are you adding columns to a temporary table instead of just including them when you create the table?Drew
Thank you for responding. I don't get to choose. Multiple procs call the same table name and construct it differently depending on needs.
But you can't even access temp tables that were created in another process, let alone alter them. So, again, I ask why you don't just include them when creating the table?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 23, 2017 at 3:39 pm
jshahan - Monday, January 23, 2017 2:03 PMdrew.allen - Monday, January 23, 2017 2:00 PMWhy are you adding columns to a temporary table instead of just including them when you create the table?Drew
Thank you for responding. I don't get to choose. Multiple procs call the same table name and construct it differently depending on needs.
Then the original code would know nothing about the new column and couldn't use it anyway. Besides, as already noted, if you didn't create the temp table, you can't accurately reference it, yet alone modify it.
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".
January 23, 2017 at 11:04 pm
jshahan - Monday, January 23, 2017 1:54 PMHello everyoneI need to check for the presence of a column in a temporary table and add them if they don’t exist already.
The problem seems to lie in the fact that the temporary table (#answer) can exist multiple times in tempdb. The attempts below are not working for me in circumstances where there is more than one instance of #Answer%.
If not exists (Select * from tempdb.information_schema.columns
where table_name like'#Answer%' andcolumn_name = 'OriginalGuarantorKey')
-- create the column
If not exists (select * from tempdb.sys.tables t inner join tempdb.sys.columns c
on t.object_id = c.object_id
where t.Name like '#Answer%'
AND c.Name = 'clientTypeKey')
-- create the column
I’ve been looking on-line for some time now. If anyone can point me in the right directionI would be very grateful
This will check only that table which is within your scope:If not exists (Select * from tempdb.sys.columns
where OBJECT_ID = OBJECT_ID('tempdb..#Answer') AND name = 'OriginalGuarantorKey')
-- create the column
_____________
Code for TallyGenerator
January 25, 2017 at 3:53 pm
Thanks to everyone for the responses and input. The problem was purely my bad. There were several nested procedures involved and code that I thought was being executed was not. Hence I sent myself on a goose chase and asked for help before I should have.
My apologies.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply