Drop and Create column in the same position (TSQL), as it was before dropping
-
Hello,
I would like to know if it is possible to create a column in a specific position using TSQL (for instance a tables with 7 columns, I would like to drop a column timestamp datatype in 4th position and create a new one binary(8) in the same position).
Thank
-
Only by doing it the way management studio does, by creating a new table, copying the data over and dropping the old column.
Have you tried Alter Table Alter Column? I don't know if it can change a timestamp to another data type. Is worth a try.
Why are you concerned with the position of the columns? Unless you have queries with SELECT * (which is bad practice), the order the columns are positioned in the table are inconsequential.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hello,
I am using a Database not built up by me, and where an application runs on it that I have not built up (some SELECT * ....I do not know), so to be sure I prefer to keep cols in the same position.
I have tried to use alter table, but it Is not possible using it.
Thank
-
-- Quick example of dropping/creating a column in same location.
-- Basically we are going to move the data to a temp table, drop and recreate the existing table
-- and copy the data back.
-- NOTE: If the table to be altered has a bizzilion rows you'll need to consider your tempdb space and
-- possibly altering this to use bulk import/export.
--
-- Build the test table
if exists (select 1
from sysobjects
where id = object_id('test_')
and type = 'U')
drop table test_
go
Create table test_(
col1_ numeric(18,0) identity(1,1) not for replication,
col2_ char(10),
col3_ timestamp,
col4_ char(10),
col5_ char(10),
constraint pk_test_ primary key (col1_) on "primary"
)
go
--Populate the test table
insert into test_ (col2_,col4_,col5_) values ('123','234','345')
insert into test_ (col2_,col4_,col5_) values ('123','234','345')
insert into test_ (col2_,col4_,col5_) values ('123','234','345')
go
--check the values
select * from test_
--prepare to alter the table by creating a temp table to hold the existing data'
if exists (select 1
from sysobjects
where id = object_id('tmp_test_')
and type = 'U')
drop table tmp_test_
go
Create table tmp_test_(
col1_ numeric(18,0) identity(1,1) not for replication,
col2_ char(10),
col3_ binary(8), --NOTE: We are changing the column type here because you can't insert exact values in a timestamp.
col4_ char(10),
col5_ char(10),
constraint pk_tmp_test_ primary key (col1_) on "primary"
)
go
--insure we can insert identity values
set identity_insert tmp_test_ on
go
--copy data to temp table (no conversion required as timestamp and binary(8) are similiar datatypes
insert into tmp_test_ (col1_,col2_,col3_,col4_,col5_)
select col1_, col2_, col3_, col4_, col5_
from test_
go
--clean up
set identity_insert tmp_test_ off
go
--Compare the data
select * from test_
select * from tmp_test_
--Now drop the existing table and recreate it with the columns as desired
--NOTE: While we could simply rename the table I find I run into problems with constraint names and stuff, so
-- I prefer this longer method
if exists (select 1
from sysobjects
where id = object_id('test_')
and type = 'U')
drop table test_
go
Create table test_(
col1_ numeric(18,0) identity(1,1) not for replication,
col2_ char(10),
col3_ binary(8), --NOTE: We are changing the column type here because you can't insert exact values in a timestamp.
col4_ char(10),
col5_ char(10),
constraint pk_test_ primary key (col1_) on "primary"
)
go
--insure we can insert identity values
set identity_insert test_ on
go
--copy data to temp table (no conversion required as timestamp and binary(8) are similiar datatypes
insert into test_ (col1_,col2_,col3_,col4_,col5_)
select col1_, col2_, col3_, col4_, col5_
from tmp_test_
go
--clean up
set identity_insert test_ off
go
--Compare the data
select * from tmp_test_
select * from test_
--if everything fine then drop the temp table and you are back in business
if exists (select 1
from sysobjects
where id = object_id('tmp_test_')
and type = 'U')
drop table tmp_test_
go
HTH,
James.
-
Hello,
thank everybody for your contribution.
I have seen the large code to rebuild a col in the same place. (I have found out that SQL drop and re-create the same table observing it by SQL profiler).
The code is very useful, the only thing is that running it for 10, 20 tables and so on is very heavy, but with your useful information I will find a solution.
Thank
-
I think you are pretty much forced to use the user interface. Manually coding this stuff is just too time comsuming and error prone (been there, didn't quite do that
). I would strongly advise against that approach. Also for the benefit of everyone : USE SSC
GO
CREATE TABLE #Test (a INT NOT NULL, b TIMESTAMP NOT NULL)
GO
INSERT INTO #Test (a) VALUES (1)
INSERT INTO #Test (a) VALUES (3)
GO
SELECT * FROM #Test
GO
ALTER TABLE #Test
ALTER COLUMN b BINARY(8)
GO
SELECT * FROM #Test
GO
DROP TABLE #Test
RETURNS
(1 row(s) affected)
(1 row(s) affected)
(2 row(s) affected)
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'b' because it is 'timestamp'.
(2 row(s) affected)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply