473,854 Members | 1,684 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5610
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=createobjec t("adodb.comman d")
cmd.CommandText = uSQL
set cmd.ActiveConne ction = YourOpenConnect ionObject
cmd.CommandType = 1 'adCmdText
Set objRS = cmd.Execute(,ar ray(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=createobjec t("adodb.comman d")
cmd.CommandText = uSQL
set cmd.ActiveConne ction = YourOpenConnect ionObject
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...@NOyah oo.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=createobjec t("adodb.comman d")
cmd.CommandText = uSQL
set cmd.ActiveConne ction = YourOpenConnect ionObject
cmd.CommandType = 1 'adCmdText
Set objRS = cmd.Execute(,ar ray(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=createobjec t("adodb.comman d")
cmd.CommandText = uSQL
set cmd.ActiveConne ction = YourOpenConnect ionObject
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
5525
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, UPDATE, DELETE) which are controlled by a web frontend and the table records are manipulated to control the permissions. Example: The Press Release section record would look like this: Username: John Doe Function Name: Press Release
0
3154
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 much data for field' or the query just runs on and on til I have to restart the postmaster. I have found rules to compare mine to but people limit the resulting insert to one record (the WHERE generally limits the result of the SELECT to one...
16
17040
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 must be UPDATED, if not, they must be INSERTED. Logically then, I would like to SELECT * FROM <TABLE> WHERE ....<Values entered here>, and then IF FOUND UPDATE <TABLE> SET .... <Values entered here> ELSE INSERT INTO <TABLE> VALUES <Values...
0
4495
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 procedure from SQL Server vs MS Access point of view ?
6
3107
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 primary key, no indexes(at least I don't think I have any), allow duplicates, allow zero length fields, the fields in the new table are of the same type of the fields in the old table, etc. Maybe someone can give me an idea as to what I'm doing...
3
1956
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 that I need to import in DOSSIER, so everything is ready. I used help to determine the correct synthax to do this with SQL but it keeps saying that my synthax is wrong, here are my lines:
8
2568
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, it tooks about 10-15 minutes. Now I'm inserting some data from that table to a brand new table in Postgresql, for that I'm doing insert into ... select from. The point is inserting this data from one table to another table in Postgresql took...
4
3136
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 can speed up the processing substantially? as it currently takes about 10 minutes and thats just way too long because there is many of these imports that I need to do.... I currently insert each record one by one and I imagine thats where all the...
8
6628
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 problem with my php setup but i could not find it... here is the code; ---------------------------------------------------------------------------------------- $db = new PDO("sqlite:bar.db");
58
8162
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 the parts section an i seem to be having trouble. When i try to insert into the parts section i get the error Invalid character value for cast specification. But not sure what i am doing wrong. Here is what i am using to insert. All the sections...
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11024
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10672
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10364
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7077
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5937
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4550
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3182
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.