Professional Web Applications Themes

INSERT Statement Query - MySQL

Hi, I am about to embark on performing a data migration into a MySQL database. The goal is to be able to take the input data, and generate SQL statements to insert the data. The problem that I have, is that alot of the data is to be generated into parent-child relationships, where the unique parent key is an auto-increment field. This means that I do not know, when generating the SQL, what the parent ID is to put into the child record. Is it possible to 'look-up' the value within an INSERT statement? For example, something like: INSERT INTO ...

  1. #1

    Default INSERT Statement Query

    Hi,

    I am about to embark on performing a data migration into a MySQL database. The goal is to be able to take the input data, and generate SQL statements to insert the data.

    The problem that I have, is that alot of the data is to be generated into parent-child relationships, where the unique parent key is an auto-increment field. This means that I do not know, when generating the SQL, what the parent ID is to put into the child record.

    Is it possible to 'look-up' the value within an INSERT statement?

    For example, something like:

    INSERT INTO member
    (fund_id, member-code, member-name)
    VALUES (
    (
    SELECT fund.fund_id
    FROM fund
    WHERE fund.fund_code = "12345"
    ),
    "NEW-MEMBER",
    "Mr New Member");

    I tried the above, but it didn't work, so I thought I'd ask if what I am after is even possible before I attempt to solve it.

    Regards,

    --

    Murdoc Guest

  2. Moderated Post

    Default Re: INSERT Statement Query

    Removed by Administrator
    Giuseppe Maxia Guest
    Moderated Post

  3. Moderated Post

    Default Re: INSERT Statement Query

    Removed by Administrator
    Murdoc Guest
    Moderated Post

  4. #4

    Default Re: INSERT Statement Query

    Assuming that fund_id is an autonumber, you can write:

    INSERT INTO fund(...) VALUES(...);
    SET FundID=LAST_INSERT_ID();
    INSERT INTO member(fund_id, ...) VALUES(FundID, ...);

    etc. The variables remain valid as long as the connection to the server
    remains open.


    Murdoc wrote:
    > Hi,
    >
    > I am about to embark on performing a data migration into a MySQL database. The goal is to be able to take the input data, and generate SQL statements to insert the data.
    >
    > The problem that I have, is that alot of the data is to be generated into parent-child relationships, where the unique parent key is an auto-increment field. This means that I do not know, when generating the SQL, what the parent ID is to put into the child record.
    >
    > Is it possible to 'look-up' the value within an INSERT statement?
    >
    > For example, something like:
    >
    > INSERT INTO member
    > (fund_id, member-code, member-name)
    > VALUES (
    > (
    > SELECT fund.fund_id
    > FROM fund
    > WHERE fund.fund_code = "12345"
    > ),
    > "NEW-MEMBER",
    > "Mr New Member");
    >
    > I tried the above, but it didn't work, so I thought I'd ask if what I am after is even possible before I attempt to solve it.
    >
    > Regards,
    >
    Dikkie Dik Guest

  5. #5

    Default Re: INSERT Statement Query

    Dikkie Dik wrote:
    > Assuming that fund_id is an autonumber, you can write:
    >
    > INSERT INTO fund(...) VALUES(...);
    > SET FundID=LAST_INSERT_ID();
    > INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    >
    > etc. The variables remain valid as long as the connection to the server remains open.
    I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.

    Regards,
    Murdoc.


    --

    Murdoc Guest

  6. #6

    Default Re: INSERT Statement Query

    Murdoc wrote:
    > Dikkie Dik wrote:
    >
    >
    >>Assuming that fund_id is an autonumber, you can write:
    >>
    >>INSERT INTO fund(...) VALUES(...);
    >>SET FundID=LAST_INSERT_ID();
    >>INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    >>
    >>etc. The variables remain valid as long as the connection to the server remains open.
    >
    >
    > I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.
    >
    > Regards,
    > Murdoc.
    >
    >
    If you're going to use an autoincrement field and need the autoincrement
    value, you have to insert row at a time. This is true in MySQL and
    every other RDB I know of which has an autoincrement field.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: INSERT Statement Query

    Jerry Stuckle wrote:
    > Murdoc wrote:
    > > Dikkie Dik wrote:
    > >
    > >
    > > > Assuming that fund_id is an autonumber, you can write:
    > > >
    > > > INSERT INTO fund(...) VALUES(...);
    > > > SET FundID=LAST_INSERT_ID();
    > > > INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    > > >
    > > > etc. The variables remain valid as long as the connection to the server remains open.
    > >
    > >
    > > I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.
    > >
    > > Regards,
    > > Murdoc.
    > >
    > >
    >
    > If you're going to use an autoincrement field and need the autoincrement value, you have to insert row at a time. This is true in MySQL and every other RDB I know of which has an autoincrement field.
    And hence the reason that I am not going to use the LAST_INSERT_ID() method as suggested. The SQL I am generating will lookup the required autoincrement value that was created based on a separate unique index (not the auto-increment field).

    --

    Murdoc Guest

  8. #8

    Default Re: INSERT Statement Query

    Murdoc wrote:
    > Jerry Stuckle wrote:
    >
    >
    >>Murdoc wrote:
    >>
    >>>Dikkie Dik wrote:
    >>>
    >>>
    >>>
    >>>>Assuming that fund_id is an autonumber, you can write:
    >>>>
    >>>>INSERT INTO fund(...) VALUES(...);
    >>>>SET FundID=LAST_INSERT_ID();
    >>>>INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    >>>>
    >>>>etc. The variables remain valid as long as the connection to the server remains open.
    >>>
    >>>
    >>>I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.
    >>>
    >>>Regards,
    >>>Murdoc.
    >>>
    >>>
    >>
    >>If you're going to use an autoincrement field and need the autoincrement value, you have to insert row at a time. This is true in MySQL and every other RDB I know of which has an autoincrement field.
    >
    >
    > And hence the reason that I am not going to use the LAST_INSERT_ID() method as suggested. The SQL I am generating will lookup the required autoincrement value that was created based on a separate unique index (not the auto-increment field).
    >
    If you already have a unique index, why do you need an autoincrement field?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  9. #9

    Default Re: INSERT Statement Query

    Jerry Stuckle wrote:
    > Murdoc wrote:
    > > Jerry Stuckle wrote:
    > >
    > >
    > > > Murdoc wrote:
    > > >
    > > > > Dikkie Dik wrote:
    > > > >
    > > > >
    > > > >
    > > > > > Assuming that fund_id is an autonumber, you can write:
    > > > > >
    > > > > > INSERT INTO fund(...) VALUES(...);
    > > > > > SET FundID=LAST_INSERT_ID();
    > > > > > INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    > > > > >
    > > > > > etc. The variables remain valid as long as the connection to the server remains open.
    > > > >
    > > > >
    > > > > I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.
    > > > >
    > > > > Regards,
    > > > > Murdoc.
    > > > >
    > > > >
    > > >
    > > > If you're going to use an autoincrement field and need the autoincrement value, you have to insert row at a time. This is true in MySQL and every other RDB I know of which has an autoincrement field.
    > >
    > >
    > > And hence the reason that I am not going to use the LAST_INSERT_ID() method as suggested. The SQL I am generating will lookup the required autoincrement value that was created based on a separate unique index (not the auto-increment field).
    > >
    >
    > If you already have a unique index, why do you need an autoincrement field?
    Because the unique index that I have is a multiple-component index (in some cases up to 4 fields). I use the auto-increment field to build the relationships (and usually do not display this value to the user), but the other unique index is visible and identifiable to the user.

    For example:

    tax_packet
    fund_code VARCHAR(15) (U1)
    purchase_transaction_id INTEGER (U1)
    folio_value INTEGER (U1)
    tax_packet_id INTEGER (U2)

    In the above example, the user knows about the fund_code, purchase_transaction_id and folio_value combination, but does not know, and does not care about the tax_packet_id value (which is used to link to the related tables).

    --

    Murdoc Guest

  10. #10

    Default Re: INSERT Statement Query

    Then just change the SET command with a setting select:

    SELECT FundID:=fund_id FROM fund WHERE fund_code='...';

    (notice the := assignment)

    Murdoc wrote:
    > Jerry Stuckle wrote:
    >
    >> Murdoc wrote:
    >>> Dikkie Dik wrote:
    >>>
    >>>
    >>>> Assuming that fund_id is an autonumber, you can write:
    >>>>
    >>>> INSERT INTO fund(...) VALUES(...);
    >>>> SET FundID=LAST_INSERT_ID();
    >>>> INSERT INTO member(fund_id, ...) VALUES(FundID, ...);
    >>>>
    >>>> etc. The variables remain valid as long as the connection to the server remains open.
    >>>
    >>> I could do that if I was using a single INSERT statement for every 'fund' record. However, I'm 'bulk' inserting the records into the fund table with a single INSERT statement.
    >>>
    >>> Regards,
    >>> Murdoc.
    >>>
    >>>
    >> If you're going to use an autoincrement field and need the autoincrement value, you have to insert row at a time. This is true in MySQL and every other RDB I know of which has an autoincrement field.
    >
    > And hence the reason that I am not going to use the LAST_INSERT_ID() method as suggested. The SQL I am generating will lookup the required autoincrement value that was created based on a separate unique index (not the auto-increment field).
    >
    Dikkie Dik Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. Help with INSERT INTO statement
    By s_jancich in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: July 20th, 04:39 PM
  3. Replies: 3
    Last Post: September 30th, 09:24 PM
  4. access insert statement
    By Curt_C [MVP] in forum ASP
    Replies: 10
    Last Post: September 2nd, 04:04 PM
  5. Insert Statement help
    By ksbrace in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 02:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139