MIGRATING TABLES AND DATA FROM ONE DATABASE TO ANOTHER IN SSMS

In this blog, I will be writing on how to transfer/copy data and tables from one database to another.

There are methods or terminologies that seem difficult at a first look but are very easy on a detailed look. That is why I will always advice you to make google your best friend in this journey. There are many things I have learnt independently from google either on a job or for personal use which I never learnt in the courses I have taken.

Over the week, I was asked to copy multiple tables from different databases into a newly created database in SQL. This led me to also know that you can automatically generate a data script without having to write any query which I will discuss in a later blog.

Let’s assume we have two databases already created named Customers and Products which have two tables each named customers, custom_address and products, product_address in that order and we want to migrate the data to a new database called CustomerProduct, there are two methods that could be used to do this:

1. Using the import/export wizard

• Create a new database called CustomerProduct in SSMS
• Launch the SQL Server import and export wizard
• Choose SQL Server Native Client 11.0 both as your data source and destination (under the data source page, pick the database named Customers and on the destination page, pick the database named CustomerProduct)
• Select the two tables to be copied
• Repeat the same procedure but with the database named Products picked on the data source page.

2. Writing a simple and short query (I personally love and prefer this method)

Using the same assumption above all you need to do this:

• Create a new database called CustomerProduct in SSMS and write the query below:

select * into CustomerProduct.dbo.customers from Customers.dbo.customers; (This will copy the data from customers table in database Customers to a table named customers in database CustomerProduct)

select * into CustomerProduct.dbo.custom_address from Customers.dbo.custom_address; (This will copy the data from custom_address table in database Customers to a table named custom_address in database CustomerProduct)

select * into CustomerProduct.dbo.products from Products.dbo.products; (This will copy the data from products table in database Products to a table named products in database CustomerProduct)

select * into CustomerProduct.dbo.product_address from Products.dbo.product_address; (This will copy the data from product_address table in database Products to a table named product_address in database CustomerProduct)

By the time this is finished for either step 1 or step 2, you will have a database named CustomerProduct with four tables.

P.S: It’s not compulsory that the name of the tables in the new database are named the same way as in the database to be copied from. You can change the name of the table by writing the query differently.

For example, if I wanted the name of the custom_address table in the Customers database to be named “contact_details” in the CustomerProduct database, the query will be written thus:

select * into CustomerProduct.dbo.contact_details from Customers.dbo.custom_address; (This will copy the data from custom_address table in database Customers to a table named contact_details in database CustomerProduct)

BASIC HINTS:

1. SSMS means SQL Server Management Studio
2. SQL is case sensitive especially when naming tables and databases(i.e. Customers is different from customers)
3. dbo is a prefix to identify a table in SSMS.

If you feel the need to ask questions for clarity, you can send a mail @[email protected] or contact 09021833246.