插入到 sysadm.tablename 中，其中包含多个值

Question

我正在努力解决sql语句，找不到正确的语法。

我使用 teamdeveloper 6.1 编程，我正在使用函数SqlPrepareAndExecute(...)

我正在尝试的是插入一组值。在mysql上，它会像这样：

INSERT INTO supportContacts
    (type, details)
VALUES
    ('Email', '[e-mail]'),
    ('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函数中使用它时，机器人都不起作用

我总是收到像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

这里都是

---

尝试 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"

只说什么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

一些代码来说明一般语法。
警告：

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

"局部变量"部分：

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

"行动"部分：

Set types[0] = 'Email'
Set details[0] = '[e-mail]'
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

Answer 3

您的源数据是否在数据库表中？然后你可以使用 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 ' )