Inserting into table with one column 1,2,3,4...

Question

Post reply

Inserting into table with one column 1,2,3,4...

Senthil T

Hall of Fame

Points: 3367
More actions
January 14, 2005 at 2:09 pm

#60044

Hi,
I have a temporary table into which I insert bulk data from a stored procedure output. It will be easy for me if the temptable can have a column with sequential numbers 1,2,3,4,. I could not figure out a way to do it. This is the stripped down sql what I have in the stored procedure.
insert into temptable select name, email, date from usertable where condition = xyz.
I tried auto increment column with seed 1, increment 1. But if I delete and insert again it does not start from 1. Is there any way to accompolish this? It will make the front-end code very simple.
Thank you.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You can use the TRUNCATE TABLE temptable to delete ALL the rows from the temp table before inserting the new data.

rhunt SSCertifiable Points: 5868 More actions · Answer 2

Senthil, is this an example of what you are looking for?

use tempdb

create table sp_who (ID INT IDENTITY (1,1) NOT NULL

,spid varchar (10)

,status varchar (20)

,loginname varchar(20)

,hostname varchar(20)

,blk varchar(20)

,dbname varchar(20)

,cmd varchar(20))

GO

use master

INSERT INTO sp_who (spid, status, loginname, hostname, blk, dbname, cmd)

execute master..sp_who

GO

select * from tempdb.dbo.sp_who

Senthil T Hall of Fame Points: 3367 More actions · Answer 3

Hi,

I tried to change it to TRUNCATE TABLE, but it says user does not have permission to perform this operation on table 'temptable'. I checked all the permission check boxes but it is still not working. What am I missing here?

Thanks.

rhunt SSCertifiable Points: 5868 More actions · Answer 4

From books on line:

"TRUNCATE TABLE permissions default to the table owner and are not transferable."

Ryan

Senthil T Hall of Fame Points: 3367 More actions · Answer 5

Hi,

I changed the owner of the temp table and it works fine. Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Ok, then you can always do something like this :

declare @Results table (MyIdent int identity (1,1), fields...)

Insert into @Results (Select ....)

Select * from @Results

Or you could do some acrobatics to generate to number line by line.

rhunt SSCertifiable Points: 5868 More actions · Answer 7

Senthil,

As a final note. See Remi's use of the table variable. If you are using this for a one time query, you really will want to do it that way. However, if you are going to use the table after you initial connection (or the completion of a GO batch) you will want to use a temp table.

If you use the temp table, make sure to go back and DROP TABLE TEMP_TABLE

If you can though, use the variable and table data type.

Ryan

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 8

> If you are using this for a one time query, you really will want to do it that way

... unless the return is very large in which case you'll run out of physical memory and start hitting the swap file. In cases where very large returns are expected, you're better of using a permanent but Truncatable working table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)