SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Copy results, update a few fields, reinsert


Copy results, update a few fields, reinsert

Author
Message
Matthew Cushing
Matthew Cushing
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 429
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*
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4701 Visits: 5913
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.
Matthew Cushing
Matthew Cushing
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 429
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.
Luis Cazares
Luis Cazares
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22188 Visits: 19354
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4701 Visits: 5913
Like the idea Luis. Far as I can tell, it assumes the newly generated Id's are sequential?
Luis Cazares
Luis Cazares
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22188 Visits: 19354
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Matthew Cushing
Matthew Cushing
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 429
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?
Luis Cazares
Luis Cazares
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22188 Visits: 19354
That's correct.
It should perform much better, assuming the results are correct.


Luis C.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Matthew Cushing
Matthew Cushing
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 429
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
Matthew Cushing
Matthew Cushing
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 429
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search