Clone existing record and modify one field. cycle

Question

My question is the following :

SQL table with multiple records, multiple attributes.

Lets select it :

select * from jaikudo

returns:

(id, created, createdByWho,relatedToWho,worksWithWho )

The job to fulfil :

when I : select * from jaikudo where createdByWho = 1 returns 1 record.

I have worksWithWho table with approx 5000 records. The Id is PK, not necessearely ascending or descending but random numbers

When I : select * from worksWithWho

returns (id, created, createdByWho,)

I need to insert records into the jaikudo table based on the following

`select * from jaikudo where createdByWho = 1`

returns 1 record (id, created, createdByWho,relatedToWho,worksWithWho )

copy the details : (createdByWho,relatedToWho) created will pull the getdate () info and insert as the following :

Create new record in the jaikudo table for each worksWithWho data ( which is unique ) and fill the remaining fields ( createdbyWho and relatedToWho ) with the data copied from the following query : select * from jaikudo where createdByWho = 1

When it`s done, I have to have approx 5000 new record in the jaikudo table, but if its possible, some error detection would be good as IF the jaikudo table already having the exact same relation then skip the record and continue.

Thanks.

you can use a Number / Tally table like in this [question](http://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table) — ughai, Oct 19 '15 at 09:16
Thanks ughai, but unfortunately it isn`t helps to solve the issue :( — Yvanhoe, Oct 21 '15 at 12:54

score 0 · Answer 1 · answered Oct 19 '15 at 09:07

0

You can try this:

DECLARE @max_worksWithWho INT = 1
    WHILE @max_worksWithWho <= 5000
    BEGIN
     INSERT INTO jaikudo
     SELECT id, created, createdByWho, relatedToWho, @max_worksWithWho
     FROM jaikudo
     WHERE createdByWho = 1
       AND worksWithWho  NOT IN (SELECT worksWithWho FROM jaikudo WHERE createdByWho = 1)
    SET @max_worksWithWho = @max_worksWithWho + 1
    END

answered Oct 19 '15 at 09:07

Nguyễn Hải Triều

1,454
1
8
14

Hi Nguyễn Hải Triều ! Sorry to be pain, I haven't mentioned that worksWithWho data is complete random records. not simple numbers between 1 and 5000. It is possible to have a record with worksWithWho = 1252452. I meant to describe that it is around 5000 records... Thanks for the quick response. – Yvanhoe Oct 19 '15 at 09:33

ughai · Accepted Answer · 2015-10-23T05:28:45.843

0

Based on your updated question, you can JOIN with worksWithWho and do something like this.

INSERT INTO jaikudo(id, created, createdByWho,relatedToWho,worksWithWho)
SELECT
    CHECKSUM(NEWID()) as id,
    GETDATE() as created,
    j1.createdbyWho,
    j1.relatedToWho,
    w.worksWithWho
FROM worksWithWho w
INNER JOIN jaikudo j1
ON j1.createdByWho = 1
LEFT JOIN jaikudo j
ON j.worksWithWho = w.id
WHERE j.id IS NULL

INNER JOIN jaikudo j1 ON j1.createdByWho = 1 is added to get details for createdbyWho and relatedToWho
LEFT JOIN jaikudo j ON j.worksWithWho = w.id WHERE j.id IS NULL is added to add only those records where a record with same worksWithWho doesnt already exist

Note: use w.id or whichever columns stores the referencing id of worksWithWho in your worksWithWho table.

edited Oct 23 '15 at 05:28

answered Oct 21 '15 at 13:16

ughai

9,830
3
29
47

It is great, thank, but the main question is still remains the same : How I can bulk create the records onto the jaikudo table based on this ? This will just list the required , but not inserts :( – Yvanhoe Oct 22 '15 at 14:12
Its pretty straightforward, Just add `INSERT INTO jaikudo(id, created, createdByWho,relatedToWho,worksWithWho)` before the `SELECT` – ughai Oct 23 '15 at 05:27
Great stuff thanks. I was way too over complicating the whole thing. – Yvanhoe Oct 23 '15 at 10:06

Clone existing record and modify one field. cycle

2 Answers2