Copy table row by row

I'm doing some trigger performance optimizations and i want to test it. I have a actual trigger and a modified trigger and i want on real data run a test on the old trigger and the new trigger and compare it. I want to copy table A to A_BCK row by row. Table A has about 60 columns and 4000 rows, so my trigger will be 4000 times executed and i can use it for performance test.

I've read about cursors, but cant figure out how use a cursor and variable for row by row copying (not select into a_bck or insert into a_bck select from a which both generate just one insert).

My row copying procedure as for now looks like this:

declare @actualrow varchar(15);

DECLARE eoauz CURSOR FAST_FORWARD FOR SELECT * FROM A

open eoauz
fetch next from eoauz into @actualrow
while @@fetch_status = 0
begin
 /* INSERT INTO A_BCK VALUES FROM @actualrow  */
fetch next from eoauz into @actualrow
end
close eoauz
deallocate eoauz

Of course this does not work. I need something like a row variable inste开发者_如何学JAVAad varchar. Can someone help how to accomplish my task?

I don't often work with cursors so there may be a more straightforward way I'm missing...

SELECT TOP 0 *
INTO   #t
FROM   master..spt_values /*Create an empty table of correct schema*/

DECLARE eoauz CURSOR FAST_FORWARD FOR
  SELECT *
  FROM   master..spt_values

OPEN eoauz

INSERT INTO #t
EXEC ('fetch next from eoauz')

WHILE @@FETCH_STATUS = 0
  INSERT INTO #t
  EXEC ('fetch next from eoauz')

CLOSE eoauz

DEALLOCATE eoauz

SELECT *
FROM   #t

DROP TABLE #t

Sample table for code below

create table A(ID INT IDENTITY, a int, b int)
create table B(ID INT, a int, b int)
insert A select 1,2 union all select 3,4 union all select 5,6

You need a variable for each column. See example below

declare @id int, @a int, @b int
DECLARE eoauz CURSOR FAST_FORWARD FOR SELECT * FROM A
open eoauz
fetch next from eoauz into @id, @a, @b
while @@fetch_status = 0
begin
 INSERT B VALUES( @id, @a, @b )
fetch next from eoauz into @id, @a, @b
end
close eoauz
deallocate eoauz

I prefer not to use cursors and instead use WHILE loops whenever possible

declare @id int
select top 1 @id = id from A order by ID
while @@ROWCOUNT > 0 begin
    insert B select * from A where ID=@id  -- one row
    select top 1 @id = id from A where id > @id order by ID
end

I would select your table's primary key into your cursor variable. This uniquely identifies a single row, which you can then easily select:

declare @pk varchar(15);

DECLARE eoauz CURSOR FAST_FORWARD FOR SELECT PK FROM A

open eoauz
fetch next from eoauz into pk
while @@fetch_status = 0
begin
INSERT INTO A_BCK select * from A where PK = @pk
fetch next from eoauz into pk
end
close eoauz
deallocate eoauz

I think this method has a big advantage in being easy to understand and read.