Migrating data from MongoDB to Microsoft SQL Server involves multiple steps and typically requires scripting or programming to handle the data extraction, transformation, and loading (ETL) process. Here's an example of Python code that uses the `pymongo` library to connect to MongoDB and the `pyodbc` library to connect to SQL Server for a basic migration:
```python
import pymongo
import pyodbc
# MongoDB connection settings
mongo_uri = 'mongodb://username:password@hostname:port/database_name'
mongo_client = pymongo.MongoClient(mongo_uri)
mongo_db = mongo_client['mongodb_database']
mongo_collection = mongo_db['mongodb_collection']
# SQL Server connection settings
sql_server_connection_string = 'Driver={SQL Server};Server=server_name;Database=database_name;Uid=username;Pwd=password'
sql_server_connection = pyodbc.connect(sql_server_connection_string)
sql_server_cursor = sql_server_connection.cursor()
# Query MongoDB data
mongo_data = mongo_collection.find()
# Define SQL Server table schema and insert SQL statement
table_name = 'sql_server_table'
create_table_sql = '''
CREATE TABLE {} (
id INT PRIMARY KEY,
name NVARCHAR(255),
age INT
)
'''.format(table_name)
insert_sql = '''
INSERT INTO {} (id, name, age) VALUES (?, ?, ?)
'''.format(table_name)
# Create SQL Server table
sql_server_cursor.execute(create_table_sql)
sql_server_connection.commit()
# Migrate data from MongoDB to SQL Server
for document in mongo_data:
id = document['id']
name = document['name']
age = document['age']
sql_server_cursor.execute(insert_sql, (id, name, age))
sql_server_connection.commit()
# Clean up resources
mongo_client.close()
sql_server_connection.close()
```
This code provides a basic outline for migrating data from MongoDB to SQL Server using Python. Please note that this code is a starting point and may need to be adapted to your specific use case. You should also consider error handling, data validation, and performance optimization depending on the size and complexity of your data.
Make sure to install the required Python libraries (`pymongo` and `pyodbc`) using `pip` if they are not already installed. Additionally, replace the placeholder values (e.g., `mongodb://username:password@hostname:port/database_name`, `Driver={SQL Server};Server=server_name;Database=database_name;Uid=username;Pwd=password`, and table/column names) with your actual connection and schema details.