InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 18:35:55 GMT20http://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxLuis,Can you remove the stored proc in your post? I just got called in because I wasn't supposed to post it.M@Thu, 27 Sep 2012 15:04:50 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI 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.*Mon, 24 Sep 2012 09:32:36 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI 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.Thu, 30 Aug 2012 13:39:25 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxOkay, 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 idwalk through the code to increment the codeuse the last position id to run an insert statement to the table we're keeping the max value inendThu, 30 Aug 2012 13:02:18 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxPlease, 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?Thu, 30 Aug 2012 12:52:16 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspx[quote][b]Matthew Cushing (8/30/2012)[/b][hr]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[/quote]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:[code="sql"]SELECT ISNULL( MAX( idcolumn), 0) + @IncrementFROM IDsTableWHERE Table = @TableAND Column = @Column[/code]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?Thu, 30 Aug 2012 12:42:01 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI 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.Thu, 30 Aug 2012 12:22:24 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxGoing to give it a try now, but I'm unsure what this does:ROW_NUMBER() OVER( ORDER BY position_id) + @new_position_id - 1Thu, 30 Aug 2012 11:57:43 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxThat's correct.It should perform much better, assuming the results are correct.Thu, 30 Aug 2012 10:08:14 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI'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?Thu, 30 Aug 2012 10:05:40 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxYes, 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.Thu, 30 Aug 2012 10:01:42 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxLike the idea Luis. Far as I can tell, it assumes the newly generated Id's are sequential?Thu, 30 Aug 2012 09:56:12 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI'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.[code="sql"]--Removed[/code]EDIT: There was an unneeded SELECTThu, 30 Aug 2012 09:11:35 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxYeah, 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.Thu, 30 Aug 2012 08:37:02 GMTMatthew Cushinghttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxHi 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.Thu, 30 Aug 2012 08:03:48 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1352185-391-1.aspxI'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*Thu, 30 Aug 2012 07:14:24 GMTMatthew Cushing