How to insert table values from one database to another database?

sql

data-transfer

permissions

network-issues

byAnton Shumikhin·Aug 18, 2024

First, you need to target the correct database. To transfer data between databases on the same server, use the INSERT INTO ... SELECT construct. Here, new_db and existing_db are your new and existing database names while table_name, col1, col2, etc., are table and column names:

-- Insert a pirate joke here, if RDBMS is MySQL, it's My-SEA-quel!
INSERT INTO new_db.table_name (col1, col2)
SELECT col1, col2 FROM existing_db.table_name;

However, for cross-server transfers, you'd need to link the databases (make sure to replace dblink with the actual link name):

-- "Database link, I choose you!" says Ash Ketchum from Pallet Town.
INSERT INTO table_name@dblink (col1, col2)
SELECT col1, col2 FROM table_name;

Always include a WHERE clause to constrain the data while testing your queries for the first time.

Before you hit 'Run'

Many roadblocks can obstruct your smooth data transfer. So, let's gear up before we hit that 'Run' button!

Data types and table constraints

Ensure the data types and constraints of both databases align. Any mismatch can set off alarm bells!

Structure compatibility

Review the structure of both source and target databases. Don't forget to cross-check primary and foreign keys, collations, and index configurations.

Test Run on limited data

Test your queries on smaller data sets. Better safe than sorry!

Security and permissions

Ensure your databases are securely linked and the relevant permissions have been granted.

Advanced data transfer techniques

Master these advance moves, and you'll be a pro data alchemist in no time!

Data transfer in heterogeneous systems

To maneuver data between different RDBMS, use linked servers. Here's a quick demonstration for SQL Server:

-- Wow! Who knew SQL Server had EXECutive powers?
EXEC sp_addlinkedserver
    @server='DBLINK_NAME',
    @srvproduct='',
    @provider='SQLNCLI', -- Not your typical SQL Ninja
    @datasrc='TARGET_SERVER_NAME';

Solving the Oracle 'riddle'

If dealing with Oracle, do check if db2SID is in the tnsnames.ora file.

Embracing OPENDATASOURCE

When linked servers aren't available, SQL Server lets you use OPENDATASOURCE:

-- Like API calls, but for databases. Mind = blown!
INSERT INTO new_db.dbo.table_name (col1, col2)
SELECT col1, col2 
FROM OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI;Initial Catalog=existing_db')
    .dbo.table_name;