Comment insert des loggings multimétaux de nombres avec une instruction dans ORACLE et Sql-server en utilisant la même phrase

Dans mon programme C ++ j'ai essayé quelque chose comme ceci:

INSÉRER DANS TEMP_TABELA (OSE_ID) valeurs (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);

Mais tout ce que j'ai obtenu était: Description: Il y a less de colonnes dans l'instruction INSERT que de valeurs spécifiées dans la clause VALUES. Le nombre de valeurs dans la clause VALUES doit correspondre au nombre de colonnes spécifié dans l'instruction INSERT. Source: Fournisseur Microsoft OLE DB pour SQL Server

La même phrase doit également fonctionner sur Oracle, car mon programme fonctionne pour les deux. Quelqu'un peut-il m'aider?

La syntaxe d'insertion en bloc d'Oracle et de SQL Server est différente l'une de l'autre. Par conséquent, le moyen le plus sûr consiste à utiliser des instructions INSERT individuelles:

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); 

Cela fonctionne dans Oracle, il est divisé sur la string (en supposant 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 

Faites ceci pour 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 

Et ceci pour 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 

Les parenthèses dans votre instruction dans l'exemple indiquent à SQL Server que vous voulez que tout cela soit sur une ligne. Vous devez placer chaque ligne entre parenthèses et les séparer par des virgules, c'est-à-dire:

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

Je ne sais pas si cela fonctionnera dans Oracle ou non, mais sinon, vous devrez peut-être créer une condition dans votre application appelante pour déterminer la database avec laquelle elle travaille et modifier la requête de manière appropriée.

Semblable à @OMGPonies peut également faire quelque chose comme:

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

Vous ne savez pas qui est le plus rapide ou même si vous vous souciez de la vitesse count tenu du nombre d'éléments que vous insérez.

Pas testé sur oracle mais je suis sûr à 90% que ça marchera.