473,385 Members | 1,830 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

INSERT Data from SELECT query

I think I am attempting a simple procedure but I just can't figure out
the correct syntax. My asp (classic) page runs a SELECT query to
obtain dates and ID's from 2 tables

uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
(((calendar.cal_Date) Between [holiday_tbl].[startdate] And
[holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

This works fine.

What I would like to do next is insert the returned values (cal_date)
and (holiday_ID) into a seperate table called holiday_dates. I am
happy to insert the results one recordset at a time, but I don't know
how to do it. I know that uSQL is returning results

'Loop until we've hit the EOF
Do Until objRS.EOF = True
response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
objRS.movenext
Loop

I would appreciate any help
Aug 6 '08 #1
2 5575
paulmitchell507 wrote:
I think I am attempting a simple procedure but I just can't figure out
the correct syntax. My asp (classic) page runs a SELECT query to
obtain dates and ID's from 2 tables

uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
(((calendar.cal_Date) Between [holiday_tbl].[startdate] And
[holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID

This works fine.
But it's non-standard syntax. Better would be (and not the use of table
aliases:
uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=" & Staff_ID

Better yet would be the use of a parameter token to pass the staff_id value.
I would do it like this:

uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
Set objRS = cmd.Execute(,array(Staff_ID))
etc.
>
What I would like to do next is insert the returned values (cal_date)
and (holiday_ID) into a seperate table called holiday_dates. I am
happy to insert the results one recordset at a time, but I don't know
how to do it. I know that uSQL is returning results

'Loop until we've hit the EOF
Do Until objRS.EOF = True
response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
objRS.movenext
Loop

I would appreciate any help
You left out some information that would have been helpful:
-the datatypes of the cal_date and holiday_ID fields
-the names and datatypes of the fields you want to insert these values into

You also left out some information that should be provided with every
database-related question you ask (think about creating a template with this
info):
database type and version
The parentheses in your WHERE clause lead me to believe you used the Access
Query Builder to construct this query, but this is just a guess - please
don't make us guess. :-)

Is this holiday_dates table in the same database? if so, there is absolutely
no need to open and loop through a recordset here. a simple INSERT...SELECT
statement will do this job nicely. Here is the sql statement:

iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
"SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN holiday_tbl As h ON " & _
"c.cal_Date Between h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
cmd.Execute ,array(Staff_ID)

As to why parameter tokens are better than dynamic sql, here is my canned
reply:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

SQL Server:

http://groups.google.com/group/micro...9dc1701?hl=en&


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Aug 6 '08 #2
On Aug 6, 12:14*pm, "Bob Barrows [MVP]" <reb01...@NOyahoo.SPAMcom>
wrote:
paulmitchell507 wrote:
I think I am attempting a simple procedure but I just can't figure out
the correct syntax. *My asp (classic) page runs a SELECT query to
obtain dates and ID's from 2 tables
uSQL = "SELECT cal_date, holiday_ID from Calendar, holiday_tbl WHERE
(((calendar.cal_Date) Between [holiday_tbl].[startdate] And
[holiday_tbl].[enddate])) And Email_sent=0 AND Staff_ID=" & Staff_ID
This works fine.

But it's non-standard syntax. Better would be (and not the use of table
aliases:
uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN *holiday_tbl As h ON " & _
"c.cal_Date Between *h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=" & Staff_ID

Better yet would be the use of a parameter token to pass the staff_id value.
I would do it like this:

uSQL = "SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN *holiday_tbl As h ON " & _
"c.cal_Date Between *h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
Set objRS = cmd.Execute(,array(Staff_ID))
etc.
What I would like to do next is insert the returned values (cal_date)
and (holiday_ID) into a seperate table called holiday_dates. *I am
happy to insert the results one recordset at a time, but I don't know
how to do it. *I know that uSQL is returning results
'Loop until we've hit the EOF
Do Until objRS.EOF = True
response.write objRS("holiday_ID") & " " & objRS("cal_date") & " "
objRS.movenext
Loop
I would appreciate any help

You left out some information that would have been helpful:
-the datatypes of the cal_date and holiday_ID *fields
-the names and datatypes of the fields you want to insert these values into

You also left out some information that should be provided with every
database-related question you ask (think about creating a template with this
info):
database type and version
The parentheses in your WHERE clause lead me to believe you used the Access
Query Builder to construct this query, but this is just a guess - please
don't make us guess. :-)

Is this holiday_dates table in the same database? if so, there is absolutely
no need to open and loop through a recordset here. a simple INSERT...SELECT
statement will do this job nicely. Here is the sql statement:

iSQL = "INSERT holiday_dates (holiday_ID,cal_date) " & _
"SELECT cal_date, holiday_ID from Calendar As c" & _
"INNER JOIN *holiday_tbl As h ON " & _
"c.cal_Date Between *h.startdate AND h.enddate " & _
"WHERE Email_sent=0 AND Staff_ID=?"

dim cmd:set cmd=createobject("adodb.command")
cmd.CommandText = uSQL
set cmd.ActiveConnection = YourOpenConnectionObject
cmd.CommandType = 1 'adCmdText
cmd.Execute ,array(Staff_ID)

As to why parameter tokens are better than dynamic sql, here is my canned
reply:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:http://mvp.unixwiz.net/techtips/sql-....aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:http://groups-beta.google.com/group/...etserver.asp.d...

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:http://www.google.com/groups?hl=en&l...8&selm=e6lLVvO...

http://groups.google.com/groups?hl=e...ff=1&selm=eHYx...

SQL Server:

http://groups.google.com/group/micro...ver.asp.genera...

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Wow! what a fantastic reply.
I will follow your template for future posts..of which there will be
many!
I have taken your advice and re-coded all me asp pages to use saved
parameter queries.
You guessed correctly, I have an access 2k database.
Aug 7 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE,...
0
by: jtocci | last post by:
I'm having a big problem with CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50) records based on a single INSERT to a view. Either I get a 'too...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
0
by: ImraneA | last post by:
Hi there I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K. Wish to provide some knowledge gained back to community - hopefully help others. 1.Question how do you test stored...
6
by: efgh | last post by:
I'm fairly confident in my knowledge of SQL but I'm stumped with regards with an Insert Into statement in Access 2003. I've got a 4 column table that I've simplified about as much as I can, no...
3
by: John Marble | last post by:
I am quite new with Access, so please be indulgent if my question sound a little bit newbish. I have two table in the same database: DOSSIER and MAIN. MAIN is having already formatted data...
8
by: Josué Maldonado | last post by:
Hello List, I'm importing some data from Foxpro to Postgres, there is atable wich contains aprox 4.8 million rows and it size about 830MB. I uploaded it to Postgres using dbf2pg and worked fine,...
4
by: Bob | last post by:
Hi all, I'm trying to import data, modify the data then insert it into a new table. The code below works fine for it but it takes a really long time for 15,000 odd records. Is there a way I...
8
by: Betikci Boris | last post by:
Can not insert data into SQLite3 database through browser however i can easily insert data into my db from konsole, in both attmpts i used php 5.2.6 on 2.6.25.* linux kernel i think there is a...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.