How?    INSERT INTO TableXX(ID1, ID2)  SELECT  3 (sameiD),   4,5,6,7 (list of id's)

Posted on 2004-10-28
Last Modified: 2006-11-17
INSERT INTO TableXX(ID1, ID2)  SELECT  3 (sameiD),   4,5,6,7 (list of id's)

Can this be done in 1 SQL sentence:

INSERT INTO TableXX(ID1, ID2)  SELECT  3 (sameiD),   4,5,6,7 (list of id's)

The code is to be executed in xsql where there is no possibility for loops.
Is it possible to do this in one sql-sentence without a stored procedure?
Question by:JorgenVester
    LVL 16

    Expert Comment

    Your example is rather unclear, but ...

    if you want to load columns in a table and do multiple records, you have obviously to get multiple data rows:

    insert into TableXX
    (ID1, ID2, Kon1)
    (         select a.x1, b.x2, 'asdf' as Kon
              from TableAA a, TableBB b
              where a.X3 = b.x3

    generally works, although the need for parens, etc., depends on the precise version of SQL parser you're using.

    So far as I know you can't insert a set of constants into a table (i.e. have Kon1 above be 'asdf' in row 1, 'sdfg' in row 2, 'dfgh' in row 3, etc) without first stuffing some other table with the list of "constants" and getting them from there ...

    Perhaps you can provide a clearer example of what you want to do ...


    LVL 6

    Expert Comment

    by:Duane Lawrence
    Not to clear on this, but here is a stab at it.

    A properly formed insert of this type will be
    insert into tablexx ( aa, bb )
    select aa, bb from tablezz

    This is an example of XSQL


    Author Comment

    OK. I understand it is not clear enough. I am doing this for a friend who has asked me because I have very much experience in MS SQL Server. Unfortunately I have no experience with xsql or mySQL.

    Here is his scenario:

    From a form he receives via http attributes for an appointment and a list of id's for persons. This is the flow of the code:
    <xsql:insert connection
    insert into tbl_appointment (x,y) values (z,e)
    <xsql:select connection
    select latest id from tbl_appointment
    <xsql:select connection
    insert id for appointment and personid's in table with this format:
    appointmentid, personid

    my solution with MS SQL Server would be to call a stored procedure and let this do the work.
    But can it be done without this? In one sql-sentence?

    LVL 16

    Expert Comment

    The INSERT, as is, is pretty much ANSO standard SQL:
    'insert into tbl_appointment (x,y) values (z,e)'

    As is the SELECT:
    'select latest id from tbl_appointment'

    You have to work out what this 'means' in terms of your MySQL database, but it'll be the same as for MS SQL Server:
    'insert id for appointment and personid's in table with this format:
    appointmentid, personid'

    Why don't you use MySQL 5 and stored procedures ?


    Author Comment

    To DcpKing:

    As I write above: it is a friend who has asked me to help him and I have absolutely no experience with mySQL.
    I have always worked with MS SQL Server. So the easy way for me - and him - would be to do all directly from the code.
    But OK. Maybe this is the time to get some MySQL-experience.
    I must get MySQL 5 to use Stored procedures?
    Is there an administration interface like the Enterprise Manager for MS SQL Server?  

    Author Comment

    I found this solution myself.
    I know that the personID exists in another table, which means that a string can be built so the db-server receives this:

    insert into tblAppointment(appointmentID, personID)
    select 2, tblPerson.ID
    from tblPerson
    where tblPerson.ID in(1,2,3)

    2 = appointmentID
    1,2,3 = personID's

    result - 3 rows in the table:
    LVL 1

    Accepted Solution

    Submitted to PAQ with points refunded (250)

    Community Support Moderator

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now