How to copy & insert records in table by strored procedure?

Question

I have a table with one field: lngStatusID with value 2 for all the records.

Now I need to insert all the records again in the same table but with lngStatusID=1

So for that I think stored procedure will help me somehow.

AS per my logic it should be something like:

1) I need to read each record with loop

2) copy all fields in temporary variables

3) And than execute insert query to insert the record with lngStatusID=1

I am new to stored procedure. So can any one guide me how to do that?

Or is there any easy way to do so?

Answer 1

You don't need a stored procedure for this, a simple INSERT statement will do:

insert into mytable
(field1, field2, lngStatusID)
select field1, field2, 1
from mytable

Answer 2

Why do you need to insert them again? Maybe simple UPDATE will be enough?

UPDATE table SET IngStatusID = 1

Please provide more details, because for me it is pointless to copy all the records to temporary table to insert them again.

Answer 3

UPDATE <tablename>
SET IngStatusID = 1

That being said, any table with only one field probably shouldn't be a table unless its some kind of lookup.

Answer 4

INSERT INTO <TABLENAME> (Col1,Col2,Col3)
SELECT Col1, Col2, 2
FROM <TABLENAME>

No need for sp or cursors