dimanche 29 janvier 2017

How to populate a mssql table continuosly with the data of another table

I am struggling to convert an oracle procedure to mssql one (unfortunately without mssql knowledge), basicly the case is like this; assume that I have a table named as HISTORIC_TABLE with constant rows of data, and I have another table named as REAL_TIME_TABLE (totally empty at the begining). I am trying to generate transactions on REAL_TIME_TABLE by fetching rows in HISTORIC_TABLE randomly and slowly. Here the oracle equivalence of the scenario. Any help would be really appreciated.

`

create or replace procedure test.p_replicate_data() 
is
cursor c1 is 
SELECT *  FROM TEST.HISTORIC_TABLE ORDER BY DBMS_RANDOM.RANDOM;

r1 c1%rowtype;

cmd_sql varchar2(10000);
cmd_values varchar2(10000);

begin 

for r1 in c1 loop

cmd_sql := '';
cmd_values := '';

--start the command sql
cmd_sql := 'INSERT INTO TEST.REAL_TIME_TABLE (' ;

cmd_sql := cmd_sql || 'COL1, ';
cmd_sql := cmd_sql || 'COL2, ';
cmd_sql := cmd_sql || 'COL3, ';
cmd_sql := cmd_sql || 'COL4';


cmd_sql := cmd_sql || ') values (' ;

--prepare the values string
cmd_values := cmd_values || '''' || r1.COL1 || ''',';
cmd_values := cmd_values || '''' || r1.COL2 || ''',';
cmd_values := cmd_values || '''' || r1.COL3 || ''',';
cmd_values := cmd_values || '''' || r1.COL4 || '''';

cmd_sql := cmd_sql || cmd_values;

cmd_sql := cmd_sql || ')';

execute immediate (cmd_sql);
commit;

dbms_lock.sleep(0.05);

end loop;
end;`




Aucun commentaire:

Enregistrer un commentaire