Post reply

Copying table structures without inheriting identity property

  • May 4, 2004 at 2:00 pm

    #108296

    Select top 0 or using a where 1=0, whether using a field list or *, seems to ALWAYS copy the identity property.

    I want to copy a table structure in TSQL, but with the ability to prevent the Identity Column property on a column being carried over into the new table.

    It'd be nice if it had other options, but no biggie if it doesn't.

    Is there some simple solution out there?  Or should I roll my own?

     

  • May 5, 2004 at 1:16 am

    #505251

    What happens if you use the field list, but replace the identity field with:

    CONVERT(int, <identity field&gt AS <identity field>

    together with top 0, etc. as you have mentioned?

  • May 5, 2004 at 1:19 am

    #505252

    Sorry for the smiley, seems like I have to change the spacing:

    CONVERT (int, <identity field> ) AS <identity field>

    but I guess you would have noted what it should be

  • May 5, 2004 at 7:30 am

    #505305

    Ah such a simple thing.  Thanks!  At first I wasn't sure, but I can custom-write the datatable build and leave it outside the utility sp.  For that, this will work splendidly.

    It still troubles me that there is no way to turn that off.  If I do a select into I typically have no desire for an identity column in the table.  If I do, it is far more difficult to remove one than it is to add one dynamically (which is simple).

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply