"Copy table to" without modifying destination table scheme

Completed

I love the feature "copy table to" for migrating content from or legacy database to a new database.

The new scheme contains two additional columns "DateCreated" and "DateModified" which have to be != NULL.

When using the "copy table to"-feature I am able to modify the scheme of the destination table, so that there is a default value "now()". This works, but I'd like to leave the scheme as it is and set a default value only for the migration.

How can I achieve that?

4 comments
Comment actions Permalink

@lufist,

If I got it right, you need to change column mapping

Remove any value from `Mapped to`, set `Default` to now()

0
Comment actions Permalink

Thank you for your response.

 Yes, this would work, but I am not allowed to do an "ALTER TABLE" on my database scheme. Therefore I would need an option do set the default value for the migration, not on the scheme.

0
Comment actions Permalink

@lufist,

Then you need to ask DBA to create a temporary trigger or rule for proper migration and then disable it.

 

0
Comment actions Permalink

I found out how to copy the rows to another table without having to change anything to the scheme.

In our case we want to copy the rows to a table which has two additional columns called "DateCreated" and "DateModified". I execute the following query:

SELECT *, 
NOW() AS DateCreated,
NOW() AS DateModified FROM SourceTable;

In the results window, there is a button which allows to copy the result to another table:

Then the copy procedure runs through smoothly without having to make any changes to the scheme.

1

Please sign in to leave a comment.