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

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

infodemers
Posting Yak Master

Canada
181 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.

col1|col2|col3|col4|
--------------------
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:
col1|col2|col3|col4|
--------------------
abba| 428|2000|2024|
deef| 428|2025|2030|
abba| 428|2031|2045|

Any suggestion?

khtan
In (Som, Ni, Yak)

Singapore
17587 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
*/



KH
Time is always against us

Go to Top of Page

infodemers
Posting Yak Master

Canada
181 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000