How to: Create new data as modified copies of an existing master/detail set data copy
The technique described below you can use for copying a complex set of data via SQLMerger.
It applies to the situations where you have one master records and a number of related detail records you want to copy as one whole. In the example it is a configuration for a screen list, with columns.
In short: You want to copy the records for a certain Id in the master record, to a new set of records with a new master Id.
Do:
1. Let’s construct an example with a “ScreenList” (master) and a “ScreenListColumn” (detail) table:
CREATE TABLE
ScreenList (
Id INT PRIMARY KEY
,
Name VARCHAR
(
50) NOT NULL
);
CREATE TABLE
ScreenListColumn (
Id INT PRIMARY KEY
,
ScreenListId INT
NOT NULL
REFERENCES
ScreenList(
Id),
Name VARCHAR
(
50) NOT NULL
);
A parameter table for controlling the copy task is also needed:
CREATE TABLE
Params (
CopyFromId INT
NOT NULL,
CopyToId INT
NOT NULL
);
And some data to work with:
INSERT INTO
ScreenList (
Id,
Name
)
VALUES
(1
/* Id */
,
'A'
/* Name */
);
INSERT INTO
ScreenList (
Id,
Name
)
VALUES
(
2 /* Id */
,
'B'
/* Name */
);
INSERT INTO
ScreenListColumn (
Id,
ScreenListId,
Name
)
VALUES
(
1 /* Id */
,
1 /* ScreenListId */
,
'a1'
/* Name */
);
INSERT INTO
ScreenListColumn (
Id,
ScreenListId,
Name
)
VALUES
(
2 /* Id */
,
1 /* ScreenListId */
,
'a2'
/* Name */
);
INSERT INTO
ScreenListColumn (
Id,
ScreenListId,
Name
)
VALUES
(
3 /* Id */
,
2 /* ScreenListId */
,
'b1'
/* Name */
);
2. Fill in the source and target databases connection settings with settings for the same database, so you will log into the same database for both source and target.
|
|
Initial project. The project is populated when opened or if you choose the "Refresh/Populate Project" option from the "Project" menu.
|
3. Add a merge item for the “Master”, with the following settings:
Source SQL:
SELECT
P.
CopyToId AS
Id, *
FROM
ScreenList SL,
Params P
WHERE
SL.
Id =
P.CopyFromId
Target SQL:
SELECT SL.
*
FROM
ScreenList SL,
Params P
WHERE
SL.Id =
P.
CopyToId
|
|
The first merge item has been added. The one for copying master records.
|
4. Now we want to use the "Params" table to specify what to copy.
I will copy the “ScreenList” with Id 1 to a new one with Id = 3.
INSERT
INTO
Params (CopyFromId,
CopyToId)
VALUES
(
1 /* CopyFromId */
,
3 /* CopyToId */
);
|
|
A quick look at the data in the controller table.
|
5. Open the merge item.
|
|
Here you can see the master records that are going to be copied
|
6. Then invoke: “Actions”, “Copy Source only records to target”.
|
|
A master record is copied into the target area
|
7. Select the “Script” tab
|
|
This SQL will be generated
|
8. Execute it and return to the “Merge” tab.
|
|
Finished copying the master records
|
Copying the Detail Data
9. Add a merge item for each detail table.
In our example we add one with the SQL:
Source:
SELECT
P.
CopyToId AS
ScreenListId,
NULL AS Id, *
FROM
ScreenListColumn C,
Params P
WHERE
C.
ScreenListId =
P.
CopyFromID
Notice we force a NULL value into the Id field, to be able to have SQLMerger generate new Id’s for the new data. To do this the “Handled Key Field Name” field in the merge item is filled in with “Id”.
Target:
SELECT
C.*
FROM
ScreenListColumn C,
Params P
WHERE
C.ScreenListId =
P.
CopyToId
10. Open the merge item and the screen will look like:
Set the Handled key field name to "Id".
11. Choose the “Copy Source only records to target” option from the “Actions” menu.
|
|
This will result in these lines.
|
12. When you click the “Script” tab this will be generated:
13. Execute the script and we're finished.
|
|
The result for the detail table when finished: The detail has been copied too and associated with the new master Id
|
Back to Overview...
|
