How to insert multimple records of numbers with one statement in ORACLE and Sql-server using the same sentence

In my C++ program I tried something like this:

INSERT INTO TEMP_TABELA (OSE_ID) values (7,12,16,17,19,21,24,26,30,33,35,38,42,46,53,58,59,72,73,74,77,78,82,86,87,88,89,91,92,93,100,101,102,104,106,109,113,115,127,133,139,140,142,143,144,148,149,150,151,153,155,160,164,166,167,170,172,178,188,189,191,192,198,199,200,201,202,203,205,207,208,219,220,223,225,231,233,236,240,241,242,244,245,253);

But all I got was: Description: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in开发者_如何学C the INSERT statement. Source: Microsoft OLE DB Provider for SQL Server

The same sentence must work also on Oracle, because my program works for both. Can anyone help me?

Oracle's and SQL Server's bulk insertion syntax is different from one another, so the safest means is to use individual INSERT statements:

INSERT INTO TEMP_TABELA (OSE_ID) values (7);
INSERT INTO TEMP_TABELA (OSE_ID) values (12);
INSERT INTO TEMP_TABELA (OSE_ID) values (16);
INSERT INTO TEMP_TABELA (OSE_ID) values (17);
...  
INSERT INTO TEMP_TABELA (OSE_ID) values (253);

This works in Oracle, it split's on the string (assuming 10g+)

CREATE TABLE TESTX(NUM NUMBER);

INSERT INTO TESTX(NUM)
select regexp_substr (x, '[^,]+', 1, level) as token
  from (SELECT '7,12,16,17,19,21,24,26,30,33,35,38,42,46,53,58,59,72,73,74,77,78,82,86,87,88,89,91,92,93,100,101,102,104,106,109,113,115,127,133,139,140,142,143,144,148,149,150,151,153,155,160,164,166,167,170,172,178,188,189,191,192,198,199,200,201,202,203,205,207,208,219,220,223,225,231,233,236,240,241,242,244,245,253' X FROM DUAL) t
connect by  regexp_instr (x, '[^,]+', 1, level) > 0  


84 rows inserted

Do this for Oracle:

INSERT INTO TEMP_TABELA (OSE_ID)
select 7 from dual union all
select 12 from dual union all
select 16 from dual union all
select 17 from dual union all
select 19 from dual union all
select 21 from dual union all
select 24 from dual union all
select 26 from dual union all
select 30 from dual union all
select 33 from dual union all
select 35 from dual union all
select 38 from dual union all
select 42 from dual union all
select 46 from dual union all
select 53 from dual union all
select 58 from dual union all
select 59 from dual union all
select 72 from dual union all
select 73 from dual union all
select 74 from dual union all
select 77 from dual union all
select 78 from dual union all
select 82 from dual union all
select 86 from dual union all
select 87 from dual union all
select 88 from dual union all
select 89 from dual union all
select 91 from dual union all
select 92 from dual union all
select 93 from dual union all
select 100 from dual union all
select 101 from dual union all
select 102 from dual union all
select 104 from dual union all
select 106 from dual union all
select 109 from dual union all
select 113 from dual union all
select 115 from dual union all
select 127 from dual union all
select 133 from dual union all
select 139 from dual union all
select 140 from dual union all
select 142 from dual union all
select 143 from dual union all
select 144 from dual union all
select 148 from dual union all
select 149 from dual union all
select 150 from dual union all
select 151 from dual union all
select 153 from dual union all
select 155 from dual union all
select 160 from dual union all
select 164 from dual union all
select 166 from dual union all
select 167 from dual union all
select 170 from dual union all
select 172 from dual union all
select 178 from dual union all
select 188 from dual union all
select 189 from dual union all
select 191 from dual union all
select 192 from dual union all
select 198 from dual union all
select 199 from dual union all
select 200 from dual union all
select 201 from dual union all
select 202 from dual union all
select 203 from dual union all
select 205 from dual union all
select 207 from dual union all
select 208 from dual union all
select 219 from dual union all
select 220 from dual union all
select 223 from dual union all
select 225 from dual union all
select 231 from dual union all
select 233 from dual union all
select 236 from dual union all
select 240 from dual union all
select 241 from dual union all
select 242 from dual union all
select 244 from dual union all
select 245 from dual union all
select 253 from dual

And this for SQL Server:

INSERT INTO TEMP_TABELA (OSE_ID) 
select 7 union all
select 12 union all
select 16 union all
select 17 union all
select 19 union all
select 21 union all
select 24 union all
select 26 union all
select 30 union all
select 33 union all
select 35 union all
select 38 union all
select 42 union all
select 46 union all
select 53 union all
select 58 union all
select 59 union all
select 72 union all
select 73 union all
select 74 union all
select 77 union all
select 78 union all
select 82 union all
select 86 union all
select 87 union all
select 88 union all
select 89 union all
select 91 union all
select 92 union all
select 93 union all
select 100 union all
select 101 union all
select 102 union all
select 104 union all
select 106 union all
select 109 union all
select 113 union all
select 115 union all
select 127 union all
select 133 union all
select 139 union all
select 140 union all
select 142 union all
select 143 union all
select 144 union all
select 148 union all
select 149 union all
select 150 union all
select 151 union all
select 153 union all
select 155 union all
select 160 union all
select 164 union all
select 166 union all
select 167 union all
select 170 union all
select 172 union all
select 178 union all
select 188 union all
select 189 union all
select 191 union all
select 192 union all
select 198 union all
select 199 union all
select 200 union all
select 201 union all
select 202 union all
select 203 union all
select 205 union all
select 207 union all
select 208 union all
select 219 union all
select 220 union all
select 223 union all
select 225 union all
select 231 union all
select 233 union all
select 236 union all
select 240 union all
select 241 union all
select 242 union all
select 244 union all
select 245 union all
select 253

The parentheses in your statement in the example indicate to SQL Server that you want all that to be on one row. You need to enclose each row in parentheses and separate those by commas, i.e:

INSERT INTO TEMP_TABELA (OSE_ID) 
values  (7),
        (12),
        (16),
        (17),
        (19),
        (21),
        (24),...

I'm not sure if this will work in Oracle or not, but if not you may need to create a condition in your calling application to determine which DB it's working with and modify the query appropriately.

Similar to @OMGPonies can also do something like:

INSERT INTO TEMP_TABLEA(OSE_ID)
SELECT 7 union all
SELECT 12 ...

Not sure which is faster or even if you care about speed given the number of items you're inserting.

Not tested on oracle but I'm about 90% sure it will work.