ccunni1
asked on
How to determine of if columns exist in one table and not another and alter the table
I have two tables of which one is a backup table. I need to dynamically add columns to the backup table if they existi in the primary table but not in the backup table.
Say your tables are t_1 and t_2
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + 'alter table t_2 add ' + quotename(c.name) + ' ' + t.name +
case
when t.name in (
'varbinary','varchar','binary','char','nchar','float',
'time','datetime2','datetimeoffset')
then '(' + right(c.xprec,10) + ')'
when t.name = 'nvarchar'
then '(' + right(c.length/2,10) + ')'
when t.name in ('decimal','numeric')
then '(' + right(c.xprec,10) + ',' + right(c.xscale,10) + ')'
else
''
end
+ ';'
from
(
select a.*
from
(select c.* from sysobjects o inner join syscolumns c on c.id=o.id where o.name = 't_1') a
left join
(select c.* from sysobjects o inner join syscolumns c on c.id=o.id where o.name = 't_2') b
on a.name=b.name
where b.name is null
) c
inner join systypes t on t.xtype=c.xtype and t.xusertype = c.xusertype
;
--print @sql
exec (@sql)
ASKER
I am getting a Msg 1001, Level 15, State 1, Line 1
Line 1: Length or precision specification 0 is invalid.
Line 1: Length or precision specification 0 is invalid.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
You can use a SQL statement like below against the INFORMATION_SCHEMA to build dynamic ALTER TABLE statements.
The query will return for you the columns and associated information that are in the live table that do not exist with same name in the backup table.
You can then use columns like COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, et cetera to build your ALTER TABLE {backup table name} ADD ... statements.
http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx
Hope that helps!
Kevin
Open in new window