replicate a table from one sql datasource to another?
m.ardito Feb 17, 2016 12:44 PMHi,
I have a multisource dynamic vdb with just two models
- one connected to a mssqlserver datasource A
- one connected to a mysqlserver datasource B
with full rights on both.
I would like to "replicate" a table from A to B, keeping the same exact structure.
This could be needed at least once a day (overnight), the more, the better.
The target table could be from few thousands rows to half a million
I digged a bit in to possible uses of:
- SELECT INTO syntax but while it works with simple statements like "select 1 as id into A.test" (still quite unuseful to me), docs say it's deprecated
- INSERT INTO, with something like "INSERT INTO A.tablename (field1,field2) VALUES (value1,value2)"an dthis also works (pk not insereted, autoincrementing)
I never thought of useing Teiid like this, up to now, but what I wish if possible, would be to keep the B.table as soon as possible in sync with A.table
I know there are other solutions to do db sync (like SymmetricDS, eg) but I can't touch the A source (so I can't insert triggers there..)
Is this possible, atm, and what is the best approach in Teiid, if yes?
(probably related to Insert Into Support)
I also tried this
insert into dbtestB.table (DocumentDate, AccrualDate, LogNo, DocNo,FeeId)
select DocumentDate, AccrualDate, LogNo, DocNo,FeeId from dbtestA.table
but I get
Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql5_dbtest: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@290f7995[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@55f98493 connection handles=0 lastUse=1455730804545 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@706efafd pool internal context=SemaphoreArrayListManagedConnectionPool@4a90a993[pool=dbtest] xaResource=LocalXAResourceImpl@2448dc28[connectionListener=290f7995 connectionManager=20381dfd warned=false currentXid=null productName=MySQL productVersion=5.1.63-0ubuntu0.10.04.1-log jndiName=java:/dbtest] txSync=null]
SQLState: 50000
ErrorCode: 30504
Thx