插入具有多值的sysadm.tablename

Question

我在sql语句上苦苦挣扎，无法找到正确的语法。

我是用TeamDeveloper6.1编程的，我正在使用SqlPrepareAndExecute(...)函数

我尝试的是插入一组值。在mysql上，它应该是这样的：

INSERT INTO supportContacts
    (type, details)
VALUES
    ('Email', 'admin@sqlfiddle.com'),
    ('Twitter', '@sqlfiddle');

有了SqlTalk我就能做到..。

INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$datatypes CHARACTER,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC
"01",0,0,0,0,0,0,0,0,0,0,0,0
"02",0,0,0,0,0,0,0,0,0,0,0,0
"03",0,0,0,0,0,0,0,0,0,0,0,0
"04",0,0,0,0,0,0,0,0,0,0,0,0
"05",0,0,0,0,0,0,0,0,0,0,0,0
"06",0,0,0,0,0,0,0,0,0,0,0,0
"07",0,0,0,0,0,0,0,0,0,0,0,0
"08",0,0,0,0,0,0,0,0,0,0,0,0
/

当我在TD 6.1的SqlExecute函数中填充一个变量并使用它时，Bot和Bot都不能工作。

我总是会遇到像Statement not ended properly或Invalid constant这样的错误。

我还试着把以下的价值观：

('01',0,0,0,0,0,0,0,0,0,0,0,0),
('02',0,0,0,0,0,0,0,0,0,0,0,0),
('03',0,0,0,0,0,0,0,0,0,0,0,0), ...

错误..。

我做错什么了？

编辑(直到现在我尝试过的东西)：

尝试1

This here all

尝试2

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$datatypes CHARACTER,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC,NUMERIC
'01',0,0,0,0,0,0,0,0,0,0,0,0
'02',0,0,0,0,0,0,0,0,0,0,0,0"

什么只有sayes SQL command not properly ended

尝试3

我想可能是因为\。实际上，这是一次越狱，所以我就像

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\\
$datatypes....

现在它发送无效字符

尝试4

"INSERT INTO SYSADM.FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
\
$da...
\'01\',0,0,0,0,0,0,0,0,0,0,0,0
\'02\',0,0,0,0,0,0,0,0,0,0,0,0"

也是个失败！)

Answer 1

这可能只是由于示例查询，但我不确定您是否知道如何在TD中进行数据库工作：

INSERT INTO SYSADM.FOOBAR VALUES(:var1,:var2,:var3)

其中，var1、var2、var3是调用SqlExecute/SqlPrepareAndExecute函数中可见的变量。

Answer 2

源数据在数据库表中吗？然后可以使用INSERT SELECT语句。

如下所示：

INSERT INTO SYSADM.FOOBAR (COL1, COL2, COL3) SELECT COL1, COL2, COL3 FROM SOURCE_DATA

编辑：

或者你可以使用：

Call SqlPrepareAndExecute( hSql, '
INSERT INTO FOOBAR VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
select \'01\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'02\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'03\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'04\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'05\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'06\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'07\',0,0,0,0,0,0,0,0,0,0,0,0 from dual union all
select \'08\',0,0,0,0,0,0,0,0,0,0,0,0 from dual ' )

Answer 3

一些代码来说明一般语法。

注意事项：

1. 这是用CTD 2.1完成的。
2. 我省略了连接到数据库的所有代码，只是假设h_SqlMain是一个有效的连接句柄。
3. 数据来自一个数组。在现实生活中，你可以读一个CSV文件。
4. 在SqlPrepare-Statment中，变量myType和myDetail用作绑定变量，因为CTD2.1不支持将数组变量直接用作绑定。

“局部变量”-Part：

String: myType
String: myDetail
String: types[*]
String: details[*]
Number: i
Sql Handle: h_SqlMain

“行动”-Part：

Set types[0] = 'Email'
Set details[0] = 'admin@sqlfiddle.com'
Set types[1] = 'Twitter'
Set details[1] = '@sqlfiddle'
Set types[2] = 'Foo'
Set details[2] = 'Bar'
Call SqlPrepare( h_SqlMain, 
   "INSERT INTO supportContacts (type, details) 
    VALUES(:myType, :myDetail)")
Set i=0
While i <= 2 
    Set myType = types[i]
    Set myDetail = details[i]
    Call SqlExecute( h_SqlMain )
    Set i=i+1