Copy Data from a table in one Database to another separate database

Action Plan for Safe Data Migration

Pre-flight Checks: Are We Good to Go?

Before you begin the migration, make sure to do a thorough check:

• Verify you're the 'boss' of both databases. Have the permissions to READ from the source and WRITE on the destination.
• Check whether there are any constraints such as foreign keys or indexes in the destination table. Skipping this step is like forgetting your parachute — it’s a real drop in excitement!
• Confirm that the destination table is ready and waiting. If not, create it similar to the source to ensure a smooth landing.
• Test drive the process with a small dataset first. Consider it as your mini private rehearsal.
• Backing up both databases before the migration is like getting a good insurance policy – not all heroes wear capes!

Taking Off: Real Deal Time

Now to perform the migration while maintaining data integrity:

• Use transactions to safeguard your data transfer. If anything gets messed up, you've got an eject button ready!

BEGIN TRANSACTION;

-- Insert statement, in case of a hiccup, we have a rollback!

COMMIT TRANSACTION;


• Got identity columns in your table? Keep those distinct values intact by setting IDENTITY_INSERT on for the destination table.

SET IDENTITY_INSERT TargetDB.dbo.TargetTable ON;

-- Insert statements goes here 
-- Just like how Google knows you, keep your table's identity!

SET IDENTITY_INSERT TargetDB.dbo.TargetTable OFF;


• Take full advantage of the four-part naming convention for clearer operations. This is like writing your full postal address — no scope for the postman (or server in this case) to get confused!

[ServerName].[DatabaseName].[Schema].[Table]


Post-flight Checks: All Landed Well?

After the migration, ensure to:

• Take a stroll and audit your tables to ensure all data has been transferred successfully. Make sure no data decided to take a day off!
• Reactivate any turned-off triggers or constraints to re-establish your database rules. Back to normal, everyone!
• Evaluate performance metrics as a heavy migration could be a workout for your database. Do you need to tweak anything?

Extra Measures: For the Tough Times

• If dealing with a mountain of data, consider bulk operations like BULK INSERT — more muscle, more power!
• If you’re regularly transferring data, think about an automated process with SQL Server Agent jobs. Sit back and let the code do the work!
• For advanced ETL processes or intricate scenarios, fancy tools like SQL Server Integration Services (SSIS) could become your best pals!

Common Issues & Their Fixes

Here's how you can tackle common transfer problems — problems are just solutions in disguise!

Permission Denied!

Encountering a permission error? Double-check you have the correct read-write rights on both databases. Feeling powerless? Reach out to your DBA!

Constraints Blocking You?

Integrity constraints causing a hitch? Inspect the destination schema. Unresolved inconsistencies are like unopened choc-chip cookies, enjoyable when tackled right!

Transfer Not Efficient?

Depending on the size and complexity, INSERT INTO... SELECT may not cut it. For large datasets, consider bulk operations or SSIS packages.

Performance Dropped?

Beware of the heavy data transfer slowing down your system. Off-peak hours or batch processing can come to your rescue! Remember, SQL Server is not Flash!