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?
JorgenVesterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
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 ...

hth

Mike
0
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
http://www.phpbuilder.com/lists/php-general/2003072/1510.php

0
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:insert>
<xsql:select connection
select latest id from tbl_appointment
</xsql:insert>
<xsql:select connection
insert id for appointment and personid's in table with this format:
appointmentid, personid
</xsql:insert>

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?
 

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

DcpKingCommented:
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 ?



0
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?  
0
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:
2,1
2,2
2,3
0
DarthModCommented:
Submitted to PAQ with points refunded (250)

DarthMod
Community Support Moderator
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.