.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

It is possible to alter multiple columns within a single alter table statement?

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.


Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

View Complete Post

More Related Resource Links

how to execute ALTER table for multiple columns


I have an SQL table called tbl, im trying to add the columns A, B and C to it.

When i execute the command :

 addcolumns = "ALTER TABLE SqlCreatedTbl ADD A char(50) ;";


 cmd = new SqlCommand(addcolumns, conn);

conn.Open(); cmd.ExecuteNonQuery();

The column is added perfectly and i can see it in the table !

However, when i try to add multiple columns, it does NOT work, it gives me an error..

the command im writting for adding multiple columns is the following:

addcolumns = "ALTER TABLE SqlCreatedTbl ADD ( A char(50), B char(50), C char(50) );";

the debugger highlights the line : cmd.ExecuteNonQuery(); and throws the following exception: Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '('.


SMO classes to generate alter table script to add columns

How to generate ALTER TABLE script to add columns using SMO and c#.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.

SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp


Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

Ambiguous MSDN help on ALTER TABLE ... SWITCH statement

This page on MSDN contains a paragraph that I believe is ridiculously ambiguous:
"Source and target tables must share the same filegroup . The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup , and their large-value columns must be stored in the same filegroup . Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup . However, the filegroup can be different from that of the corresponding tables or other corresponding indexes."

What other "corresponding" tables or indexes can there be involved in the ALTER TABLE ... SWITCH statement? Why does this paragraph mention tables and indexes sharing the same filegroup when partitions of each of them can reside on different filegroups?

Can someone please enlighten me on the meaning of this paragraph?

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

Right click script alter table disabled

Does anyone know how to enable the "Script Table As" -> "Alter To" option in SQL Server 2008 SSMS? John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

is Alter table a fully logged operation

Hello, A friend of mine has a very large table which has a column of type int. Since this table is growing very fast he wanted to alter the table to change the data type from int to bigint. when he did that, he found that he ran out of disk space on the t-log volume. (600 GB of free space). why did this happen? shouldn't the type change be a meta data change operation? why was the t-log so heavily used on change of type?


the database result is as follows DATE     code   class1 class2  class3 1/8/10     M1     10       20     30     3/8/10     V2      20       30     10      1/8/10      H1      1           2      3         7/8/10     ! ! 8/8/10     !M2    5      5      5       3/8/10     B2      4      4     4       1/8/10     V1      3      7     3       THE OUTPUT SHOULD BE AS FOLLOWS:- DATE           M1                          M2           

Alter Table add column

Hi all, ALTER TABLE dbo.tbl_allocation ADD calc_method [decimal](38, 6) ADD allocation_percentage [decimal] (38, 6) when i run this i get the following message: incorrect syntax near decmial. please help

Upgrading timestamp column to rowversion (alter table)

Hi, We are upgrading 2005 to 2008 and observed that time stamp data type is deprecated, and should be using rowversion instead. So I started doing alter table for a table with timestamp to rowversion and the alter table failed with the error "cannot alter column 'rv' to be data type timestamp. Can some one please let me know what I am missing here? thanks, Sivakumar

To Search a word in all the columns of a Single Table

Dear Friends, How can I search a word in all the columns of a table?Please advise.

Alter table error


continue to get this error and cannot figure out why

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '('

This is the code;  - error refers to the '(' at end of Alter Table line





/****** Object: Table [dbo].[MarkData] Script Date: 09/22/2010 10:17:06 ******/





Alter table nockeck constr with VAR


Hi all,


When i run this query:

declare @FK_Name varchar(500)
declare @UT_Name varchar(500)
set @UT_Name = Table_Name
set @FK_Name = FK_Name
alter table @UT_Name nocheck constraint @FK_Name

I get this error msg:

Incorrect syntax near '@UT_Name'.

I guess The problem is in the last dtring of the query.

What should i change in order it works?


Thank you


Insert multiple rows with a single INSERT statement


With SQL Server 2005 Express coming out, I have switched from MySql.  I'm having trouble with a very simple INSERT statement that has previously worked in both MySql and Oracle.  The statement is as follows:

INSERT INTO pantscolor_t (procode,color,pic) VALUES
 ('74251', 'Black', '511black.jpg'),
 ('74251', 'Charcoal', '511charcoal.jpg'),
 ('74251', 'Khaki', '511khaki.jpg'),
 ('74251', 'Navy', '511navy.jpg'),
 ('74251', 'OD Green', '511odgreen.jpg');

However, when I attempt to execute this statement with Management Studio Express I get the following error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

Any ideas?

Unable to alter table that is beeing replicated


We are using SQL Server 2008 and transaction replication. I have set up the publication so that it is replicating schema changes but today i discovered that I was unable to add a column to one table that also is an article in the publication. The error I get is this:

Altering [dbo].[Group]
Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Have anyone any clue of what this can be?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend