January 20, 2009 at 2:22 pm
Hello,
[font="Courier New"]
if object_id('tempdb..#tmp1') is not null
drop table #tmp1
create table #tmp1 (someName varchar(10), someNumber int)
insert into #tmp1 select 'Name1', 0
insert into #tmp1 select 'Name2', 1
insert into #tmp1 select 'Name3', 2
insert into #tmp1 select 'Name4', 5
insert into #tmp1 select 'Name5', 12
if object_id('tempdb..#tmp2') is not null
drop table #tmp2
create table #tmp2 (someName varchar(10), someNumber int)
insert into #tmp2 select 'Name6', -1
insert into #tmp2 select 'Name7', -1
insert into #tmp2 select 'Name8', -1
insert into #tmp2 select 'Name9', -1
insert into #tmp2 select 'Name10', -1
[/font]
I have to insert data from #tmp2 to #tmp1 so that someNumber is never the same. So after correct insert from #tmp1 to #tmp2 table #tmp2 should look like this:
[font="Courier New"]
Name10
Name21
Name32
Name45
Name512
Name63
Name74
Name86
Name97
Name108
[/font]
I could do this with cursor through #tmp2 and a function, which searches for right someNumber but I guess it could be done easier?
Thanks
January 21, 2009 at 6:08 am
Are you trying to insert into the first temp table filling the gaps?
Because if you're simply trying to insert unique numbers, you can get the max value from the original table and insert all the values going up from there. No cursor required.
If it's gaps, while that's more difficult, cursors are still not required. Here's an example from Jeff Moden that does that job of identifying gaps. Once identified you can modify it to do the inserts:
SELECT GapStart = (SELECT ISNULL(MAX(b.ID),0)+1
FROM yourtable b
WHERE b.ID < a.ID),
GapEnd = ID - 1
FROM yourtable a
WHERE a.ID - 1 NOT IN (SELECT ID FROM yourtable)
AND a.ID - 1 > 0
You can also do a search using the box in the upper right part of the screen to find other methods, but Jeff usually has some of the most efficient solutions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2009 at 1:15 am
Thanks, very nice solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply