I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
> >I made a mistake when I first created the column and just found out when I
> > tried to calculate. I tried using the design feature on Enterprise
> > Manager,
> > but got an error message that this couldn't be done. Any help is deeply
> > appreciate.
>
>
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Monday, March 26, 2012
How can I change a column with datatype "text" to "int"?
I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.
Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.
|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.
Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.
|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>
How can I change a column with datatype "text" to "int"?
I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>
Wednesday, March 21, 2012
How can I alter an int column to IDENTITY
Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
How can I alter an int column to IDENTITY
Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre Ament
No, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
How can I alter an int column to IDENTITY
Hi!
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
--
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
I have to copy complete table with auto increment column.
I create integer column, copy the data into it and want to alter it to
integer IDENTITY(1,1) PRIMARY KEY.
This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
ColumnName int IDENTITY(1,1) in Query Analyzer.
In SQL-DMO the identity property of the column is read only after the
creation.
This SQL command is good : SET IDENTITY_INSERT TableName ON
But I can insert rows only from SQL command not from an OLEDB recordset.
Exists the way to alter a column to IDENTITY not from Enterprise Manager?
I will be glad of any answer.
Regards,
Imre AmentNo, you can't alter a column to give it the IDENTITY property. You can drop
and recreate the column (if your table is empty). In the scenario you
describe, you can create the table _with_ the integer column with IDENTITY,
then SET IDENTITY_INSERT TableName ON, copy in the data, and then set
IDENTITY_INSERT off again.
--
Jacco Schalkwijk
SQL Server MVP
"Imre Ament" <ImreAment@.discussions.microsoft.com> wrote in message
news:77B40A32-3DF1-4543-8392-9D199EC0FCBA@.microsoft.com...
> Hi!
> I have to copy complete table with auto increment column.
> I create integer column, copy the data into it and want to alter it to
> integer IDENTITY(1,1) PRIMARY KEY.
> This SQL command cause an error : ALTER TABLE TableName ALTER COLUMN
> ColumnName int IDENTITY(1,1) in Query Analyzer.
> In SQL-DMO the identity property of the column is read only after the
> creation.
> This SQL command is good : SET IDENTITY_INSERT TableName ON
> But I can insert rows only from SQL command not from an OLEDB recordset.
> Exists the way to alter a column to IDENTITY not from Enterprise Manager?
> I will be glad of any answer.
> Regards,
> Imre Ament
Subscribe to:
Posts (Atom)