table variable...... clearing IDENTITY

[davejam]

hi all,

I am using a table variable to hold data while i play with records from other tables, unfortunately i have to use this table several times so i clear it out and use it again, the problem i have is that the id increments (as it should) each time i use the table.

I need to reuse the table variable but with the ID starting at 1 each time, theres not a great deal of help i can find through the web when it comes to table variables (more people trying to compare against temp tables) so i'm a little stumped!!!

If anyone knows how to clear down a table variable to use it a fresh please help!!!

Cheers



daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[TysonLPrice]

(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.

 

[davejam]

found a way round it..... a bodge as you will!!!!

using the same table variable, cleared it out each time, then found the first and last rec id into variables and used these to populate/process tables further along the line..... luckily the ID was only a reference for my code anyway.

I did try the likes of truncate etc but obviously got a nasty little microsoft error!!!!

but hey, bodge or not, got it working!!!

Cheers

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[ca8msm]

Why not use a temporary table rather than a table variable? e.g.

[COLOR=blue]CREATE[/color] [COLOR=blue]TABLE[/color] #TEST (id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), [COLOR=blue]name[/color] [COLOR=blue]varchar[/color](10))

[COLOR=blue]INSERT[/color] #TEST [COLOR=blue]VALUES[/color] ([COLOR=red]'Mark'[/color])
[COLOR=blue]INSERT[/color] #TEST [COLOR=blue]VALUES[/color] ([COLOR=red]'Denis'[/color])
[COLOR=blue]INSERT[/color] #TEST [COLOR=blue]VALUES[/color] ([COLOR=red]'George'[/color])

[COLOR=blue]SELECT[/color] id, [COLOR=blue]name[/color] [COLOR=blue]from[/color] #TEST

[COLOR=#FF00FF]TRUNCATE[/color] [COLOR=blue]TABLE[/color] #TEST

[COLOR=blue]INSERT[/color] #TEST [COLOR=blue]VALUES[/color] ([COLOR=red]'Dave'[/color])

[COLOR=blue]SELECT[/color] id, [COLOR=blue]name[/color] [COLOR=blue]from[/color] #TEST

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[davejam]

i know theres a big debate as to whether table variabl;es or temp tables are the best thing to use, i tend to go down the table variable route and i managed to get around the problem anyway......

cheers for the tip though

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[ca8msm]

I tend to go down the table variable route as well but in this case it's clear that a temp table would be more beneficial as it provides the functionality that you need.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.