"Truncate" table variable?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"Truncate" table variable??

Author

Topic

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 20:30:10

I realize that it is in fact impossible to "truncate" a table variable. However I am looking to do something with similar functionality - delete all records from the table, and when it repopulates, to start from 1 again. I feel like this is insanely stupid, but I checked BOL without much success.

Any help/direction is much appreciated. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 20:48:49

use exec()


declare @table_name	varchar(30),
	@sql		varchar(100)

select  @table_name = 'your table'
select	@sql = 'TRUNCATE TABLE ' + @table_name 
print @sql
exec (@sql)

-----------------
'KH'
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 21:15:23

Hmm... this looks to me like it would truncate any physical table, but my question is not about using dynamic sql to truncate table @whatever, it is rather about clearing the contents of a local table variable.

For instance, the T-SQL below doesn't work... I get Incorrect Syntax near @whatever - and it highlights the truncate line.

declare @whatever table
(
id int identity,
number int
)

declare @start int
declare @stop int
set @start = 0
set @stop = 40

while @start <= @stop
BEGIN
insert into @whatever(number)
select @start

set @start = @start + 1
end

select * from @whatever

truncate table @whatever

select * from @whatever

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 21:31:32

oh table variable... just delete @whatever

-----------------
'KH'
if you can't beat them, have someone else to beat them

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-12 : 21:32:33

EDIT:

truncate TABLE @tablevar doesn't work because @tablevar is not a table. Just use DELETE.
next we can get rid of you loop for a set based solution

I suppose you're using truncate so that the identity value will be reseeded. dbcc checkident won't work either.

Be One with the Optimizer
TG

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 21:51:31

TG - yes, I want the identity to be reseeded. Will using "delete @whatever" reseed the identity? Let's pretend that using a loop is necessary, just for a second :)

The sproc I wrote uses a series of local table variables (yes, it is incredibly dumb and inefficient) to organize and rehash data. I ran into this table issue because the sproc is so poorly written. Its not even running on the server correctly... bleh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-12 : 21:58:08

Post your sproc and explain what are trying to do. There should be a better way to do this to achieve what you want.

-----------------
'KH'
if you can't beat them, have someone else to beat them

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-12 : 22:23:51

>>Will using "delete @whatever" reseed the identity?
Nope.

(truncate, dbcc checkident, identity_insert) none of these things work with table variables. You'll either need to declare a different variable or switch to an actual table.

Be One with the Optimizer
TG

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 22:26:33

All I want to do is create a logActivity report. It seems to be extremely simple. The tables I am working with are below. However, there is a catch (of course). The logout event client-side is not recorded. So, there is a sproc that runs that checks every 9 minutes to see if a user has been active since his last action. If yes, then the sproc just runs in another 9 minutes. If not, then the sproc inserts a logout event into the table. It is possible for a user to log in and out twice in less than 9 minutes, so obviously the table isn't necessarily correct. So, I need to make this table correct, and then create a report containing:

Who logged in, when they logged in, when they logged out, how long that session was, and the sum of time in minutes spent logged in for a particular day.

create table loginHistory
(
loginHistoryID int identity,
logTime smalldatetime,
username varchar (30),
password varchar (30),
loginResultID tinyint
)

create table loginResult
(
loginResultID tinyint identity,
loginResultDescription varchar (40)
)

insert into loginresult (loginresultdescription)
values ('Logged in')

insert into loginresult (loginresultdescription)
values ('Invalid login')

insert into loginresult (loginresultdescription)
values ('Logged out/Disconnected')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-12 : 22:39:15

couple questions:
since users can login and out multiple times per day how do you want the results structured? One result set with total time per day repeated for each session?
How do you want to handle when a user's session spans midnight

If you provide a few rows of sample data and what the query results would look like, that would help.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-12 : 22:43:41

Not sure of the req, but why not use #temp tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-12 : 23:09:02

From login to logout = 1 session

One report will have all sessions of every user, line by line. The other report is just an aggregate sum of minutes spent logged in. No one can login or stay logged in until midnight, so we never have to worry about that problem.

