Copy table row by row

Question

I am doing a trigger performance optimization and I want to test it. I have the actual trigger and the modified trigger, and I want the real data to run the 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 contains about 60 columns and 4,000 rows, so my trigger will run 4,000 times and I can use it to test performance.

I read about cursors, but I can’t understand how to use the cursor and the variable to copy line by line (do not select in a_bck or paste in a_bck a choice from which both generate only one insert).

My procedure for copying strings, as of 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

, . - varchar. - , ?

Answer 1

, , ...

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

Answer 2

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

.

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

WHILE-,

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

Answer 3

. , :

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

, , .