Now we are going to see how to copy table into another table in Sql Server.
You might have sometimes faced a situation where you need to :-
1) Drop a table from database.
2) Copy table into a new table.
3) or Copy a table into another already created table with the same schema.
I am sure that every developer and programming geek would have faced these situations.
Here I will show how you can manage these things efficiently in your Sql Server database.
Now lets get straight to the point.
How to copy table into another table in Sql Server.
Suppose I have a table name Employee and I want to copy Employee table into new table which we have not created yet.
select * into staff from employee
Here a table name with staff gets automatically created with the same schema as of the Employee table. All the data of Employee table gets copied to the staff table. Here I will show you the design of table Employee and staff table.
You can also check the data inside both the table by using queries :-
select * from employee
select * from staff
One thing if you guys have noticed or not I want to tell you that if you have carefully looked at the above images of Employee table and staff table design the PRIMARY KEY of Id in Employee table is not copied in staff table. Now this is one way of copying a table into another new table which gets created at the time of copying. Isn’t it great guys I mean you have never created a table still it gets created at the time of copying table which saves a lot of query shoot into the Sql Server.
The new table will get created in the same database which means the staff table will get created into the database which contain the employee table.
Now there is also another way of copying data into another table. In this method unlike the previous one you have to create a table into which you want the data to get copied. So you have to create a table first with same schema and then you can copy the data. Ok just cut the crap and show me how is this done.
How to copy table data into another already created table.
So here is the query to do that :-
insert into emp2 select * from employee
It goes like this if you entered directly this query you will get “Invalid Object” error. So just do one thing, create a table emp2 first and then fire the above query and all data will get copied in emp2 table.
Sometimes we come across the situation where we want to drop the table but we do not want to lose the data in it. So we can do this to copy data from table to another table in Sql Server. We can also create script to do this but what will happen is that it will consume your time. So why waste time when we are only one query away from getting what we want in the coolest way ever. So please share this post from below with your developer friends and community to share the knowledge.
Latest posts by Amit Kulat (see all)
- Add Foreign Key Relationship from Table Designer in Sql Server - October 17, 2017
- Why Coding Will Make You An Awesome Geek - May 31, 2017
- 10 things you should know while working in .NET - May 26, 2017