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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40455 Visits: 19808
Matthew Cushing (8/30/2012)
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

That's taking the new position_id and adding the Row Number minus 1 to avoid a gap between sets.

I'm guessing your SP GetUpLdNextInstance is doing something like this:

SELECT ISNULL( MAX( idcolumn), 0) + @Increment
FROM IDsTable
WHERE Table = @Table
AND Column = @Column


Am I correct?

Are you sure the new_position_id is getting the same value on every row? Can you check what is it trying to insert?


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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40455 Visits: 19808
Please, review the code I sent as there are some errors I didn't see, mainly because I can't test the code. I hope you get the idea of what it's doing, so you can modify it and mantain it.
For the repeating new id, could you please post the code you used?


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
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 442
Okay, I think I get it now. I moved things around so I'm using the Row_number() + @new_position_id to insert into the new_position_id field in the temp table, and it's incrementing beautifully. As an example, when I run it the first time, the new position id I'm getting is 920781 and the last one is 920813.

The problem is, if I run it a second time, the first new position_id value it comes up with is 920782. I believe that the code that generates the new id's writes the id to a table so it knows what number to start with, so in our case, it's only generating one value.

I'm wondering if it's as easy as just updating that table with the final value that I generate with your code.

So:
run stored proc to get new position id
walk through the code to increment the code
use the last position id to run an insert statement to the table we're keeping the max value in
end
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40455 Visits: 19808
I believe there's a problem with your insert trigger. It might be prepared for single row inserts. You might want to check it as it can be a general problem with your database.
In a company where I worked we had something like that, however is was meant for something different.
I hope that the table is not being used by several users at a time, because it will create a problem with duplicates.


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
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 442
I believe I got things working, but only partially. If I run it for today, it's fine. If I want to run it for a day last week or last month, it's checking the max value in the cursor, so it's not going to work.

Essentially I either have to write two stored procs or rewrite this somehow. The problem comes in when I have a weekend or a holiday. Since I'm using the max value, that part is fine, as long as I am running today. If I go back and try to run a different day, the values it grabs won't be right, since it's a max function, it'll grab todays.

I'm thinking of somehow putting in a conditional statement for the cursor select, or something along those lines. This seems like it should be so easy, but it really isn't. I'm just trying to copy two tables, give them new sequential id's, and insert the values with some updates along with them.

*had to take out the stored proc.*
Matthew Cushing
Matthew Cushing
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 442
Luis,

Can you remove the stored proc in your post? I just got called in because I wasn't supposed to post it.

M@
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