So end-query results might look like -
report 1:
user login logout minutesIn
rav3n0u5 2006-01-12 00:01:00.000 2006-01-12 00:04:00.000 3
rav3n0u5 2006-01-14 02:00:00.000 2006-01-14 05:00:00.000 180
rav3n0u5 2006-01-14 09:00:00.000 2006-01-14 14:00:00.000 300
rav3n0u5 2006-01-15 08:30:00.000 2006-01-15 09:30:00.000 60

report 2:
rav3n0u5 543

sample data:
1 2006-01-12 00:01:00.000 rav3n0u5 blahblah 1
2 2006-01-12 00:04:00.000 rav3n0u5 blahblah 3
3 2006-01-14 02:00:00.000 rav3n0u5 blahblah 1
4 2006-01-14 05:00:00.000 rav3n0u5 blahblah 3
5 2006-01-14 09:00:00.000 rav3n0u5 blahblah 1
6 2006-01-15 08:30:00.000 rav3n0u5 blahblah 1
7 2006-01-15 09:30:00.000 rav3n0u5 blahblah 3

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-13 : 05:40:12

I wrote a solution that ended up working pretty well.

I stuck with the table variable for a small table that I populated with the distinct users. But then I created a temp table that was essentially a miniature version of my main table. I was able to truncate this table as often as I pleased. I also made this a two-part procedure instead of the one huge one I was attempting.

First I "correct" the bad data by using some error-checking logic and these tables. Then I can safely create a report using Datediff and whatnot.

Thanks for the help!

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 06:27:45

~~Why not just DROP the @TableVar and recreate it? Should be quicker than DELETE @TableVar~~

Kristen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 07:52:48

>>Why not just DROP the @TableVar and recreate it?
Drop a table variable??

rav3nOu5, I'm glad you have a working solution. See if this is any better: (I didn't test it because I didn't feel like typeing all those insert statement)


--session dirations
select	username
	,login
	,logout
	,minutesin = datediff(minute,login,logout)
	,isValid = cast(1 as bit)
from	(
	select	username
		,login = logtime 
		,logout = (select min(o.logtime) 
			from loginHistory o
			where username = i.username 
			and o.loginResultid = 3
			and o.logtime > i.logtime)
	from	loginHistory i
	where	loginResultid = 1
	) a

union all

--invalid logins
select	username
	,logtime
	,null
	,0
	,cast(0 as bit)
from	loginHistory
where	loginResultid = 2

order by 2

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-01-13 : 07:53:44

quote:
Originally posted by TG

>>Why not just DROP the @TableVar and recreate it?
Drop a table variable??

rav3nOu5, I'm glad you have a working solution. See if this is any better: (I didn't test it because I didn't feel like typeing all those insert statement)

--session ~~dirations~~ durations
select username
,login
,logout
,minutesin = datediff(minute,login,logout)
,isValid = cast(1 as bit)
from (
select username
,login = logtime
,logout = (select min(o.logtime)
from loginHistory o
where username = i.username
and o.loginResultid = 3
and o.logtime > i.logtime)
from loginHistory i
where loginResultid = 1
) a

union all

--invalid logins
select username
,logtime
,null
,0
,cast(0 as bit)
from loginHistory
where loginResultid = 2

order by 2

Be One with the Optimizer
TG

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-01-13 : 08:37:19

"Drop a table variable??"

Yeah, like a Brick or a Clanger

Kristen

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-01-19 : 03:25:13

TG - thank you so much for that script. I actually had just finished creating something to insert appropriate logouts (this is an extremely over-engineered system that our c# programmer refuses to simplify...), and had not begun writing the actual audit. I am using a modified version of your report. It uses some techniques I have not had to use yet (as simple as it is!), and it ended up helping me write something I had to get out the next day!

Kristen - how often do people say that they have dropped a clanger?

fly2ajit
Starting Member

1 Post

Posted - 2009-10-12 : 09:23:22

try using

DELETE @tablename

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-12 : 10:04:43

quote:
Originally posted by Kristen

~~Why not just DROP the @TableVar and recreate it? Should be quicker than DELETE @TableVar~~

Kristen

Effect of being out of SQL Server?

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-12 : 10:50:24

Is it my imagination or does it seem that there are a lot of first time posters that choose to respond to long inactive posts? And usually, like this case, with an answer that was already given....odd.

Be One with the Optimizer
TG

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources