July 12, 2010 at 9:43 am
Hi, I have a loop to populate tables from a production database into a UAT database whilst applying some logic to reduce the number of rows in the UAT database as we don't have the disk space for it to be production sized. My problem is that the loop fails when trying to populate a table with a timestamp column - an oddity a bit like an identity column. As my loop is generic code, I really don't want to define a column list (I am copying 600 tables). Therefore, is it possible to temporarily define a default value that somehow means I don't need a column list and my generic insert will just work? Thanks.
July 12, 2010 at 10:32 am
Can you post sample of your "generic" code?
July 12, 2010 at 10:38 am
you can use "default" as per:
create table #tt (val int, ts timestamp)
insert #tt (val, ts) values (1, default)
July 12, 2010 at 11:09 am
Alan G-436699 (7/12/2010)
As my loop is generic code, I really don't want to define a column list (I am copying 600 tables).
Column List in the SELECT part of the code or in the INSERT part?
If the later, it's really bad practice to code an INSERT without a column list. It will cause you problems as soon as you change the schema of your tables. Mainly, your code will suddenly start failing because the number of items in your INSERT list will be greater than the number of items in your SELECT list (that's the error you'll get). The only way to avoid schema changes from causing your code to die is to define a column list in the INSERT part of your code.
But if you're talking about a default value for the SELECT list, see Eugene's post.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply