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

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?
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

DarthModConnect With a Mentor Commented:
Submitted to PAQ with points refunded (250)

Community Support Moderator
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 ...


Duane LawrenceCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

JorgenVesterAuthor Commented:
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?

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 ?

JorgenVesterAuthor Commented:
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?  
JorgenVesterAuthor Commented:
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:
All Courses

From novice to tech pro — start learning today.