|
Home > Archive > microsoft.public.sqlserver.server > August 2002 > Identity column and insert into
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Identity column and insert into
|
|
| Tom Pester 2002-08-14, 6:23 am |
|
Hi all,
I am wondering for quite some time if the following query can be put to work
: insert into tableA select * from tableB
TableA and tableB have the same shema.
TableA contatains and identy column and sql server complains that it can't
be null.
I want new identity alues in Table A otherwise i could have used 'identiy
insert ON'.
Of course you can use the (field1,field2) VALUES (a,b) syntax and ommiting
the identy field,
but I want to use the * for not having to change this consruct when the
table changes it its number of fields.
Is there a trick or can I write a function that dynamicaly constructs the
fieldlist so that it isn't hardcoded?
I came across this problem once again when I coded an instead of trigger on
a single table view that must pass the insert to its base table
but executes some additional code also
I would be very happy to find a solution because this problem keeps poppins
up.
I am sure that others have come across this.
Thanks in advance
Tom
Publinet.be
| |
| Andrew J. Kelly 2002-08-14, 6:23 am |
| You can do the "SELECT * INTO TableB FROM TableA" and then drop the
Identity column and add a new one.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Tom Pester" <tom@publinetDELLETEETHIS.be> wrote in message
news:ud74Fa4QCHA.1956@tkmsftngp13...
>
> Hi all,
>
> I am wondering for quite some time if the following query can be put to
work
> : insert into tableA select * from tableB
> TableA and tableB have the same shema.
> TableA contatains and identy column and sql server complains that it can't
> be null.
> I want new identity alues in Table A otherwise i could have used 'identiy
> insert ON'.
>
> Of course you can use the (field1,field2) VALUES (a,b) syntax and ommiting
> the identy field,
> but I want to use the * for not having to change this consruct when the
> table changes it its number of fields.
>
> Is there a trick or can I write a function that dynamicaly constructs the
> fieldlist so that it isn't hardcoded?
>
> I came across this problem once again when I coded an instead of trigger
on
> a single table view that must pass the insert to its base table
> but executes some additional code also
>
> I would be very happy to find a solution because this problem keeps
poppins
> up.
> I am sure that others have come across this.
>
> Thanks in advance
>
> Tom
> Publinet.be
>
>
>
>
>
>
|
|
|
|
|