CODEWITHSUNDEEP
sql
kpschwert
kpschwert

Reputation: 119

How to copy rows in SQL and add new data to each row

I have a SQL table named ItemReviewedStatuses like the following:

ItemReviewedStatus Tabel

I am needing a query that will replicate each row 4 times but add a new AuditReleaseId to each row. So for the first row, I am needing to add 3 new rows that look identical to it (so there are a total of 4 rows) but in the AuditReleaseId add value 20181 to 1st row, 20182 to 2nd row, 20183 to 3rd row, and 20184 to 4th row. Then I am to do the exact same to all the rest of the rows with the same sequence of AuditReleaseIds for each row.

Upvotes: 1

Views: 187

Answers (3)

Bryan Dellinger
Bryan Dellinger

Reputation: 5294

since you did not provide a rdbms here is a solution in oracle. using connect by level

http://sqlfiddle.com/#!4/96715d/19

create table mytable as select 'A' as col1, 'A2' as col2 from dual union all
select 'B1', 'B2' from dual union all
select 'C1', 'C2' from dual



   with t1 as (select level + 20180 as AuditReleaseId from dual connect by level < 5) 
select * from mytable, t1 
order by col1, col2, AuditReleaseId

Upvotes: 0

wizzwizz4
wizzwizz4

Reputation: 6426

SQL is a relational database. I'm 80% sure you're asking for the wrong thing, here. You want to attach four AuditReleaseIds to each status, right? What you should do is add another table with two columns: your table's primary key (I assume that's ItemStatusId) and your AuditReleaseId. Then, each time you want to attach an audit to an item status, you just add another row in that table.

If this is a one to many relationship, as opposed to a many to many, you should just add an ItemStatusId column to your audit table.

This answer is useless if you wish for audits to modify a copy of the data, but otherwise you should avoid denormalising data if you aren't performing a profiled optimisation.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do something like this:

insert into ItemReviewedStatuses (AuditReleaseId, . . .)  -- . . . are the othercolumns you want
    select ar.AuditReleaseId, . . . 
    from ItemReviewedStatuses irs cross join
         (select 20181 as AuditReleaseId union all
          select 20182 as AuditReleaseId union all
          select 20183 as AuditReleaseId union all
          select 20184 as AuditReleaseId 
         ) ar;

Note that the syntax for generating the table of constants varies depending on the database, but this works in many databases (and something similar works in others).

Upvotes: 2

Related Questions