How to copy a table structure (only fields) without identity and anything else
-
Hi,
I have a question for you....
I need to copy a table...I mean: duplicate a table from another one with only fields without identity, constraints and so on.
I tried with
select into #table from atable where 1=0
but doing so the fields are copied but also the identity if any...
I need copy only fields and datatype....
Any suggestion?
Thank in advance
-
Select col1, col2, coln, 0 as id into #table from dbo.atable where 1=0
BTW, DON'T DO THIS in production. It's cool for a one time copy of the table, but even then it's just lazy
. -
Just Cast the Identity column to int on the Filed List of the select statement!
* Noel -
Always another way to skin the cat
.On board with the not doing it in production part??
-
Thank very much.......
but I need this in production to make update from a file created with bcp from another database....
Very bad in production in your opinion....?
thank......
-
Yes, it can create locks in tempdb and grind the server to a halt (Less likely with 1=0).
If you need a table, script it out and execute that script when needed. Or you could always create a perm table for this task and use it (if you only have one instance of the process running at the same time).
-
Totally on board with the production part. If and only If the server is lightly loaded at the time you are executing this script (with 1=0) it may be OK.
If all you need is to save typing you should reconsider the strategy as pointed above. If you need to avoid code changes due to Base Table changes you may want to implemet a dependecy system (not a trivial task) or simply document on your environment that you may need to change this if the base table changes.
Just my $0.02
* Noel -
It's always simpler to document... assuming the guys read it. Makes better code anyways.
Hey Noeld, wanna race to 3k posts
?[EDIT]
DAMN it was this one
!! -
Congratulations dude!
* Noel -
Tx. Now I can say I'm almost in range of Frank
. -
I think I won't be able to get to 3K but is there any where in the site a list of the frequent/higher posters?
* Noel -
I don't think so.
Maybe you should suggest it. I would do it but it wouldn't look to good
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply