SQLTeam.com | Weblogs | Forums

Inserting new recordset from another recordset in Stored Procedure


#1

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


#2

Don't use * in your query...list all of the columns individually and you can do this:

Select FieldA = 'NEWVAL'
     , x.FieldB
     , x.FieldC
     , x.FieldD
     , x.FieldE
  From dbo.XZY   x
 Where x.FieldA = 'DEFAULT'

Now you have your new set based on the default set and can adjust as needed.


#3

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 ?


#4

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.


#5

You are spot on... I wasn't getting the Insert portion. Thats exactly what I want.. 10 new rows, with FieldA = 'NewVal' ....

Thank you. Appreciate your time and help :slight_smile:


#6

I'm sure this was obvious, but just in case not!

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


#7

I meant to put the commit and rollback with comments but got distracted.


#8

I do that too ... but the screaming users down the hall normally brings me back to my senses :sunglasses:


#9

I will add the error check.
Never occurred to me that the table could get locked.
Thank you both.