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.
| Author |
Topic |
|
Abu-Dina
Posting Yak Master
206 Posts |
 Posted - 2008-02-13 : 11:14:48
|
Afternoon SQL friends,I have the following two table :create table dbo.TypedTable ( FirstName varchar(20), Surname varchar(30), Age tinyint, Gender char(1))create table dbo.TypelessTable ( FirstName varchar(200), Surname varchar(30), Age varchar(200), Gender varchar(200)) The first table I called a Typed table as it has the correct data types for my columns. The second table contains the same named columns but all fields except for one are set to VARCHAR(200). I want to be able to change the data types in my second table to match those in the Typed table. In other words, I want to change the data type for Firstname, Age and Gender. But I am stuck!The way I plan to do this something like this. I create a temp table to hold the columns that need to change (this is the bit that I don't know how to do!)create table #AlterStagingTable ( ColumnInfo sysname ) I will then run a WHILE loop and run some dynamic SQL to alter my table. In other words, I obviously need to pull COLUMN_NAME, DATA_TYPE and CHARACTER_MAXIMUM_LENGTH from the INFORMATION_SCHEMA.COLUMNS to populate my temp table but this is the bit that I can't do. Do I do a left join between two derived (INFORMATION_SCHEMA.COLUMNS) tables and do a left join or something?Please help!Thanks in advance. |
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-13 : 11:28:16
|
Hi, I have solved the problem. Apologies for posting too soon!My solution is this :insert into #AlterStagingTable (ColumnInfo) select a.COLUMN_NAME +' '+ a.DATA_TYPE + case when a.CHARACTER_MAXIMUM_LENGTH is not null then '('+cast(a.CHARACTER_MAXIMUM_LENGTH as varchar(4))+')' else '' end + case a.IS_NULLABLE when 'NO' then ' not null' else ' null' end from (select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TypedTable') as a left join (select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TypelessTable') as b on a.COLUMN_NAME = b.COLUMN_NAME and a.DATA_TYPE = b.DATA_TYPE and a.CHARACTER_MAXIMUM_LENGTH = b.CHARACTER_MAXIMUM_LENGTH and a.IS_NULLABLE = b.IS_NULLABLE where b.COLUMN_NAME is null |
 |
|
|
|
|
|
|
|
Matching column data types...