I want to run following sql query based on two differend databases as below:
Code: Select all
hDB1Query =
"CREATE TABLE table1 (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, str_value nvarchar(50), comnfild varchar(8));"
hDB1Query + #CRLF$ +
"INSERT INTO table1 (id, str_value, comnfild) VALUES (1, 'Value 1', 'aaaa1111'), (2, 'Value ?', 'aaaa1122'), (3, 'Value 3', 'aaaa1145');
hDB2Query =
"CREATE TABLE table2 (id2 INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, str_value2 nvarchar(50), comnfild2 varchar(8));"
hDB2Query + #CRLF$ +
"INSERT INTO table2 (id2, str_value2, comnfild2) VALUES (1, 'Value 1', 'aaaa1111'), (2, 'Value 2', 'aaaa1133'), (4, 'Value 4', 'aaaa1145');"
hDBCrossQery =
"insert into table1(str_value, comnfild)
select a.str_value2, a.comnfild2 from table2 a left join table1 b on a.comnfild2 = b.comnfild where b.comnfild is NULL; " + #CRLF$ +
"insert into table2(str_value2, comnfild2)
select a.str_value, a.comnfild from table1 a left join table2 b on a.comnfild = b.comnfild2 where b.comnfild2 is NULL;"
how to run hDBCrossQery which affects both databases natively based on sql without walking each record using PureBasic Database commands like "While NextDatabaseRow"