I have a "default" set of values in table XZY
Field A = 'DEFAULT'
Field B = 'Other
Field C....E .. whatever
the Primary key is A & B
There are 10 or more records with FIELD A as 'DEFAULT"
In a stored proceedure, I want to create a new of records
from 'DEFAULT' and change that to 'NEWVAL' as example
In basic script it would look something like this:
Rs.Open Select * From XYZ Where FieldA = 'DEFAULT'
RX.add
RX.FieldA='NEWVAL'
RX... all other fields the same
Rx.Update
'============
So How would I do this in Stored Procedure, or Can I ?
Was thinking some temp table, but I get lost on the Add or the insert
Ok, thanks for the suggestion....
Hate 2 be so dense... but then How do I insert / add the rows (based on selection criteria of FieldA='DEFAULT' back into XZY table with a FieldA ='NEWVAL'
'---------
I want to add actual records BACK to into the XZY table with FieldA='NEWVAL'
Can I do that when the same stored procedure ?
I need more information - you stated the table has 10 rows currently with DEFAULT as the value. You also stated that 2 columns are utilized as the key values - that is both FieldA and FieldB.
What is the end result that you want? Do you want 10 new rows with NEWVAL and the same values in FieldB? Or something else...
If you want to duplicate the 10 rows - this is all you need to do:
Begin Transaction
Insert Into dbo.XZY (FieldA, FieldB, FieldC, FieldD, FieldE)
Select FieldA = 'NEWVAL'
, x.FieldB
, x.FieldC
, x.FieldD
, x.FieldE
From dbo.XZY x
Where x.FieldA = 'DEFAULT'
This will insert the results of the query into the table - with FieldA defined as NEWVAL and all of the other fields defined the same as the default rows.
At the end either a COMMIT (if everything went OK) or a ROLLBACK (if not!) is needed
I don't type that into the original command (or I comment it out) so I can run the command, see the number of rows that were inserted - maybe PANIC! - maybe do a SELECT to check that its all "as expected" - and THEN do the COMMIT or ROLLBACK. The table may well be locked (or if not then "partially locked") to other users during that time, so if you are doing this on a live system, with users connected and working concurrently, have all the commands you need ready-to-excute so you can be as quick as possible