Undo a range to insert new record

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Undo a range to insert new record

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2013-09-25 : 22:54:25

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)

17689 Posts

Posted - 2013-09-25 : 23:36:56

[code]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
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

infodemers
Posting Yak Master

183 Posts

Posted - 2013-09-25 : 23:46:29

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources