Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Copy results, update a few fields, reinsert Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:22 AM
Points: 166, Visits: 387
I'm trying to find an easier way of doing the following: We have a batch file that processes records into two tables, position, and position_detail. I'm trying to make a copy of the records entered, giving them new keys, update_date, and update_source columns. I've got it working using cursors with a few columns, but I need to get all of the columns for each table and one of the tables has almost 100 columns, so the cursor seemed to be getting huge.

The added hitch is that the tables don't have autoincrementing for their pks, they have a stored proc they call to get the new id, and it gets recorded in another table.

Here's the stored proc I have written that works pretty well, it's just going to get super unwieldy when I explode the cursor out to 100 fields.

*had to take out the stored proc for work*
Post #1352185
Posted Thursday, August 30, 2012 8:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 2,057, Visits: 3,575
Hi Matthew,
I'm guessing that the proc pace_master.dbo.GetUpLdNextInstance only works on one row at a time?
Unless that can be changed you're stuck with the cursor, or a while loop, which won't really be any better.
Post #1352245
Posted Thursday, August 30, 2012 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:22 AM
Points: 166, Visits: 387
Yeah, pretty much.

I'm wondering if I can take the code in the stored proc and put it in a function that I can call?

select a,b,fn(getNewIndex),d, e, f, getDate()

plausible?

Just seems there should be a way to copy the whole table and just replace a column.
Post #1352273
Posted Thursday, August 30, 2012 9:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 3,905, Visits: 8,842
I'm sure there's a way to avoid the SPs to obtain the Next IDs, but here's a possible suggestion.
I can't assure it will work, but it can give you an idea.

--Removed

EDIT: There was an unneeded SELECT



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1352309
Posted Thursday, August 30, 2012 9:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 2,057, Visits: 3,575
Like the idea Luis. Far as I can tell, it assumes the newly generated Id's are sequential?
Post #1352338
Posted Thursday, August 30, 2012 10:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 3,905, Visits: 8,842
Yes, that's why I wanted to know what GetUpLdNextInstance is doing.
I just noticed that I erased the first one on my code by accident. I'm putting it back on now.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1352341
Posted Thursday, August 30, 2012 10:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:22 AM
Points: 166, Visits: 387
I'm about to jump into a meeting, but I'll give it a shot when I get out.

Essentially, you just got rid of the cursors, right?
Post #1352347
Posted Thursday, August 30, 2012 10:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 3,905, Visits: 8,842
That's correct.
It should perform much better, assuming the results are correct.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1352348
Posted Thursday, August 30, 2012 11:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:22 AM
Points: 166, Visits: 387
Going to give it a try now, but I'm unsure what this does:

ROW_NUMBER() OVER( ORDER BY position_id) + @new_position_id - 1
Post #1352403
Posted Thursday, August 30, 2012 12:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:22 AM
Points: 166, Visits: 387
I tried the code, but it's not giving each row it's own new id, it's giving all of the rows the same new id, and inserting them into the temp table, then throwing an error when I try to insert because it's a unique column. See attached.
Post #1352414
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse