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

paulmitchell507 wrote:

Quote: 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

>

Quote: 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

>

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

Quote: 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

>

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

>

Quote: 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...t.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...netserver.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&...-8&selm=e6lLVvO...
>
http://groups.google.com/groups?hl=...off=1&selm=eHYx...
>
SQL Server:
>
http://groups.google.com/group/micr...rver.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"