How to add numeric values to an integer field in SQL

The table I am managing has the following fields:

YearId int,
Year int

YearId is the primary key and Year is the real value that is holding the value of a numeric value.  So basically I wanted to start from 1900 and make the Year column go all the way to 2015.  How would I do that using only SQL?
VBBRettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
declare @i int
set @i = 1900

while @i <=2015
 begin
   insert into <table> (year) @i
   set @i = @i + 1
 end

I'm assuming you have an identity on yearid as well.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony303Commented:
If the Id field isn't an identity, then we can add it to Ged's code above...
NOTE, I made a table called "Years"


declare @yr int
declare @id int
set @yr = 1900
set @id = 1

while @Yr <=2015 AND @id >= 1
 begin
   insert into years (id ,year) Values (@id ,@yr)
   set @yr = @yr + 1
   SET @id = @id + 1
   
 end
0
David ToddSenior DBACommented:
Hi,

There isn't any reason to loop. Look up all the uses for a Tally table or Numbers table.

Okay, a little more work to set it up, but if the number of inserts is substantial, what's illustrated here is a lot quicker than a loop.

--
use ExpertsExchange
go

create table dbo.Number(    
	n int constraint pk_Number primary key clustered 
	)
;

if object_id( N'tempdb..#digit', N'U' ) is not null 
	drop table #digit;
	
create table #digit(
	i int
	)

insert #digit( i ) values ( 0 ), ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ), ( 6 ), ( 7 ), ( 8 ), ( 9 )
	
insert dbo.Number( n )
	select
		(( h.i * 10 ) + t.i ) * 10 + o.i
	from #digit o
	cross join #digit t
	cross join #digit h
;

--insert dbo.YourTableName( _year )
select 
	1900 + N.n as _year
from dbo.Number N
where
	1900 + N.n <= 2015
;

Open in new window


HTH
  David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.