SQL Server Performance Forum – Threads Archive

Insert without a cursor ?

Hi There This is probably an old often asked question. I need to do the following: INSERT INTO TABLEA (COLUMN LIST) SELECT (COLUMN LIST) FROM TABLEB. However i want a certain column in TABLEA to increment for each row inserted. How do i do this with 1 sql statement and without using a cursor ? Thanx

Since you don’t use an ORDER BY, what about doing some workaround?
CREATE TABLE SEQ_T
(
col1 INT DEFAULT 0
)
DECLARE @SEQUENCE INT
SET @SEQUENCE = 0
WHILE @SEQUENCE <=10
BEGIN
INSERT INTO SEQ_T DEFAULT VALUES
SET @SEQUENCE = @SEQUENCE+1
END
SET @SEQUENCE = 0
UPDATE SEQ_T SET @SEQUENCE = col1 = @SEQUENCE + 1
SELECT * FROM SEQ_T
DROP TABLE SEQ_T
col1
———–
1
2
3
4
5
6
7
8
9
10
11 (11 row(s) affected) If you want this directly in the INSERT you can do something like
CREATE TABLE SEQ_T2
(
col1 INT DEFAULT 0
)
INSERT INTO SEQ_T2 (col1)
SELECT
(SELECT
COUNT(*)
FROM SEQ_T b
WHERE b.col1 <= a.col1) col1
FROM SEQ_T a SELECT * FROM SEQ_T2
DROP TABLE SEQ_T
DROP TABLE SEQ_T2 Another way would be to do a SELECT INTO. There you could use the IDENTITY function. If you’re on SQL Server 2005 you can utilize ROW_NUMBER() —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

>>However i want a certain column in TABLEA to increment for each row inserted. Where do you want to show the data? If you use Front End application, do numbering there [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Are you aware of column identity property? If not, read more in BOL.

]]>