Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
We know how to create a table using the create table statement. A table statement creates a blank table structure where the user must manually fill up data. But imagine a situation where there are hundreds of rows in a table, and you need to create a copy of the table with all the rows inside. There are several techniques on how to do that. One of them is to use the Insert into a Select statement. Insert into select statement allows not only copying data and table structure from an existing table but can also create blank table structures.
Over the following few paragraphs, we will learn all about copy data from one table to another sql.
SQL Copy from one table to Another
There are different ways of copying data from one table to another.
For this example, we will consider the AdventureWorks database. We will take the HumanResource.Employee table.
The query to view the data is below
select * from [HumanResources].[Employee]
The dataset looks like below
Now let us create another table, say Employee1. The script for creating the table is as below.
CREATE TABLE [HumanResources].[Employee_Example]( [BusinessEntityID1] [int] NOT NULL, [NationalIDNumber] [nvarchar](15) NOT NULL, [LoginID] [nvarchar](256) NOT NULL, [OrganizationNode] [hierarchyid] NULL, [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()), [JobTitle] [nvarchar](50) NOT NULL, [BirthDate] [date] NOT NULL, [MaritalStatus] [nchar](1) NOT NULL, [Gender] [nchar](1) NOT NULL, [HireDate] [date] NOT NULL, [SalariedFlag] [dbo].[Flag] NOT NULL, [VacationHours] [smallint] NOT NULL, [SickLeaveHours] [smallint] NOT NULL, [CurrentFlag] [dbo].[Flag] NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_Employee_BusinessEntityID1] PRIMARY KEY CLUSTERED ( [BusinessEntityID1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
The output looks like the one below.
Next, we copy data from the Employee table.
Open the new table in edit mode.
Select any blank row in the new table and paste the copied data.
SQL Copy From One Table To Another
Let us again take the case of the Adventurework database and the same Employee table. We will create the table and copy the data using a single query this time.This time we will copy the Employee table to another table called Employee_Example1.Here is the query.
select * into Employee_Example2 from [HumanResources].[Employee]
The output looks like the one below.
The data in the new table looks like this below.
select * from Employee_Example2
The data looks like this below.
Copy Date From One Table To Another Sql Server
There is another way to copy data from one table to another. This time you have to create the table before and then copy the data from the old table to the new one.
The query to copy data is as below
INSERT INTO [HumanResources].[Employee_Example] (BusinessEntityID1, NationalIDNumber, LoginID, OrganizationNode, JobTitle, BirthDate, MaritalStatus, Gender,HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate) SELECT BusinessEntityID, NationalIDNumber, LoginID,OrganizationNode, JobTitle,BirthDate, MaritalStatus, Gender, HireDate, SalariedFlag, VacationHours, SickLeaveHours, CurrentFlag, rowguid, ModifiedDate FROM [HumanResources].[Employee] The output is as below.
The sample dataset is as below.
select * from [HumanResources].[Employee_Example]
The output is as below.
The advantage of copy table sql is that if the insert fails, there will be no physical harm in the resulting table.
The disadvantage of copy table SQL is that it truncates the copied data without warning if the field size of the target table does not match the source table.
SQL Testing Training
The blog describes how to copy data from one table in a database to another. This discusses the different techniques of copying data and its advantages and disadvantages. This sheds light on the different methods of copying data and give basic idea on how to copy data from one sql server to another. We hope this will generate interest among readers who want to learn more about sql insert query.
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment