... or changing system table

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
ws).
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

(re)using a temporary table in a stored proc (was "Confusion")

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?

Howdy!Yes..the local temp table is only around for the length of process...

A global temp table sticks around as long as it's referenced by any process...

So you can do something like this...basically wrapping your sprocs with a driver sproc

USE Northwind
GO

CREATE PROC mySproc00
AS
BEGIN
UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
END
GO

CREATE PROC mySproc99
AS
BEGIN
SELECT * INTO ##myGlobalTemp99 FROM Orders

SELECT TOP 10 * FROM ##myGlobalTemp99

EXEC mySproc00

SELECT TOP 10 * FROM ##myGlobalTemp99

DROP TABLE ##myGlobalTemp99

END
GO

EXEC mySproc99
GO

DROP PROC mySproc00
DROP PROC mySproc99
GO|||Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.

Any other guidlines!

Howdy!|||post what the table looks like...and what the sproc is suppose to be doing...|||Instead of Temp table, declare table type variable and work with that.

Madhivanan|||I believe he needs to pass and store data between processing threads...table variables won't let you do that.

Why I asked for the Table Layout is this.

Each Process gets it's own unique spid.

I would create a permanent table, and and a column for the spid.

Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

Just make sure you clear out the rows for your spid before you start the process.|||Yes, Brett; that idea helped. thanx. :)

Howdy!|||Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

I'll take a look into this...|||I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

create proc sp_1 as selct * from #t
go
create proc sp_2 as
ceate table #t (f1 int null)
insert #t select 1 union select 2
exec sp_1
go
exec sp_2
go
drop proc sp_1, sp_2
go

Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.|||Doooh...it's the same spid

As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

Even with that...what I suggested wouldn't buy you anything anyway

Thanks for pointing that out...|||Doooh...Even with that...what I suggested wouldn't buy you anything anyway...So why did you suggest it? :D|||Cause I'm a moron...

It's like flying down the freeway...I missed the exit

You could use that method to pass data to a trigger though...|||So if that is not causing the problem he is having, then what is?

If i call #mytable after executing the procedure; won't work.

This seems to indicate he wants to access the table through a different connection...|||If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?Actually it looks like the same connection, unless his definition of a "session" is different...|||It's like flying down the freeway...I missed the exit...Don't feel bad, I use turn-arounds very often :D|||Then maybe he should post his code, 'cause something must be going wrong...|||He's probably creating the temp in the nested proc

The temp needs to be created in the driver

I imagine this is what's going on

create proc sp_2 as
create table #t (f1 int null)
insert #t select 1 union select 2
go

create proc sp_1 as
EXEC sp_2
select * from #t
go

exec sp_1
go

drop proc sp_1, sp_2
go|||Hi folks; u've been so kind! Sorry for getting back late;

I've a proc proc1 that performs some routine and outputs a temporary table #tab1. I want this table be accessed through other procs or within a batch that calls this procedure; WITHIN THE SAME CONNECTION(SESSION,SPID :))

The only way around i could find was the SPID idea into a permanent table.

The proc1 is a standard routine and many other procs or scripts need the output from it; i wonder if i could use function that returns a table... that could be used further.

Howdy|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua

First read Brett's sticky on top of the forum.|||...and start a new thread rather than resurrecting this old one...