Copy Default Values 1

[josephwalter]

I have two tables that have the exact same fields. One is named MAIN_03 and the other MAIN_04. The '03 tables holds 2003 data and the '04 holds 2004 data - I don't like it, but I'm not allowed to change the table structures.

When we copied the '03 table to create the '04 table we didn't get the Default Values copied over. Now the '04 table has data in it, so I can't simply blow it away and copy the table again.

How can I copy the Default Values from one table, into another table that has all the same field names? By the way, the table has an un-godly number of fields, so doing it by hand would be ridiculous.

Any suggestions???

 

[philhege]

josephwalter,

Some psuedocode:
declare @l_ccolname varchar(128), @l_cdefaultval varchar(128), @l_vsqlstring varchar(2048)
select column_name, column_default, 0 as processed INTO
#tmp_columninfo
from information_schema.columns
WHERE table_nam = your03table

while (select count(*) from #tmp_columninfo where processed = 0 ) > 0
begin
select top 1 @l_ccolname = column_name, @l_cdefaultval = column_default from #tmp_columninfo where processed = 0

set @l_vsqlstring = 'alter table my04table
(alter column '+@l_ccolname+ ' DEFAULT '+@l_cdefaultval+')'
exec(@l_vsqlstring)

update #tmp_columninfo set processed = 1 where column_name = @l_ccolname
end


I say "pseudo" because I didn't test it, OK?

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[josephwalter]

That bit of code was precisely what I was looking for... Thank you!

I am getting an error "Incorrect syntax near the keyword 'DEFAULT'." I looked in the Books Online, but I couldn't figure out what I was doing wrong. Here's the sql statement I'm trying to run:

[tt]alter table MAIN_O4 alter column OPERDAYS DEFAULT (0)[/tt]

 

[philhege]

I think the ALTER COLUMN statement and parameters have to be in parentheses.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....