Click here to Skip to main content
15,938,218 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have a table with data ,In weeks columns Missing some sequence number(week).
In that place i want to add missing number.

for example:

Columns :  week  year platformname operatingsystem
            1    2018        sql         w7
            2    2018        sql         w7 
            5    2018        sql         w7
            6    2018        sql         w7

in this data missing number 3,4 .how to add this data in table.

Please help me out ASAP.

Thanks in advance.

What I have tried:

i tried sequence number but its comming only 3 (1st number only coming)
Updated 10-Jan-20 4:21am
OriginalGriff 10-Jan-20 4:23am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So show us the input data you have and the output you want, show us the code that generates the output above, and explain what is wrong with it.

And don't tell us you need it ASAP: everyone wants a quick answer and you are not our boss. So acting like one does not help you get a faster answer, it just makes you look arrogant ...
Use the "Improve question" widget to edit your question and provide better information.

Share this answer
Maciej Los 10-Jan-20 12:27pm    
[no name] 10-Jan-20 12:35pm    
Thank you Maciej. To be honest, at least one of the links I noted in the past from one of your answers ;)
Maciej Los 10-Jan-20 13:45pm    
The principle is that you need to select a contiguous list of numbers and then match them to the data you have in your table.

Here is your data
declare @demo table ([week] int, [year] int, platformname nvarchar(30), operatingsystem nvarchar(30))
insert into @demo ([week], [year], platformname, operatingsystem) values
(1, 2018, 'sql', 'w7'),(2, 2018, 'sql', 'w7'), 
(5, 2018, 'sql', 'w7'),(6, 2018, 'sql', 'w7')
As you say there is a gap where 3 and 4 should be.

Here is a neat and efficient way of generating all the numbers you want
select * from 
(select top (@top) row_number() over (order by  a.object_id) AS seqNum 
from sys.columns a) as nums
That query uses the system table sys.columns just because I know there is going to be some data in there, probably quite a lot depending on my database schema. In my case there are 1167 columns in my database, but if that isn't enough you can use a cross join to the same table to get even more numbers - 1361889 in my case i.e. 1167 x 1167

So I didn't end up with 1361883 empty rows in my results I've limited the number of numbers I want by using top (@top) where
declare @top int = (select max([week]) from @demo)
I could just as easily have hard-coded this to 10 for example.

Combine the two queries with a LEFT OUTER join using the numbers table as the left table and your data table as the right table i.e.
select nums.seqNum, [year], platformname, operatingsystem
  (select top (@top) row_number() over (order by  a.object_id) AS seqNum 
  from sys.columns a cross join sys.columns b)  as nums
left outer join @demo  on seqNum = [week]
which gives the results
seqNum	year	platformname	operatingsystem
1	2018	sql		w7
2	2018	sql		w7
5	2018	sql		w7
6	2018	sql		w7
You'll have to decide what to do with the NULL values
Share this answer
[no name] 10-Jan-20 11:04am    
Wow, great compact and easy explanation. +5
CHill60 10-Jan-20 11:13am    
Thank you! I'm quite embarrassed about the cross join - I had a similar query that worked for ages until the number of rows required exceeded the number of columns in my schema. It was around about then that I started insisting on a numbers table in our database :-)
Maciej Los 10-Jan-20 12:27pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900