September 15, 2010 at 11:07 am
Rt not i am performing Alter statements for a tabe one at a time as shown below, I have to add 4 columns to the same table only if they donot exist and if they do i show a message as below, is there a way to perform 4 alters within the same block ?? Thanks for the help
IF EXISTS(SELECT * FROM sys.columns WHERE [name] = 'A' AND OBJECT_NAME([object_id]) = 'TableName')
Begin
Print 'The Column A already exists'
Select * from TableName
END
ELSE
Begin
ALTER TABLE TableName ADD A DATETIME NULL
Print 'Added the column A to the TableName'
Select * from TableName
End
September 15, 2010 at 11:16 am
sure, you can do it two ways;
the BEGIN...END block can contain more than one command, and the ALTER TABLe command can take a comma delimited lit of columsn tadd:
IF EXISTS(SELECT * FROM sys.columns WHERE [name] = 'A' AND OBJECT_NAME([object_id]) = 'TableName')
BEGIN
Print 'The Column A already exists'
Select * from TableName
END
ELSE
BEGIN
ALTER TABLE TableName ADD A DATETIME NULL
ALTER TABLE TableName ADD B VARCHAR(10) NULL
ALTER TABLE TableName ADD C int,D money,E VARCHAR(30)
Print 'Added the column A to the TableName'
END
Lowell
September 15, 2010 at 11:49 am
Thanks for the script but my problem is that i have to check if column A exists than add BCD if B exists add ACd and so on, and there is no way we can accomplish that right,
1) so add ABCD to a table only if ABCD donot exist
2) Add any of the column if they donot exist
3) if all exist show " aLL already exist"
i will work on something but if you already know please let me know
thanks
September 15, 2010 at 12:06 pm
Sounds like you are asking if you can four logical checks within the same if statement and then do some logic that changes for each condition. You are correct in that you can't do this in one if block.
You will need to check for each column since the code will be unique to that column's existence. What you could do i create 4 bit variables to track which (if any) of the columns needed to be added.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2010 at 12:26 pm
how can i accomplish that can we declare without creating a procedure in sQL. I hae somefamiliarilt with PL/SQl procedures but not SQL server which is partly why i am struggling.
If u can direct me towards something that would be awesome
thanks Again
September 15, 2010 at 12:35 pm
Given that this is adding columns and such i will assume that this a one time use need. Just create your script in SSMS and then save it so you can execute it on your production server.
declare @Col_A_Added bit = 0
declare @Col_B_Added bit = 0
declare @Col_C_Added bit = 0
declare @Col_D_Added bit = 0
if col_A...
begin
alter statement here
print 'Col A added'
set @Col_A_Added = 1
end
if col_B...
begin
alter statement here
print 'Col B added'
set @Col_B_Added = 1
end
if col_C...
...
if col_D...
...
then you could add something like
if @Col_A = 0 and @Col_B = 0 and @Col_C = 0 and @Col_D = 0 begin
print 'All columns existed or no columns were added'
end
Sorry for the pseudocode but hopefully this will point you in the right direction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply