if ALTER myTable ALTER COLUMN id ...
to remove IDENTITY from a column is not possible (what i fear), can i
change this in a system table?
HelmutHelmut
Why ? It is strongly not recommended to alter system tables.
Why don't you want to move the data to the new column and then drop the old
one?
"Helmut Woess" <user22@.inode.at> wrote in message
news:sdwgal2frk9n$.gvo98pu5gu99$.dlg@.40tude.net...
> if ALTER myTable ALTER COLUMN id ...
> to remove IDENTITY from a column is not possible (what i fear), can i
> change this in a system table?
> Helmut|||Am Sun, 30 Apr 2006 14:50:06 +0300 schrieb Uri Dimant:
> Why ? It is strongly not recommended to alter system tables.
> Why don't you want to move the data to the new column and then drop the o
ld
> one?
Okay, here the whole story: i have a special stored proc called by the
trigger, and in this stored proc i need the data from temp tables deleted
and inserted.
For this i did a "insert into #ins select * from inserted" (and the same
for deleted) and used the data in #ins/#del in the stored proc. This is
working, but it needs too much time and ressource, to create and drop this
temp tables in every call of the trigger.
So i thougt, i create a global ##ins/##del at first call of the trigger:
if object_id('tempdb..##table1_ins') is Null -- create table
select * into ##table1_ins from inserted where 1 = 2
...
and then there is no need to create and drop the temp tables in each
execution of the trigger.
But if i do this, i cannot longer do a "insert into #ins select * from
inserted" i must do a "select * into ##table1_ins from inserted".
And my problem is now, that every record has as first field a field named
id and this is an identity field. So a "select * into ##table1_ins from
inserted" is not working because of the identity field.
But if i can change this and remove the identity property it should work.
It doesn't work if i drop this column and add it again, because it is added
at the end of the record and not at the beginning, so a "select * into..."
would not longer be possible.
And i don't want to set identity_insert ON and OFF and use a field list for
the select statement, because i have more than 120 tables where i need this
solution and some of the records have nearly 200 fields, so i need a
universal solution to bring the data from temp tables inserted and deleted
into the stored proc. In this misery i would even change system tables if
this helps.
Maybe you have a better idea how to do this?
thanks, Helmut|||Helmut
As I see you are using SQL Server 2000, am I right? In SQL Server 2005 you
have a new functino ROW_NUMBER() which provides you a sequential number of a
row within a partition of a result set.
Well, I don't know your business requirements , however I'd redesign an
approach of calling stored procedures within a trigger and morepver creating
a temporaring tables.
Now, can you create a surrogate key in your tables that you will see in
'deleted' and 'inserted' virtual tables , so there is no need to add am
identity property at all, or if it does not help please post the code and
additional info about what are you trying to achive.
"Helmut Woess" <user22@.inode.at> wrote in message
news:ket7d7xavej9$.1niowodavl99.dlg@.40tude.net...
> Am Sun, 30 Apr 2006 14:50:06 +0300 schrieb Uri Dimant:
>
> Okay, here the whole story: i have a special stored proc called by the
> trigger, and in this stored proc i need the data from temp tables deleted
> and inserted.
> For this i did a "insert into #ins select * from inserted" (and the same
> for deleted) and used the data in #ins/#del in the stored proc. This is
> working, but it needs too much time and ressource, to create and drop this
> temp tables in every call of the trigger.
> So i thougt, i create a global ##ins/##del at first call of the trigger:
> if object_id('tempdb..##table1_ins') is Null -- create table
> select * into ##table1_ins from inserted where 1 = 2
> ...
> and then there is no need to create and drop the temp tables in each
> execution of the trigger.
> But if i do this, i cannot longer do a "insert into #ins select * from
> inserted" i must do a "select * into ##table1_ins from inserted".
> And my problem is now, that every record has as first field a field named
> id and this is an identity field. So a "select * into ##table1_ins from
> inserted" is not working because of the identity field.
> But if i can change this and remove the identity property it should work.
> It doesn't work if i drop this column and add it again, because it is
> added
> at the end of the record and not at the beginning, so a "select * into..."
> would not longer be possible.
> And i don't want to set identity_insert ON and OFF and use a field list
> for
> the select statement, because i have more than 120 tables where i need
> this
> solution and some of the records have nearly 200 fields, so i need a
> universal solution to bring the data from temp tables inserted and deleted
> into the stored proc. In this misery i would even change system tables if
> this helps.
> Maybe you have a better idea how to do this?
> thanks, Helmut|||Am Sun, 30 Apr 2006 16:08:39 +0300 schrieb Uri Dimant:
> Helmut
> As I see you are using SQL Server 2000, am I right? In SQL Server 2005 you
> have a new functino ROW_NUMBER() which provides you a sequential number of
a
> row within a partition of a result set.
> Well, I don't know your business requirements , however I'd redesign an
> approach of calling stored procedures within a trigger and morepver creati
ng
> a temporaring tables.
> Now, can you create a surrogate key in your tables that you will see in
> 'deleted' and 'inserted' virtual tables , so there is no need to add am
> identity property at all, or if it does not help please post the code and
> additional info about what are you trying to achive.
>
Uri,
you are right, it is SQL2000 (but we want to change to 2005 the next
w
s).
What i have to solve: log all inserts/changes/deletes
Because i have to do this for a lot of tables i need an universal solution.
For this i use a trigger, the trigger only puts data from inserted/deleted
into temp tables and calls a stored proc which makes the rest.
This is because if something changes, i have only the stored proc to change
and not hundred of triggers.
The business is now, how can i bring the data from inserted/deleted as fast
as possible in an universal way into this stored proc.
And i am figthing with the fact that that every of this tables has this
identity field as first field. I don't want to add identity attribute, i
want to remove it!
Because of automation, temp table ##tbl is created as
"select * into ##tmp from inserted where 1 = 2"
which creates an empty copy of inserted, unfortunately with identity
attribute too.
Then i want to copy data from inserted into #tbl with
"insert into ##tbl select * from inserted"
which is not possible as long as ##tbl has this identity attribute too.
If you tell me that it is not possible in a simple way in SQL2000, then i
could stop this work for SQL2000 and search for a solution in SQL2005.
What do you think, will it be easier in SQL2005 (maybe using CLR)?
sorry, i cannot post complete code (company ownership, you know)
thanks, Helmut|||Helmut Woess (user22@.inode.at) writes:
> Okay, here the whole story: i have a special stored proc called by the
> trigger, and in this stored proc i need the data from temp tables deleted
> and inserted.
> For this i did a "insert into #ins select * from inserted" (and the same
> for deleted) and used the data in #ins/#del in the stored proc. This is
> working, but it needs too much time and ressource, to create and drop this
> temp tables in every call of the trigger.
> So i thougt, i create a global ##ins/##del at first call of the trigger:
> if object_id('tempdb..##table1_ins') is Null -- create table
> select * into ##table1_ins from inserted where 1 = 2
> ...
Use a permanent table, keyed by spid instead.
Read more about it here:
http://www.sommarskog.se/share_data.html#prockeyed
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_v6eqLjaArfINnzWxtY1R9lHqwFsLH7a2w0MBbjGdEhQffS8VpKmUBlCQTftYLrwUlNFBZdWrrK13Qb7lrMwsZEvW_FKb_xTvmUTki7fvNvEsDG3HGKCw=s0-d)