Black Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook  - SCHEDULE CALL

sddsfsf

A Detailed Study on Copy Table in SQL

Introduction

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. 

  1. Direct Copy From One Copy 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.

  1. 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.

  1. 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.

Advantage of Copy Table SQL

The advantage of copy table sql is that if the insert fails, there will be no physical harm in the resulting table.

Disadvantage of Copy Table SQL

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.

cta14 icon

SQL Testing Training

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Conclusion

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.

Trending Courses

Cyber Security icon

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security icon1

Upcoming Class

12 days 14 Dec 2024

QA icon

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA icon1

Upcoming Class

-0 day 02 Dec 2024

Salesforce icon

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce icon1

Upcoming Class

8 days 10 Dec 2024

Business Analyst icon

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst icon1

Upcoming Class

11 days 13 Dec 2024

MS SQL Server icon

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server icon1

Upcoming Class

11 days 13 Dec 2024

Data Science icon

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science icon1

Upcoming Class

4 days 06 Dec 2024

DevOps icon

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps icon1

Upcoming Class

4 days 06 Dec 2024

Hadoop icon

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop icon1

Upcoming Class

4 days 06 Dec 2024

Python icon

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python icon1

Upcoming Class

19 days 21 Dec 2024

Artificial Intelligence icon

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence icon1

Upcoming Class

12 days 14 Dec 2024

Machine Learning icon

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning icon1

Upcoming Class

25 days 27 Dec 2024

 Tableau icon

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau icon1

Upcoming Class

4 days 06 Dec 2024