February 2, 2010 at 10:50 am
I want to add columns( for eg column x) to a Table (for eg table A), only if is is not there already if it is already there i want to update the field value to the column, can anyone direct me to a statement in sql that would allow me to do this.
thanks
February 2, 2010 at 10:02 pm
You need dynamic SQL to do this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 3, 2010 at 12:03 pm
It's possible the others have mnade your problem more complicated than you intended.
If you were just looking for how to tell if a column exists in a table, you can query the Dynamic Management Views like so:
IF EXISTS(SELECT NULL FROM sys.columns WHERE [name] = 'x' AND OBJECT_NAME([object_id]) = 'A')
ALTER TABLE...
ELSE
UPDATE TABLE...
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 3, 2010 at 3:45 pm
When i used
IF EXISTS(SELECT NULL FROM sys.columns WHERE [name] = 'x' AND OBJECT_NAME([object_id]) = 'b')
ALTER TABLE x ADD x varchar(2000) NULL
ELSE
UPDATE b
SET X = 'some values'
I get an error as Invalid Column name x, am i X in an invalid column , Please suggest what am i doing wrong
February 3, 2010 at 4:35 pm
I'm sorry it looks like I reversed the order of those.
The select statement looks at the table definition for a column named X in the table named A
So if that column exists, then you _don't_ want to alter the table.
Just put the update first.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 3, 2010 at 4:41 pm
IF EXISTS(SELECT NULL FROM sys.columns WHERE [name] = 'x' AND OBJECT_NAME([object_id]) = 'b')
UPDATE b
SET X = 'some values'
ELSE
ALTER TABLE x ADD x varchar(2000) NULL
I wanted to do more updates could i end the statement and start a second update rt after the alter so that something like
*** the statement above*****
GO
Update more values
thanks
February 3, 2010 at 4:52 pm
Sure, you just have to wrap it in a block like this:
IF EXISTS(SELECT NULL FROM sys.columns WHERE [name] = 'x' AND OBJECT_NAME([object_id]) = 'b')
UPDATE b
SET X = 'some values'
ELSE
BEGIN
ALTER TABLE b ADD x varchar(2000) NULL
UPDATE b
SET X = 'some values'
END
Edit: you were referencing table x in the alter statement, but table b in the exist statement, so I changed the second reference.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 3, 2010 at 4:54 pm
hey i think i got it it works when we reverse the order but the update is not performed so we can use a
Begin and an END ?? would that be better ( if it deos find the column int )
also is there a try and a catch
February 3, 2010 at 4:55 pm
Please tell me this is a one off , oh i need to add a new column sort of a thing , and not a programmatical add a new column if needed.
If the later then something is wrong in your design and needs to be addressed urgently before you hit even bigger hurdles.
February 3, 2010 at 5:00 pm
Dave is right, This sort of code is useful for delivering updated code or something, but if you're writing an application that depends on this, I guarantee there's a better way. Also, if you're wanting to extend this to use variables in place of x and b, you're going to have to use dynamic sql like the first two poster mentioned. That would probably be a bad idea.
As far as Try and Catch, (they do exist) take a look here:
http://msdn.microsoft.com/en-us/library/ms189826(SQL.90).aspx
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
February 3, 2010 at 5:02 pm
i think i have found the answer but i am not entirely sure what is being asked by DAve, all i am tryin to do is add a column and update it with new values, but if it already exist just update the values in that column
thanks
i think a try and a catch is the most efficient way to achieve this
February 3, 2010 at 5:05 pm
thanks for all the help wietzera,i think i will figure out a way to do the same procdure with a try
thanks again for the help
February 3, 2010 at 5:06 pm
What im getting at is that IF you are adding a column , for example, for January2010 data and then for each subsequent month you need to add another column for feb , mar , apr etc then your design is wrong.
If on the other hand , you are adding 'Title' (ie Mr , Mrs etc) column to a table and just need to make the script run cleanly not reporting any errors, then fair enough.
February 3, 2010 at 5:13 pm
just trying to add a column ( for eg title to table person and update its values ), sorry for the confusion I think i found the way for try and catch as
BEGIN TRY
IF NOT EXISTS(SELECT NULL FROM sys.columns WHERE [name] = 'x' AND OBJECT_NAME([object_id]) = 'T')
ALTER Table T add x Type nvarchar(2000) NULL
END TRY
BEGIN CATCH
END CATCH
UPDATE T SET x = 'some values'
thanks again
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply