Menu
What would be the best way to copy data from one table, one database, one server to the table in another database, another server in PostgreSQL?
474
PostgreSQL makes it simple to do so using the CREATE DATABASE statement as follows: Syntax: CREATE DATABASE target_database WITH TEMPLATE source_database; This statement copies the source_database to the target_database.
Select and right-click on the Source Database, go to Tasks > Export Data. Import/Export Wizard will be opened and click on Next to proceed. Enter the data source, server name and select the authentication method and the source database. Click on Next.
Using SQL Server Management StudioOpen the table with columns you want to copy and the one you want to copy into by right-clicking the tables, and then clicking Design. Click the tab for the table with the columns you want to copy and select those columns. From the Edit menu, click Copy.
To copy a table with partial data from an existing table, users can use the following statement: Syntax: CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition; The condition in the WHERE clause of the query defines which rows of the existing table will be copied to the new table.
pg_dump allows the dumping of only select tables:
pg_dump -Fc -f output.dump -t tablename databasename
(dump 'tablename' from database 'databasename' into file 'output.dump' in pg_dumps binary custom format)
You can restore that dump on your other server with pg_restore:
pg_restore -d databasename output.dump
If the table itself already exists in your target database, you can import only the rows by adding the --data-only flag.
171
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
