"Copy table to" without modifying destination table scheme

Completed
Avatar
lufist
Created

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?

Votes

1

Share

4 comments
Avatar
lufist
Created

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
Avatar
vasily chernov
Created

@lufist,

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

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

0
Avatar
lufist
Created

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
Avatar
vasily chernov
Created

@lufist,

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

 

0

Please sign in to leave a comment.