Changing column from nvarchar to int

I am upgrading an Access database and need to convert some of the fields
from text to a foreign key from another table. This is no problem and I
already have the query batch to do this. I would like to know if it is
possible to change a column type from nvarchar to int.
Thanks,
Drew LaingAs long as all the NVARCHAR values can be converted to an INT, sure.
ALTER TABLE tablename ALTER COLUMN columnname INT
http://www.aspfaq.com/
(Reverse address to reply.)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>|||Drew,
You can do it if the values are numeric or null. If the value can not be
casted, then you will get an error.
Example:
use northwind
go
create table t (
colA varchar(15)
)
go
insert into t values('1')
insert into t values('2')
insert into t values(null)
go
select * from t
go
alter table t
alter column colA int
go
select * from t
go
alter table t
alter column colA varchar(15)
go
insert into t values ('a')
go
-- will fail
alter table t
alter column colA int
go
select * from t
go
drop table t
go
AMB
"Drew" wrote:

> I am upgrading an Access database and need to convert some of the fields
> from text to a foreign key from another table. This is no problem and I
> already have the query batch to do this. I would like to know if it is
> possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>
>|||Thanks to both!
Thanks,
Drew
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uMwE9OZJFHA.3184@.TK2MSFTNGP10.phx.gbl...
>I am upgrading an Access database and need to convert some of the fields
>from text to a foreign key from another table. This is no problem and I
>already have the query batch to do this. I would like to know if it is
>possible to change a column type from nvarchar to int.
> Thanks,
> Drew Laing
>

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.
> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:
>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,
> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.

> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated with
> numerous rows. This table should have been created with one primary and one
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,
|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:

>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and one
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing a Primary Key to Foreign Key

I created a table with two primary keys. The table was been populated with
numerous rows. This table should have been created with one primary and one
foreign key.
What is the correct procedure to correct this error?
Thanks,> I created a table with two primary keys. The table was been populated
with
> numerous rows. This table should have been created with one primary and
one
> foreign key.
A table can have only one Primary Key.

> What is the correct procedure to correct this error?
Check the ALTER TABLE command in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Joe,
Are you sure about this? SQL server will not allow you to create two
primary keys constraints.
create table joe1 (
i int not null primary key,
j int not null primary key)
GO
Server: Msg 8110, Level 16, State 1, Line 1
Cannot add multiple PRIMARY KEY constraints to table 'joe1'.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> I created a table with two primary keys. The table was been populated wit
h
> numerous rows. This table should have been created with one primary and o
ne
> foreign key.
> What is the correct procedure to correct this error?
> Thanks,|||On Mon, 17 Jan 2005 19:27:01 -0800, Joe K. wrote:

>I created a table with two primary keys. The table was been populated with
>numerous rows. This table should have been created with one primary and on
e
>foreign key.
>What is the correct procedure to correct this error?
>Thanks,
Hi Joe,
You can never have two primary keys on one table. I guess you are using a
visual development tool (Enterprise Manager?) and seeing two key symbols.
That actually means that you have one compound (i.e. spanning multiple
columns) primary key.
The first thing you should do is to check the current data against the
intended constraints. You'll probably find that you have duplicates in the
column intended to be primary key and that you have values in the intended
foreign key that are not in the referred table. Fix those errors first.
After that, you COULD use ALTER TABLE commands to fix it, but it might in
this case be quicker to rename your current table (using sp_rename),
create a new table with the intended constraints and copy all data from
the renamed old table to the correct new table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)