Help with: insert...into...select...union...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Help with: insert...into...select...union...

Author

Topic

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-20 : 18:17:54

Here is the problem:
I want to insert multiple rows in a table (e.g T1) in a single SQL statement.
This table has 3 columns (integer, integer, boolean)
I know the exact values to be inserted.
I try something like this:

INSERT INTO T1(col1, col2, col3)
SELECT * FROM
(
SELECT 7, 2, false UNION
SELECT 7, 4, true
) T

and a msgbox pops-up:
"Query input must contain at least one table or query"

I tried something like this before with SQL Server and worked.
Does anyone know if it is "doable" in MS Access?
The actual problem is in the inner SELECT statement, because if I try them without the insert statement I get the same error...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-09-20 : 19:19:54

It is do-able, but you need to coax Access a bit.

Normally I would break this into 2 queries:
Query1 contains just the SELECT ... UNION statement
Query2 INSERTS the results into your new table.

You're probably finding out that working with Access after coming from SQL is very frustrating....

HTH,

Tim

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 04:03:20

Yes I tried that, but I get the same error...
If the SQL statement is only:

SELECT 7,2,false

it's ok!

But if I add one more SELECT statement to the query:
SELECT 7,2,false UNION
SEELCT 7,4,true

I get the error.

(Note: I get the error when I try to execute the query)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-21 : 09:35:47

quote:
Originally posted by vassilisvas

Yes I tried that, but I get the same error...
If the SQL statement is only:

SELECT 7,2,false

it's ok!

But if I add one more SELECT statement to the query:
SELECT 7,2,false UNION
SEELCT 7,4,true

I get the error.

(Note: I get the error when I try to execute the query)

Well, you have a typo there (SEELCT). And you didn't name any of your columns. Why not post the *actual* statement you are trying to run and the actual error message, then it will be a little eaiser for us to help you.

- Jeff

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 10:37:25

Sorry about the typo.

Here is the problem again:
I want to fetch more than one row, but not from a table!

If I write:
SELECT 'hello'
I get a table with 1 row and 1 column, displaying the word "hello".

But If I try this:
SELECT 'hello' UNION SELECT 'world'
I get this error message:

"Query input must contain at least one table or query"

I tried to rename the returned column like this:
SELECT 'hello' as col1
UNION
SELECT 'world' as col1

but I got the same error.

I tried even this:
SELECT T.Name
FROM Teacher AS T
UNION SELECT 'hello';

but I got the same error.

So, the question again:
Does anyone know how to fetch multiple rows of data that do not exist in a table, but are given in the query?

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 10:46:26

I've just found a way to achieve what I want, but I hoped if there was a better one...

SELECT DISTINCT 'hello'
FROM Teacher
UNION
SELECT DISTINCT 'world'
FROM Teacher;

This query returns a table with 2 rows and 1 column, but it can be very slow if the Teacher table is big.

Does anyone know a better way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 11:09:20

Try to add FIRST keyword, or TOP 1.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-21 : 12:03:12

Why not just store the data in a table and use that? i.e., create a table. Type in a few rows for the values you want. Then use that table any time you want to insert those values.

It is bad practice to hard-code data into your SQL statements....

what exactly are you trying to do?

- Jeff

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 14:58:05

>Why not just store the data in a table and use that? i.e., create a >table. Type in a few rows for the values you want. Then use that >table any time you want to insert those values.
>
>It is bad practice to hard-code data into your SQL statements....
>
>what exactly are you trying to do?

I thought about doing that, but I would like to avoid it if there is another way.
What I am trying to do, is generate a SQL command from the program in order to do multiple inserts in a table. This command depends on the user's choice. Specifically, the user will select some subjects in a checked list box and these subjects are going to be inserted in the table...

>Try to add FIRST keyword, or TOP 1.
>
>Peter Larsson
>Helsingborg, Sweden

I didn't understand how to use these keywords. I've never used them.

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 15:12:17

It seems that there are 3 possible ways:

1) create a temporary table, fill it with data and use it to insert the data in the correct table

2) use the unorthodox way I've already posted to fetch the data, in order to do the insertions (I can use a table which has permanently 6 records only instead of the Teacher table)

3) generate multiple insert commands from the program

I would like to read your suggestions about this dilemma, in terms of speed and performance...

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 16:42:57

FIRST, or TOP 1, only selects one record from the table.
So you don't have to use DISTINCT, which looks at all records in table.

This might work (it is ages since i did any real work in ACCESS)

SELECT TOP 1 'hello'
FROM Teacher
UNION
SELECT TOP 1 'world'
FROM Teacher;

Or this

SELECT FIRST 'hello'
FROM Teacher
UNION
SELECT FIRST 'world'
FROM Teacher;

Peter Larsson
Helsingborg, Sweden

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-21 : 17:08:01

Now I understand what you were saying, about FIRST or TOP 1.
It seems a nice way to do what I want!

Thanks Peter Larsson, Jeff and Tim for your help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 17:36:58

Did the suggestion make the query go faster?

Peter Larsson
Helsingborg, Sweden

vassilisvas
Starting Member

8 Posts

Posted - 2006-09-22 : 21:40:07

I don't have a lot of data in the tables, but in order to test the speed of the query, I made a simple experiment:

I created 3 queries:
1)
SELECT 7 AS Teacher_Id, 2 AS Subject_Id, false AS Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student
UNION
SELECT 7 as Teacher_Id, 4 as Subject_Id,true as Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student;

This query can fetch the data I want because the UNION command excludes any duplicate rows.

2)
SELECT DISTINCT 7 AS Teacher_Id, 2 AS Subject_Id,false AS Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student
UNION
SELECT DISTINCT 7 as Teacher_Id, 4 as Subject_Id,true as Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student;

I've just added the DISTINCT keyword

3)
SELECT TOP 1 7 AS Teacher_Id, 2 AS Subject_Id, false AS Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student
UNION
SELECT TOP 1 7 as Teacher_Id, 4 as Subject_Id,true as Main_Subject
FROM Hours_Reserved, Teacher, Hours_Available, Room, Subject, Student;

I've just added the TOP 1 keyword.

Note: In FROM clauses I added a lot of tables. All of them contain about 410 rows.

Results:
The 1st and 2nd queries were way too slow!
The 3rd query was VERY fast!

Thank you again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-23 : 19:37:22

4)
SELECT TOP 1 7 AS Teacher_Id, 2 AS Subject_Id, false AS Main_Subject
FROM Room
UNION
SELECT TOP 1 7 as Teacher_Id, 4 as Subject_Id,true as Main_Subject
FROM Room;

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources