SQL Server 2008 – How to copy a Table

Lets see how to copy an existing table to new table in SQL Server. There are two options.

• Copy the table with Data.
• Copy the table without data (i.e.Table Structure Only)

-- Create Table 

CREATE TABLE EMPLOYEE
(
    ID                INT NOT NULL,
    FIRST_NAME        VARCHAR(10),
    LAST_NAME         VARCHAR(10)
);

 

-- INSERT SOME VALUES INTO TABLES

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH');

 

--CREATE A COPY OF EMPLOYEE TABLE WITH DATA 

SELECT * INTO EMPLOYEE_BACKUP FROM EMPLOYEE

 

-- CREATE A COPY OF EMPLOYEE TABLE WITHOUT DATA

SELECT * INTO EMPLOYEE_BACKUP1 FROM EMPLOYEE WHERE 1 = 0;

 

Note: This way simply you can copy a table into another (new) table in the same SQL Server database. This way of copying does not copy constraints and indexes.