CODEWITHSUNDEEP
sqlsql-server
DiamondJoe12
DiamondJoe12

Reputation: 1809

Primary key error in inserting rows into a view SQL

I'm trying to create a new view, based on an existing table. I want this new view to have inserted records.

Here's the original table, called population:

Agency    Year    Total   PopGroup
01        2017    3467    3C
01        2018    3444    3C
01        2019    3567    3C
02        2017    1000    1C
02        2018    1354    1C
02        2019    1333    1C
03        2017    6784    2C
03        2018    3453    2C
04        2017    3333    2C

If an agency has a row for year 2019, I want to duplicate this row and call it 2020 (basically, an estimate population for 2020). Here's the desired result for my view:

Agency    Year    Total   PopGroup
01        2017    3467    3C
01        2018    3444    3C
01        2019    3567    3C
01        2020    3567    3C
02        2017    1000    1C
02        2018    1354    1C
02        2019    1333    1C
02        2020    1333    1C
03        2017    6784    2C
03        2018    3453    2C
04        2017    3333    2C

This is my code to try and create the view:

Use MY_PROJECTS
Go

CREATE VIEW population_w2020Estimates AS
SELECT *
FROM [MY_PROJECTS].[dbo].[population]
WHERE Year in (2017,2018,2019)

GO

INSERT INTO population_w2020Estimates (Agency, Year, Total, PopGroup)
SELECT Agency, 2020 as Year, Total, PopGroup
FROM population_w2020Estimates

The view is created, but the insert fails, with the following error:

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object...

I'm using Microsoft SQL Server Management Studio.

Upvotes: 0

Views: 301

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

If you just want your view to show you an extra row for each agency as 2020 data derived from 2019 data then create the view as below:

CREATE VIEW population_w2020Estimates AS
SELECT *
FROM [population]
WHERE Year in (2017,2018,2019)
union all
SELECT Agency, 2020 as Year, Total, PopGroup
FROM population where year='2019'

Upvotes: 1

strickt01
strickt01

Reputation: 4048

You are creating a new row with 2020 for every current row in the population_w2020Estimates view. So for Agency 01 the SELECT will bring back three rows - 2017,2018,2019. Hence the primary key error. Try:

INSERT INTO population_w2020Estimates (Agency, Year, Total, PopGroup)
SELECT Agency, 2020 as Year, Total, PopGroup
FROM population_w2020Estimates
WHERE Year = 2019

Upvotes: 2

Related Questions