moving all records from one database and inserting into another databa

[Joelo]

I am trying to move all records from one database (databasetemp) to another database (databaseoutput) and delete all records in databasetemp leaving it blank for future use....But I keep getting the following Error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/compliance/move3.asp, line 78

Line 78 is:
conn.execute strsql1

Please anybody help me with my script, I don't know what I am doing wrong


<%@ Language=&quot;VBScript&quot;%>
<% Option Explicit


response.buffer = true

Dim xDb_Conn_Str
Dim Conn
Dim strsql
Dim strsql1


xDb_Conn_Str = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & server.mappath(&quot;dboutput\Databaseoutput.mdb&quot & &quot;;&quot;
set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open xDb_Conn_Str


' Build Query

strsql = &quot;INSERT INTO bsldatabase (FACILITYCOMPONENTID, &quot; & _
&quot; COMPONENTDESCRIPTION, &quot; & _
&quot; DEVICEID, &quot; & _
&quot; ISSTACKARRESTORINSTALLED, &quot; & _
&quot; ISFLAMEARRESTORINSTALLED, &quot; & _
&quot; DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL, &quot; & _
&quot; TESTDATE, &quot; & _
&quot; NEXTDUETESTDATE, &quot; & _
&quot; MONTHYEAR, &quot; & _
&quot; FACILITY, &quot; & _
&quot; AREA, &quot; & _
&quot; TECHNICIANSNAME, &quot; & _
&quot; COMMENTS, &quot; & _
&quot; USERID &quot; & _
&quot; ) &quot; & _
&quot; SELECT [bsldatabase.FACILITYCOMPONENTID], &quot; & _
&quot; [bsldatabase.COMPONENTDESCRIPTION], &quot; & _
&quot; [bsldatabase.DEVICEID], &quot; & _
&quot; [bsldatabase.ISSTACKARRESTORINSTALLED], &quot; & _
&quot; [bsldatabase.ISFLAMEARRESTORINSTALLED], &quot; & _
&quot; [bsldatabase.DIDALARMOPERATEATWONDERWAREANDSHUTDOWNLOCALPANEL], &quot; & _
&quot; [bsldatabase.TESTDATE], &quot; &_
&quot; [bsldatabase.NEXTDUETESTDATE], &quot; & _
&quot; [bsldatabase.MONTHYEAR], &quot; & _
&quot; [bsldatabase.FACILITY], &quot; & _
&quot; [bsldatabase.AREA], &quot; & _
&quot; [bsldatabase.TECHNICIANSNAME], &quot; & _
&quot; [bsldatabase.COMMENTS], &quot; & _
&quot; [bsldatabase.USERID] &quot; & _
&quot; from [&quot;& server.mappath(&quot;dbtemp\Databasetemp.mdb&quot & &quot;].bsldatabase &quot;
conn.execute strsql
Response.Write strsql

strsql1 = &quot;INSERT INTO devicebsldevice (FACILITYCOMPONENTID, &quot; & _
&quot; DEVICEID, &quot; & _
&quot; ISDEVICEOPERABLE, &quot; & _
&quot; DIDFIREDCOMPONENTSHUTDOWN, &quot; & _
&quot; ISFLAMEARRESTORINSTALLED, &quot; & _
&quot; ISSTACKARRESTORINSTALLED, &quot; & _
&quot; DIDALARMOPERATEDURINGTEST, &quot; &_
&quot; DIDDEVICEINDICATEANDALARMATPLC, &quot; &_
&quot; ANNUALTESTDATE, &quot; & _
&quot; MONTHYEAR, &quot; & _
&quot; FACILITY, &quot; & _
&quot; AREA, &quot; & _
&quot; TECHNICIANNAME, &quot; & _
&quot; COMMENT, &quot; & _
&quot; SECTION, &quot; & _
&quot; USERID &quot; & _
&quot; ) &quot; & _
&quot; SELECT [devicebsldevice.FACILITYCOMPONENTID], &quot; & _
&quot; [devicebsldevice.DEVICEID], &quot; & _
&quot; [devicebsldevice.ISDEVICEOPERABLE], &quot; & _
&quot; [devicebsldevice.DIDFIREDCOMPONENTSHUTDOWN], &quot; & _
&quot; [devicebsldevice.ISFLAMEARRESTORINSTALLED], &quot; & _
&quot; [devicebsldevice.ISSTACKARRESTORINSTALLED], &quot; & _
&quot; [devicebsldevice.DIDALARMOPERATEDURINGTEST], &quot; & _
&quot; [devicebsldevice.DIDDEVICEINDICATEANDALARMATPLC], &quot; & _
&quot; [devicebsldevice.ANNUALTESTDATE], &quot; & _
&quot; [devicebsldevice.MONTHYEAR], &quot; & _
&quot; [devicebsldevice.FACILITY], &quot; & _
&quot; [devicebsldevice.AREA], &quot; & _
&quot; [devicebsldevice.TECHNICIANNAME], &quot; & _
&quot; [devicebsldevice.COMMENT], &quot; & _
&quot; [devicebsldevice.SECTION], &quot; & _
&quot; [devicebsldevice.USERID] &quot; & _
&quot; from [&quot;& server.mappath(&quot;dbtemp\Databasetemp.mdb&quot & &quot;].devicebsldevice &quot;
Response.Write strsql
conn.execute strsql1


set strsql = Nothing
set xDb_Conn_Str = Nothing


xDb_Conn_Str1 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & server.mappath(&quot;dbtemp\Databasetemp.mdb&quot & &quot;;&quot;
set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.Open xDb_Conn_Str1

conn.Execute = &quot;DELETE INTO bsldatabase&quot;
conn.Execute = &quot;DELETE INTO devicebsldevice&quot;

set conn = Nothing
set xDb_Conn_Str1 = Nothing
conn.Close


%>

 

[vbkris]

well unless i am mistaken u cannot add a select stmt in a Insert stmt...

Known is handfull, Unknown is worldfull

 

[Joelo]

I don't get it

Could you show me how to do it .... I am still learning

 

[vbkris]

the structure of an insert stmt is something like this:
&quot;insert into table(column) values(clovalue)&quot;

u cannot have a stmt like this:
&quot;insert into table(column) select values from someothertable&quot;

it may explain ur logic but the sql is wrong to transfer data from one table to another u cannot use this technique, u have to first give a select stmt from the table u want to copy from:
sql=&quot;select * from TableToCopyFrom&quot;
rs.open sql,con
do until rs.eof
'u get the value of all the fields, lets assume that there is only one column in this table
vl=rs(0)
sql1=&quot;insert into TableToCopyTo(column1) values('&quot;&vl&&quot;')&quot;
con.execute sql1
rs.movenext
loop

do u get it?

Known is handfull, Unknown is worldfull

 

[GaryC123]

If the table structure is identical then

sql = &quot;INSERT INTO tablename SELECT * from tablename&quot;
conn.execute(sql)

Of course you can append any conditions to the end of the sql

sql = &quot;INSERT INTO tablename SELECT * from tablename WHERE Field10 = something&quot;

Hmmm 60 lines of code down into 1 line ;o)

http://www.intouchmedialtd.com

 

[Joelo]

Yes, the table structure is identical...but I am trying to exclude the autonumber field (Primary Key)

Please Help me out

Thanx in Advance

 

[GaryC123]

I would suggest having exactly the same structure even if you're not using the ID field in the second table - just because its in the second table doesn't mean you have to use it. Means you can have a nice clean sql otherwise you have to set what fields to copy over.

http://www.intouchmedialtd.com

 

[vbkris]

Garcy, does ur stmt hold true for all DBs? if it does boy its going to save me a lot of time...

Known is handfull, Unknown is worldfull

 

[GaryC123]

Works for Access,MS-SQL and MySQL, I don't have ready access to any other DB's

http://www.intouchmedialtd.com

 

[GaryC123]

Actually just reread your question and see your q header says from one database to another database, whereas your code suggests from one table to another table in the same database.
If it is from one db to another db then use this code

strSQL=&quot;SELECT * INTO [tablename1] IN 'c:\inetpub\

http://wwwroot\db2.mdb'

FROM [tablename2]&quot;
conn.execute(strSQL)

Some explanation
SELECT * INTO [tablename1] - Insert all records into tablename1, this will create a new table called tablename1 so it mustn't exist otherwise you will get an error
'c:\inetpub\

http://wwwroot\db2.mdb'

- is the path and name of the database to insert into
tablename2 - is the name of the table we're copying data from.

Your connection should point to the original database.

http://www.intouchmedialtd.com

 

[active]

GaryC123 - I try to copy a table from Pervasive to Mysql - needs to be done at least once a day -and the recordset to recordset is SLO

http://WWWW.

Is the above code suppose to work when you copy between different types of databases and or servers? Also - if the new table doesn't exist - why does it have to be identical with the original one? I know this is an old thread - just thought that maybe you have the mail set to get the new posts - I appreciate any help. We do have the SqlWaysWizard to transfet the tables but it needs to be done by a secretary and she said it's too much to remember

 

[GaryC123]

No it can only be used for the same database types.

http://www.intouchmedialtd.com

 

[active]

GaryC123 - THANK YOU - I appreciate your quick response ) ... even if it works only between same type db's it saves a lot of time - thank you again for the tip