SQL Server 2008 - DBOutputTable - Autogenerated keys

CloverDX Platform
1

Hi,

I’m evaluating Clover for a data migration project, moving data between two SQL Server 2008 databases.
As part of my testing I need to evaluate whether clover can migrate parent/child data between the two systems, whilst retaining the relationship. For my test I have a DBInputTable that reads the source parent records, and a DBOutputTable that inserts the mapped parents into the destination db. However I am getting stuck on having the DBOutputTable component return the new ids that the db has generated.

Does anyone have a solution to this problem?

Thanks i advance,

Dave Oram

2

Hello Dave,
if the key column is set as an IDENTITY you have to turn of generating the keys, before copying data from one database to another (phase 0 on the picture). Then copy data from one table to another (phase 1). Now you can turn off inserting the identity (phase 2). Loading new data after it, triggers the automatic key generation (phase 3). To make this graph working properly you have to uncheck “Thread-safe connection” box in Advanced properties of target db connection.
mssqlcopy.png

3

Thanks for that, however I realise I didn’t really highlight the main issue.

In your sample, and the online help the DBOutputWriter uses the syntax:

Insert into table(col1,col2) 
values ($col1, $col2)
returning $id:=auto_generated

This syntax is not acceptable for the SQL Server 2008 database I am using. I checked on wikipedia, and it appears valid for other databases such as Oracle but not SQL Server. Do you know what the equivalent SQL Server syntax is?

Cheers,
Dave

4

Hello Dave,
the example above I’ve tested with the MSSQL Server 2008 and it works properly. Do you have MSSQL jdbc specific in your connection definition (jdbcSpecific=MSSQL)? Is one of the columns defined as IDENTITY? Do you get an error?