当从Access .mdb表中插入选定的记录/行时,我在该代码块的第23行找到了" Syntax error in INSERT INTO statement. (-3502)
"。
我想要发生的是
- 创建表65001
- 从'LMR_Combined'表中选择前65000行
- 将选中的行插入到新建的65001表中。
在这个INSERT INTO语句中有什么语法错误阻止了成功的执行?
import pyodbc
DBFile = r'C:Python27FCC_ProcessingLMR Combined.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBFile)
cursor = conn.cursor()
# Creates a table "65001" in the MDB that matches the schema of table "LMR_Combined"
string = "CREATE TABLE 65001(OBJECTID integer, Unique_ID varchar(255), LICENSEE_NAME varchar(255))"
cursor.execute(string)
# Selects 65000 records from table "LMR_Combined"
cursor.execute('select OBJECTID, Unique_ID, LICENSEE_NAME from LMR_Combined where OBJECTID > 0 and OBJECTID < 65001')
row = cursor.fetchone()
# For debugging, print a line
if row:
print row
# Inserts the 65000 rows into the new table "65001"
cursor.execute('insert OBJECTID, Unique_ID, LICENSEE_NAME into 65001')
conn.commit()
Thanks in advance
考虑用SELECT语句创建和填充65001表是否足够。声明:
SELECT OBJECTID, Unique_ID, LICENSEE_NAME
INTO [65001]
FROM LMR_Combined
WHERE OBJECTID > 0 AND OBJECTID < 65001;
如果您需要不同于SELECT...INTO
提供的数据类型,请执行CREATE TABLE
,然后执行INSERT
:
INSERT INTO [65001] (OBJECTID, Unique_ID, LICENSEE_NAME)
SELECT OBJECTID, Unique_ID, LICENSEE_NAME
FROM LMR_Combined
WHERE OBJECTID > 0 AND OBJECTID < 65001;
你的语法错误。
在表中插入数据的正确方法是:
insert into table1 (field1, field2, ...)
select ...
或:
insert into table1(field1, field2, ...)
values (value1, value2, ...)