[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

want to add a new column between two columns in a table

Posted on 2009-12-29
5
Medium Priority
?
220 Views
Last Modified: 2012-05-08
do i have to write all the column names to do this command
0
Comment
Question by:rgb192
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 26138509
there is no other options other than droping and recreating the table..
0
 
LVL 15

Assisted Solution

by:Faiga Diegel
Faiga Diegel earned 500 total points
ID: 26139075
Or use the Management Studio --> open the table designer and move the newly created column. It will always be int he end of the column list when you add new column to an existing table :(  
0
 
LVL 2

Assisted Solution

by:mabbj747
mabbj747 earned 500 total points
ID: 26139310
Your can follow these steps.
1) Create temporary table with the column in the desired sequence
2) Insert the data in the temporary table from original table
3) Drop the original table
4) Rename the temporary table

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_yourTable
      (
      PK_Col1 int NOT NULL IDENTITY (1, 1),
      col2 nchar(10) NULL,
      cold3 nvarchar(255) NOT NULL
      )  
GO
SET IDENTITY_INSERT dbo.Tmp_yourTable ON
GO
IF EXISTS(SELECT * FROM dbo.yourTable)
       EXEC('INSERT INTO dbo.Tmp_yourTable (PK_Col1, cold3)
            SELECT PK_col1, cold3 FROM dbo.yourTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_yourTable OFF
GO
DROP TABLE dbo.yourTable
GO
EXECUTE sp_rename N'dbo.Tmp_yourTable', N'yourTable', 'OBJECT'
GO
COMMIT
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 26139384
>Or use the Management Studio --> open the table designer and move the newly created column. It will always be int he end of the column list when you add new column to an existing table :(  

this will do the same, which will lock the table  and i dont think you really need to do this as the column order rarely matters. also sql server 2008 management studio wont support this
0
 

Author Closing Comment

by:rgb192
ID: 31670796
thanks for advice
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question