Feb 6, 2017 12:53:13 PM by prashanthjayaram
There was a question from the op regarding adding a new column to a query output by generating the cyclic sequence numbers from 1 to 3.
Select A =identity(int,1,1),B,C from table_abs
For this output, the 4th column generates the Sequence of numbers from 1 to 3 which is shown below
If you are using SQL 2012 then Sequence would be natural choice for any such operations.
START WITH 1
INCREMENT BY 1
SELECT table_name,NEXT VALUE FOR Seq New_column
Using CTE and Modulus operator
;with q as
select row_number() over (order by (select null)) A, *
select A, 1+A%3 B, *
Loops and Temp table
create table dummyTest
insert into dummyTest values(1,'A',410),(2,'B',411),(3,'c',4111),(4,'d',421),(5,'e',441),(6,'f',451),(7,'g',481),(8,'h',401)
create table #dummy
declare @n int,@i int,@limit int
select @n=count(*) from dummyTest
insert into #dummy
select *,NewColumn=@limit from dummyTest where id=@i
select * from #dummy
The same solution can be derived using the cursor and there may be other solutions as well. At many instances, we opt for any solutions without thinking of data volume that may degrade the performance. This is one of the prime examples of why we need to upgrade to newer version.
Tags: SQL Server
Written by prashanthjayaram
I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell.