Alter Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alter Table

Author

Topic

ahsan19
Starting Member

7 Posts

Posted - 2006-11-06 : 11:57:36

Hi Kathi

I hope you are doing ok. I have a quick question about changing the data types in an SQL database. We have a database where the zip code field in a coloum is defined as a bigint (since US zip codes are all numeric). We would like to change the zip code data type from bigint to varchar, in order to accomodate canadian zip codes (since they are alphanumeric). Is there a way to do this conversion in SSIS? If not, what other ways can this be done?

Thank you in advance.
Syed Ijaz

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 12:04:40

select convert(varchar(20), yourZipIntColum) as yourZipVarcharColum
from YourZiptTable

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Kristen
Test

22859 Posts

Posted - 2006-11-06 : 12:13:34

Changing the data type of your ZipCode column should implicitly convert from INT to VARCHAR

Backup & Test first though!

Kristen

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 12:16:36

This will change data type of the column in the table, just run it in a query window, no need to use SSIS. Change the width (I used 20) of the varchar to whatever you need, but make sure it is enough to hold your longest current value or you'll get an error.

ALTER TABLE mytable
ALTER COLUMN mycol varchar(20)

ahsan19
Starting Member

7 Posts

Posted - 2006-11-06 : 12:56:40

ALTER TABLE mytable
ALTER COLUMN mycol varchar(20)
-
Thank you. This worked well. I would like to do this to ALL the tables in the database. Basically, I am looking to execute a command that would loop through all the tables, and inside each table, it should loop through all the columns and change the Zip data type to varchar(10). How can this be done?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 16:35:27

I wouldn't recommend that you do that automatically because you want to check that its valid for each table and column, but you can easily find all the tables with a column named ZipCode like this (assumes that the names are the same in every table, but you could easily tweak this for other names as necessary)

select table_name from information_schema.columns
where column_name = 'ZipCode'

Then you could use that to generate your code and just copy the code from the query results (in text mode), something like

select 'ALTER TABLE [' + table_name + ']
ALTER COLUMN [ZipCode] varchar(10)
GO'
from information_schema.columns
where column_name = 'ZipCode'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources