Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Undo a range to insert new record
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

183 Posts

Posted - 09/25/2013 :  22:54:25  Show Profile  Reply with Quote
Hi all,

I have a table with range of numbers. As an example like the following.

abba| 428|2000|2045|

Let's say I want to create a new range (2025 to 2030) of numbers existing in the range above in table.

The results I wish to have after the update would be as follow:
abba| 428|2000|2024|
deef| 428|2025|2030|
abba| 428|2031|2045|

Any suggestion?

In (Som, Ni, Yak)

17689 Posts

Posted - 09/25/2013 :  23:36:56  Show Profile  Reply with Quote
declare	@table table
	col1	varchar(5),
	col2	int,
	col3	int,
	col4	int

declare	@col1	varchar(5)	= 'deef',
	@col2	int		= 428,
	@col3	int		= 2025,
	@col4	int		= 2030

insert into @table select 'abba', 428, 2000, 2045

select	*
from	@table
col1  col2        col3        col4        
----- ----------- ----------- ----------- 
abba  428         2000        2045

update	t
set	col4	= @col3 - 1
output	deleted.col1, deleted.col2, @col4 + 1, deleted.col4
into	@table (col1, col2, col3, col4)
from	@table t
where	col3	<= @col3
and	col4	>= @col4

insert into @table select @col1, @col2, @col3, @col4

select	*
from	@table
order by col3
col1  col2        col3        col4        
----- ----------- ----------- ----------- 
abba  428         2000        2024
deef  428         2025        2030
abba  428         2031        2045

Time is always against us

Go to Top of Page

Posting Yak Master

183 Posts

Posted - 09/25/2013 :  23:46:29  Show Profile  Reply with Quote
Hi khtan,

This is it...
I only have to modify the following lines ..
declare @col1 varchar(5)
declare @col2 int
declare @col3 int
declare @col4 int

SET @col1 = 'deef'
SET @col2 = 428
SET @col3 = 2025
SET @col4 = 2030

Thanks a lot!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000