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

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.