DataType Change in Large Table
-
I am having trouble establishing a relationship between two of my tables. The problem is that the data type in the Primary Key Table is bigint and the data type in the Foreign Key Table is nvarchar.
When I make the change to the Foreign Key table, which by the way has a little over 40K rows, and try to save it, it just hangs up the database and my Microsoft Management Console is "Not Responding" and eventually boots me out.
Any ideas anyone?
Thanks for any input!
Kristin
Kristin
-
The way I would do it:
First off
SELECT FKCol
FROM TableName
where ISNUMERIC(FKCol) =0
To make sure that the all data in the column is a valid numeric number.
ALTER TABLE TableName
Drop constraint FK_CONSTRAINT_NAME
GO
ALTER TABLE TableName
Add column FKCol_New Bigint NOT NULL default 0
GO
UPDATE TableName
SET FKCol_New = FKCoL
GO
ALTER TABLE TableName
DROP column FKCol
GO
ALTER TABLE TableName
ADD column FKCol Bigint NOT NULL default 0
GO
UPDATE TableName
SET FKCol = FKCoL_New
GO
ALTER TABLE TableName
ADD constraint FK_CONSTRAINT_NAME
GO
I think that would be in the ball park.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
If the ISNUMERIC test reports that all the data is, in fact, numeric, you should be able to just issue an alter statement in Query Analyzer, like the following:
ALTER TABLE TableName
ALTER COLUMN ColName bigint
The steps outlined by Jim will work, but are probably overkill. In fact, I wouldn't be surprised at all if Enterprise Manager is doing something very similar in the background, which is why I don't use EM to make changes to the database...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I am pretty sure you have to drop the foreign Key first!
Then do the alter
and later recreate the Foreign Key.
* Noel -
One of the reasons em takes so long on any large table alteration is that it inserts all records into temp table and inserts back after the change (pretty much recreates table)
-
If the data types are not the same you can't declare a fk constraint, so he can't have a fk on the table.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yep
You are right but that is what the poster said
* Noel -
Thanks for all your help guys, worked like a charm
I went with the simpler alter table route:ALTER TABLE TableName
ALTER COLUMN ColName bigint
really appreciate all the input!
Kristin
-
FWIW, there are some funny annoyances possible when using ISNUMERIC.
SELECT
ISNUMERIC('$') AS Money_1
,ISNUMERIC('2d3') AS Float_1
, ISNUMERIC('$+,') AS Money_2
Money_1 Float_1 Money_2
----------- ----------- -----------
1 1 1
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I know, but we could probably assume that the original FK relationship was done progromatically instead of constraint/referential.
Just wanted her to check for the obvious.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply