Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-10-28
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Expert Comment

ID: 12433312
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 ...



Expert Comment

by:Duane Lawrence
ID: 12433435
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

ID: 12433613
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?

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 16

Expert Comment

ID: 12433739
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

ID: 12433862
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

ID: 12436163
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:

Accepted Solution

DarthMod earned 0 total points
ID: 13772665
Submitted to PAQ with points refunded (250)

Community Support Moderator

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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