Insert into sysadm.tablename with multipy values

Question

I am struggeling on a sql statement and can't find the correct syntax for this.

Im programming with teamdeveloper 6.1 and I am using the function SqlPrepareAndExecute(...)

What i'm trying is to insert a set of values. On mysql it would be some like:

INSERT INTO supportContacts
    (type, details)
VALUES
    ('Email', '[email protected]'),
    ('Twitter', '@sqlfiddle');

With SqlTalk i can do this...

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
/

Bot both won't work when I fill a variable and use it in the SqlExecute function of TD 6.1

I allways get errors like Statement not ended properly or Invalid constant.

I also tried to put the values like:

('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), ...

Error...

what am I doing wrong ?

EDIT (Things I have tried untill now):

---

Try 1

This here all

---

Try 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"

What only sayes SQL command not properly ended

---

Try 3

I thought may its because of the \. Actually it is a escapechar so I escaped it like

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

Now it sayed Invalid Character

---

Try 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"

Was a fail also! :)

Answer 1

This may just be due to the example query, but I'm not sure whether you are aware how databinding works in TD:

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

where var1, var2, var3 are variables visible in the function that calls SqlExecute/SqlPrepareAndExecute

Answer 2

Is your source data in database table? Then You can use INSERT SELECT statement.

Like this:

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

EDIT:

Or You can use:

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

Some code to illustrate the general syntax.
Caveats:

1. this is done with CTD 2.1.
2. I left out all the code to connect to the database and just assume h_SqlMain as a valid connection handle.
3. The data comes from an array. In real life you would e.g. read a CSV file.
4. The variables myType and myDetail are used as binding variables in the SqlPrepare-Statment because CTD 2.1 does not support the direct use of array variables as bindings.

The "Local Variable"-Part:

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

The "Actions"-Part:

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