Copy tables from one database to another in SQL Server, using Python

Question

Does anybody know of a good Python code that can copy large number of tables (around 100 tables) from one database to another in SQL Server?

I ask if there is a way to do it in Python, because due to restrictions at my place of employment, I cannot copy tables across databases inside SQL Server alone.

Here is a simple Python code that copies one table from one database to another. I am wondering if there is a better way to write it if I want to copy 100 tables.

print('Initializing...')

import pandas as pd
import sqlalchemy
import pyodbc

db1 = sqlalchemy.create_engine("mssql+pyodbc://user:password@db_one")
db2 = sqlalchemy.create_engine("mssql+pyodbc://user:password@db_two")

print('Writing...')

query = '''SELECT * FROM [dbo].[test_table]'''
df = pd.read_sql(query, db1)
df.to_sql('test_table', db2, schema='dbo', index=False, if_exists='replace')

print('(1) [test_table] copied.')

are you sure all tables from the source DB will fit into RAM on the machine where you are going to run your Python script? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Oct 25, 2017 at 21:35
store table names in a simple text file. Read the table name one at a time and then use the above script to copy one by one. You can print some info in between to log the progress. Even better, there can be a central machine which holds the list. You can have other worker machines which will read each table name individually and copy it that way you will distribute the work. But destination DB should be able to handle the incoming load as well. — Albatross
– Albatross, Commented Oct 25, 2017 at 21:35
@Confused can you show me a sample code or tell me how python would read in a text file? thank you so much!!! im seriously new to python — pynewbee
– pynewbee, Commented Oct 25, 2017 at 21:38
Please refer (stackoverflow.com/questions/3277503/…). You can ignore the list part. — Albatross
– Albatross, Commented Oct 26, 2017 at 18:38

Muposat · Accepted Answer · 2017-10-25 21:49:15Z

12

SQLAlchemy is actually a good tool to use to create identical tables in the second db:

table = Table('test_table', metadata, autoload=True, autoload_with=db1)
table.create(engine=db2)

This method will also produce correct keys, indexes, foreign keys. Once the needed tables are created, you can move the data by either select/insert if the tables are relatively small or use bcp utility to dump table to disk and then load it into the second database (much faster but more work to get it to work correctly)

If using select/insert then it is better to insert in batches of 500 records or so.

answered Oct 25, 2017 at 21:49

Muposat

1,5061 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MaxU - stand with Ukraine Over a year ago

"This method will also produce correct keys, indexes, foreign keys." +1

Mateus Felipe Over a year ago

I ditn't get the part of select/insert. How can I do this succintly with SQLAlchemy?

Muposat Over a year ago

@Mateus Felipe: something like this: for rec in select(table1): table2.insert(rec). This is best if discussed as a separate question. Make sure to post a code sample if you do.

Mateus Felipe Over a year ago

@Muposat Thanks for your answer! I actually searched a little more and found this: stackoverflow.com/questions/21770829/…

MaxU - stand with Ukraine · Accepted Answer · 2017-10-25 21:44:21Z

3

You can do something like this:

tabs = pd.read_sql("SELECT table_name FROM INFORMATION_SCHEMA.TABLES", db1)

for tab in tabs['table_name']:
    pd.read_sql("select * from {}".format(tab), db1).to_sql(tab, db2, index=False)

But it might be be awfully slow. Use SQL Server tools to do this job.

Consider using sp_addlinkedserver procedure to link one SQL Server from another. After that you can execute:

SELECT * INTO server_name...table_name FROM table_name

for all tables from the db1 database.

PS this might be done in Python + SQLAlchemy as well...

answered Oct 25, 2017 at 21:44

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Collectives™ on Stack Overflow

Copy tables from one database to another in SQL Server, using Python

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related