Adding new rows with existing data using SQL

Question

I have the following table:

And all I want to do is add two new rows per PersonID i.e.

These are just small snip bits from the actual table, as this table contains large amount of data.

Any help would be greatly appreciated.

so essentially you want to duplicate each row based on personID? — Cârnăciov
– Cârnăciov, Commented Nov 29, 2016 at 12:25
It is completely unlear what you're asking about. Please describe what are the rules which define adding rows (by the way in question you wrote columns) to table. — Kacper
– Kacper, Commented Nov 29, 2016 at 12:27
Please also tag your question to make clear what DBMS you are using. — Sefe
– Sefe, Commented Nov 29, 2016 at 12:30
Any help would be greatly appreciated. ... Any explanation about the rules for adding data would also be appreciated. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 29, 2016 at 12:35

Tim Biegeleisen · Accepted Answer · 2016-11-29 12:38:46Z

1

Use INSERT?

INSERT INTO yourTable (ID, PersonID, JobID, Verified)
SELECT NULL, PersonID, MAX(JobID) + 1, 0
FROM yourTable
GROUP BY PersonID
UNION ALL
SELECT NULL, PersonID, MAX(JobID) + 2, 0
FROM yourTable
GROUP BY PersonID

Explanation:

Inserting NULL for the ID column should force the database to assign the next sequence, assuming that ID be an autoincrement column. If it is not, then maybe consider changing this. For each of the new records, the JobID sequence is just continued, hence my use of n+1 and n+2. And you seem to be using zero as the default value of Verified, so this was hard coded.

edited Nov 29, 2016 at 12:38

answered Nov 29, 2016 at 12:32

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hungryhorse · Accepted Answer · 2016-11-29 14:19:01Z

0

Thanks for everyone's replies/comments.

I managed to resolve my question by using a CURSOR (see below). It's not brilliant but it does the job.

answered Nov 29, 2016 at 14:19

hungryhorse

454 bronze badges

Collectives™ on Stack Overflow

Adding new rows with existing data using SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related