Insert sets of IDs and Years into a table

Question

Insert sets of IDs and Years into a table

chrisjgreen08

SSC Rookie

Points: 28
More actions
January 12, 2018 at 1:55 pm

#402833

This is probably rather simple, but I'm having a brain stumble today.
Any thoughts on a SQL query to insert a set of IDs and Year(s) into a table? i.e.
ID   Year
123  2017
123  2018
373  2017
373  2018
of course this needs to be dynamic for the years.
I can obviously use one of these two methods for getting the years, but what about including the IDs as well?
           with yearlist as
        (
            select 2017 as year
            union all
            select yl.year + 1 as year
            from yearlist yl
            where yl.year + 1 <= YEAR(GetDate())
        )
        select year from yearlist order by year desc;
SELECT Yr
FROM (
SELECT TOP 100 2016 + ROW_NUMBER() OVER (ORDER BY ID) AS Yr
FROM Name
) Years
WHERE Yr <= YEAR(GETDATE())

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

chrisjgreen08 - Friday, January 12, 2018 1:55 PM
This is probably rather simple, but I'm having a brain stumble today.
Any thoughts on a SQL query to insert a set of IDs and Year(s) into a table? i.e.
ID   Year
123  2017
123  2018
373  2017
373  2018
of course this needs to be dynamic for the years.
I can obviously use one of these two methods for getting the years, but what about including the IDs as well?
           with yearlist as
        (
            select 2017 as year
            union all
            select yl.year + 1 as year
            from yearlist yl
            where yl.year + 1 <= YEAR(GetDate())
        )
        select year from yearlist order by year desc;
SELECT Yr
FROM (
SELECT TOP 100 2016 + ROW_NUMBER() OVER (ORDER BY ID) AS Yr
FROM Name
) Years
WHERE Yr <= YEAR(GETDATE())

What does the source data look like? Where does 'ID' come from?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

chrisjgreen08 SSC Rookie Points: 28 More actions · Answer 2

ID comes from a table called 'Name'. It's a unique VARCHAR field.

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

chrisjgreen08 - Friday, January 12, 2018 2:26 PM
ID comes from a table called 'Name'. It's a unique VARCHAR field.

OK, and does this table also contain years?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

chrisjgreen08 SSC Rookie Points: 28 More actions · Answer 4

chrisjgreen08

SSC Rookie

Points: 28

January 12, 2018 at 2:35 pm

#1975794

It does not.

Phil Parkin SSC Guru Points: 246693 More actions · Answer 5

So how do you determine which years are required for each ID?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 6

What you need/want is a CROSS JOIN. That will match every ID with every year:

SELECT n.ID, y.year FROM dbo.Name n CROSS JOIN ( SELECT ... AS year FROM <query_to_determine_year(s)> ) AS y

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.