How-to: Copy records via single record inserts, instead of using a bulk insert statement
This guide will show you how to copy a set of data from one database in a better way than doing it by using an ordinary INSERT SELECT approach.
You will see that instead of using a statement like this one below, there is a better method:
INSERT
INTO
MovementType
(
FromPlaceId
,
ReasonTypeId
,
ToPlaceId
,
Logging
,
OperationTypeId
)
SELECT
P
.
ID
AS
FromPlaceId
,
23
AS
ReasonTypeId
,
P
.
ID
AS
ToPlaceId
,
1
AS
Logging
,
164
AS
OperationTypeId
FROM
Place P
WHERE
P
.
AreaId
=
4
AND
NOT
EXISTS
(
SELECT
*
FROM
MovementType M
WHERE
M
.
FromPlaceId
=
P
.
ID
AND
M
.
ToPlaceId
= P
.
ID
)
The statement selects data for insertion and inserts the date in the same one go.
The problem is we do not have any control over it. Also if many records are inserted, it is done within the same one transaction, which might grow too huge to be fast and manageable for the database server. It might even time out.
The strategy lined up below does not have these problems built in.
Do:
1. Enter connection information for the target database.
|
|
For the example we will connect to a SQL Server database named "SQLMergerExamples". See SQL for creating the example in the end of this page.
|
2. Open the project using the menu "Project", "Refresh/Populate Project" item.
|
|
This action will both open the database and populate the project with the tables of the database(s).
|
Below you will see the results for the project:
|
|
The project with new merge items automatically added by the "Refresh/Populate Project" operation.
|
3. Add an additional merge item.
Select the menu "Item", then invoke "Add New Merge Item".
This will bring up the "Save Merge Item as" window.
Enter a name and click the [OK] button.
|
|
The extra merge item is now visible in the project.
|
4. Configure the Merge Item.
Enter "Target Table Name" as "MovementType" (the name of the table) and "Auto Incr. Field" to "ID" (The primary auto-generated field of the table).
Let SQLMerger know that it should use the database specified for Target, also for Source. This is done by putting in a check in the ""Use Target DB for Source" checkbox, in the project, for the Merge Item.
Enter the SELECT part of the query into the source:
SELECT
P.
ID AS
FromPlaceId,
23 as
ReasonTypeId,
P.
ID AS
ToPlaceId,
1 AS
Logging,
164 AS
OperationTypeId
FROM
Place P
WHERE
P.
AreaId =
4
AND
NOT
EXISTS
(
SELECT
*
FROM
MovementType M
WHERE
M.
FromPlaceId =
P.
ID AND
M.
ToPlaceId =
P.
ID
)
And the INSERT part, slightly transformed, into the Target
query:
SELECT
FromPlaceId,
ReasonTypeId,
ToPlaceId,
Logging,
OperationTypeId
FROM
MovementType
|
|
The Merge Item configured
|
5. Open the Merge item data.
This can be done by clicking the [Open Source] and [Open Target] buttons.
6. Add the source records
This is done via the "Actions" menu."Copy 'Source' only' records to Target".
|
|
Invoking the action to copy source records to the target
|
Result:
|
|
New records to be added.
|
7. Script the changes.
Simply click the "Script" tab and it is automatically generated.
8. Execute the changes
9. Go back to the "Merge" tab and see the final results.
|
|
Final results, 2 records have been copied.
|
Finished
SQL for adding the tables and data for the example:
CREATE
TABLE
Area(
ID INTEGER
PRIMARY
KEY
,
AreaName VARCHAR
(
25)
NOT
NULL
);
CREATE
TABLE
Place
(
ID
INTEGER
PRIMARY
KEY
,
AreaId
INTEGER
NOT
NULL
REFERENCES
Area
(
ID
),
PlaceName
VARCHAR
(
25
NOT
NULL
);
CREATE
TABLE
ReasonType(
ID
PRIMARY
KEY
,
ReasonTypeName VARCHAR
(
25)
NOT
NULL
);
CREATE
TABLE
OperationType(
ID INTEGER
PRIMARY
KEY
,
OperationTypeName VARCHAR
(
25)
NOT
NULL
);
CREATE
TABLE
MovementType(
ID INTEGER
PRIMARY
KEY
IDENTITY
,
FromPlaceId INTEGER
NOT
NULL
REFERENCES
Place(
ID),
ReasonTypeId INTEGER
NOT
NULL
REFERENCES
ReasonType(
ID),
ToPlaceId INTEGER
NOT
NULL
REFERENCES
Place(
ID),
Logging BIT
NULL
DEFAULT
0,
OperationTypeId INTEGER
NULL
REFERENCES
OperationType(
ID)
);
INSERT
INTO
Area(
ID,
AreaName)
VALUES
(
4 /* ID */
,
'A better place'
/* AreaName */
);
INSERT
INTO
Place(
ID,
AreaId,
PlaceName)
VALUES
(
1 /* ID */
,
4 /* AreaId */
,
'A'
/* PlaceName */
);
INSERT
INTO
Place(
ID,
AreaId,
PlaceName)
VALUES
(
2 /* ID */
,
4 /* AreaId */
,
'B'
/* PlaceName */
);
INSERT
INTO
Place(
ID,
AreaId,
PlaceName)
VALUES
(
3 /* ID */
,
4 /* AreaId */
'C'
/* PlaceName */
);
INSERT
INTO
ReasonType(
ID ReasonName)
VALUES
(
23 /* ID */
,
'The main reason'
/* ReasonName */
);
INSERT
INTO
OperationType(
ID,
OperationTypeName)
VALUES
(
164 /* ID */
,
'visit'
);
INSERT
INTO
MovementType(
FromPlaceId,
ReasonTypeId ToPlaceId,
Logging,
OperationTypeId)
VALUES
(
1 /* FromPlaceId */
,
23 /* ReasonTypeId */
,
1 /* ToPlaceId */
,
1 /* Logging */
,
164 /* OperationTypeId */
);
>
Back to Overview...
|
