Copy results, update a few fields, reinsert

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Copy results, update a few fields, reinsert

16 posts, Page 1 of 2

1 2 »»

Copy results, update a few fields, reinsert

Rate Topic

Display Mode

Topic Options

Author

Message

Matthew Cushing

Posted Thursday, August 30, 2012 7:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:02 AM
Points: 162, Visits: 376

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

Gazareth

Posted Thursday, August 30, 2012 8:03 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

Matthew Cushing

Posted Thursday, August 30, 2012 8:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:02 AM
Points: 162, Visits: 376

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

Luis Cazares

Posted Thursday, August 30, 2012 9:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1352309

Gazareth

Posted Thursday, August 30, 2012 9:56 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Like the idea Luis. Far as I can tell, it assumes the newly generated Id's are sequential?

Post #1352338

Luis Cazares

Posted Thursday, August 30, 2012 10:01 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1352341

Matthew Cushing

Posted Thursday, August 30, 2012 10:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:02 AM
Points: 162, Visits: 376

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

Luis Cazares

Posted Thursday, August 30, 2012 10:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205

That's correct.
It should perform much better, assuming the results are correct.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1352348

Matthew Cushing

Posted Thursday, August 30, 2012 11:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:02 AM
Points: 162, Visits: 376

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

Matthew Cushing

Posted Thursday, August 30, 2012 12:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:02 AM
Points: 162, Visits: 376

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 »

16 posts, Page 1 of 2

1 2 »»

Permissions