INSERT INTO SomeTable(thing1, thing2)
SELECT thing1, thing2
FROM SomeTable

SET @SelectResult = (SELECT thing, thing2 FROM SomeTable) 

DECLARE TBL CURSOR FOR 
SELECT thing, thing1 from SomeTable

OPEN TBL
FETCH TBL INTO @thing1, @thing2

WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO AnotherTable VALUES (@Tthing1,@thing2)
	FETCH TBL INTO @thing, @thing2
END
CLOSE TBL
DEALLOCATE TBL

quote:

---

If you are doing a straight insert then you don't need to store the result in a variables.

INSERT INTO SomeTable(thing1, thing2)
SELECT thing1, thing2
FROM SomeTable

However if you are going to manipulate the data in a certain way and you need to look at each record individually then consider the following approach :

I know people will bite my head off for suggesting this but have you tried using a cursor?

It seems that most SQL developers here avoid using cursors but your code suggests that it is probably the best option.

SET @SelectResult = (SELECT thing, thing2 FROM SomeTable) 

You are trying to store thing and thing2 in one variable?!

something like the following will help you,

DECLARE TBL CURSOR FOR 
SELECT thing, thing1 from SomeTable

OPEN TBL
FETCH TBL INTO @thing1, @thing2

WHILE @@FETCH_STATUS=0
BEGIN

-- manipulate the data as you want... 

INSERT INTO AnotherTable VALUES (@Tthing1,@thing2)
	FETCH TBL INTO @thing, @thing2
END
CLOSE TBL
DEALLOCATE TBL

----------------
Have a good day!

Edited by - Amethystium on 06/25/2003 06:31:52

---

quote:

---

Excellent, thanks mr_mist, ha I feel rather stupid now. That works perfectly.

Out of curiosity, how would this be done as a loop? In situations wher e it wasn't a simple insert you wanted to perform, looping would be the only option.

---