How to copy table between databases in PostgreSQL

Galaxy Glossary

How do I copy a table between SQL Server databases quickly?

Copying a table between databases moves structure and/or data from one database to another within SQL Server.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why would I copy a table between databases?

Move data to a new environment, split monoliths, create reporting databases, or back-up critical tables without full restores.

What is the quickest same-server approach?

Use SELECT INTO with fully-qualified names to create the table in the target database and populate it in one step.

Example

SELECT *INTO ReportingDB.dbo.CustomersFROM ProductionDB.dbo.Customers;

How do I copy into an existing table?

Pre-create the table, then run INSERT INTO ... SELECT.This preserves custom definitions, constraints, or indexes.

Example

INSERT INTO ReportingDB.dbo.Customers (id,name,email,created_at)SELECT id,name,email,created_atFROM ProductionDB.dbo.Customers;

How can I copy between different SQL Server instances?

Create a linked server or use OPENROWSET. Then reference the four-part name that includes the linked server.

Example

SELECT *INTO [REMOTE].SalesDB.dbo.OrdersFROM LocalDB.dbo.Orders;

How do I migrate indexes and constraints?

Script the table using SSMS (right-click ➜ Script Table as ➜ CREATE To).Run the script in the target database before the data copy.

Best practices for large tables?

Copy in batches with TOP (N) or a date filter, disable non-clustered indexes, and enable minimal-logged operations by using SIMPLE recovery.

Common mistakes to avoid

Unqualified names: Omitting the database name writes data to the source database again. Always include TargetDB.Schema.Table.

Identity insert errors: Copying data with identity columns fails if the identity property is set.Add SET IDENTITY_INSERT TargetDB.dbo.Table ON for explicit identity values.

What tools can help?

SSMS Import/Export Wizard, BCP, and SSIS offer GUI or command-line alternatives when T-SQL is not enough.

Key takeaways

Use SELECT INTO for quick cloning, INSERT INTO ... SELECT for existing tables, and linked servers for cross-instance copies. Always script constraints first.

Why How to copy table between databases in PostgreSQL is important

How to copy table between databases in PostgreSQL Example Usage


--Copy all orders and related items to an archive DB
SELECT *
INTO ArchiveDB.dbo.Orders
FROM SalesDB.dbo.Orders;

INSERT INTO ArchiveDB.dbo.OrderItems (id,order_id,product_id,quantity)
SELECT id,order_id,product_id,quantity
FROM SalesDB.dbo.OrderItems;

How to copy table between databases in PostgreSQL Syntax


--Create and populate a new table in another DB
SELECT *
INTO TargetDB.dbo.TableName
FROM SourceDB.dbo.TableName;

--Insert into an existing table
INSERT INTO TargetDB.dbo.TableName (col1, col2, ...)
SELECT col1, col2, ...
FROM SourceDB.dbo.TableName;

--Between servers (linked server)
SELECT *
INTO [LinkedSrv].TargetDB.dbo.TableName
FROM LocalDB.dbo.TableName;

Common Mistakes

Forgetting to include the target database name causes data to be written back to the source. Always qualify objects as TargetDB.Schema.Table.
Copying tables with identity columns without SET IDENTITY_INSERT ON leads to insert errors. Enable identity insert or exclude identity columns.