IDENTITY values in a stored procedure

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END

This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5....
or is there any chance of ID column having values like 1,2, 4, 6,7,8...

Please reply...
qaAs long as you don't do any deletes from your temp table, your identity column should remain sequential with no gaps.|||Thanks for your quick response.sql

IDENTITY SELECT

Hi All
How Can I Select From Table With IDENTITY For Row Count
The Table Has No IDENTITY Column
Select Col1,IDENTITY As Col2 From My Table
Col1 Col2
A 1
B 2
C 3
D 4
.. ..
ThanksHere is an article I wrote about this that might help:
http://www.databasejournal.com/feat...cle.php/2244821
"Taha" wrote:

> Hi All
> How Can I Select From Table With IDENTITY For Row Count
> The Table Has No IDENTITY Column
> Select Col1,IDENTITY As Col2 From My Table
> Col1 Col2
> A 1
> B 2
> C 3
> D 4
> ... ..
> Thanks
>
>
>
>|||OK
Thank You Greg Larsen
"Greg Larsen" <GregLarsen@.discussions.microsoft.com> wrote in message
news:CE2DF0A4-859B-465F-A55F-B4630F8917A7@.microsoft.com...
> Here is an article I wrote about this that might help:
> http://www.databasejournal.com/feat...cle.php/2244821
>
> "Taha" wrote:
>|||I have an article too. Not to compete with Greg, in fact I'm sure they're
similar. Just posting for completeness.
http://www.aspfaq.com/2427
"Taha" <taha105@.hotmail.com> wrote in message
news:OeRTdCwlGHA.4772@.TK2MSFTNGP04.phx.gbl...
> Hi All
> How Can I Select From Table With IDENTITY For Row Count
> The Table Has No IDENTITY Column
> Select Col1,IDENTITY As Col2 From My Table
> Col1 Col2
> A 1
> B 2
> C 3
> D 4
> .. ..
> Thanks
>
>
>

Identity

In a table(say table1) I have a identity column(say Col1).I want to remove the identity property of the Col1 but int property will remain same.
How can I do it by Query Analyzer?
Subhasishcreate new table without identity col , copy the data , drop the old table and rename the new table to the old table name :mad:

Identity

Suppose I have a table named table1 which has a identity field named "Col1".Now i want to have a backup of this table by running this script
select * into table1_backup from table1
I get the backup in the table table1_backup but i miss the identity property for the field col1.
My question is
1) How can I get the identity property by running that script?
2)How can i have the constraints of table1 in table1_backup?
SubhasishLooks like your trying to make an exact copy of the original table including the identity seed?

To import the identity turn on identity insert like so:

Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Brent|||Thanks Bren.
But what is about my second question?|||Brent
This script will not work
Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Because the table isgetting created in the second stape(Select * into table1_backup from table1)
So before creating the table how can it's IDENTITY_INSERT property set to on or off?
Subhasish|||Sorry bout that, this one should work,, takes awhile longer as you need to define the datatypes for each column such as col1 int, col2 varchar(20)

Create Table table1_backup (col1 col1type, col2 col2type, etc)

Set IDENTITY_INSERT table1_backup ON
Insert into table1_backup (col1, col2, etc)
select col1, col2, etc
from table1
Set IDENTITY_INSERT table1_backup OFF

As for the second question, not sure off the top of my head on importing contraints. I'll look around, but best bet would be to put that question in a new thread here in dbforums.

Brent

Originally posted by subhasishray
Brent
This script will not work
Set IDENTITY_INSERT table1_backup ON
Select * into table1_backup from table1
Set IDENTITY_INSERT table1_backup OFF

Because the table isgetting created in the second stape(Select * into table1_backup from table1)
So before creating the table how can it's IDENTITY_INSERT property set to on or off?
Subhasish