Introduction
This article series is about the various SQL tips/techniques that I came across in my professional work. It will be very silly to someone who is an expert in SQL Server. But this article discusses some of the tips/techniques that I encountered when I tried to solve problems in SQL in my day-to-day programming life. I will be updating this series whenever I come across various problems which may not be easily accessible in the internet or you may not find the result in google search!
Copying Table to New Table
In our very first tip we will discuss creating a replica of a table in SQL with data and constraint/keys. Often we come across situation in which we may be working on enhancements/technical bugs/improvements in our products which may need to alter/change table data/table structures. Apart from us, many other developers may be working on the same table. Sometimes, we may do trial and error of changing the table data/structure. We may wish that other developers are not affected with the changes. We can copy the content/structure of the table to a new one and experiment in the new table, do whatever changes we want and finally make change to the original table.
A Brief Example
We have a table named as EmployeeDetails and EmployeeDesignation. The content of the tables are as shown below.
Let us consider we want to make changes in the EmployeeDetailsTable and don't want other developers to be affected. For this we need to copy the table data with the table structure. A simple SELECT * INTO EmployeeDetails1 FROM EmployeeDetails will only copy the data not the structure where EmployeeDetails1 is the dummy table.
The structure of the EmployeeDetails table is shown below. The table has primary key/foreign key constraints with different column names and datatypes.
To copy the above table structure with data - we need to copy the table structure with constraints, keys, indexes to the new table along with data. This can be done in two steps:
- Copy the table structure to a new table
- Copy the table data to new table
1. Copying table structure
Click on the table that you want to create a copy and do the operation as shown in the screenshot
A script will be generated as below. After successfuly completion of script generation, paste the script in a query window. it will look as below.
Rename the EmployeeDetails name to EmployeeDetailsTry. The EmployeeDetailsTry is a table on which the developer is going to experiment his changes. Run the script and table structure for new table will be as below.
Execute the script. New table will be created. The table structure will look as below
We have the desired table with the structure and constraints. Next step is to copy the data.
2. Copying the data
Execute below query to copy the table content
The final result will be as below with EmployeeDetailsTry having structure and data as EmployeeDetails table.
Now you can work on the EmployeeDetailsTry table, note the changes you have made and apply to the original table once you are satisfied with the change/modifications you have done.
Thanks
I would like to thank Mohankumar Chendurapandian of my team for his kind suggestions.