CODEWITHSUNDEEP
sql
hungryhorse
hungryhorse

Reputation: 45

Adding new rows with existing data using SQL

I have the following table:

Table1

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

Table 2

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

Any help would be greatly appreciated.

Upvotes: 0

Views: 95

Answers (2)

hungryhorse
hungryhorse

Reputation: 45

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.

enter image description here

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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.

Upvotes: 1

Related Questions