MS SQL Server 7 - Copy Data from one table to another without using create and insert statement

Hello All,

welcome to SQL Server article, In this article  we will copies data from one table into a new table without create and insert.

1. Create database MyLearning_DB

create database MyLearning_DB

2. Connect to created database

use MyLearning_DB

3. Create table called Course

create table Course
(
	Id int primary key identity(1,1),
	title varchar(100),
	status varchar(20)
)

4. Insert some records to the table Course

insert into course values('c sharp','active')
insert into course values('java','active')
insert into course values('ms sql server','in-active')

5. Get all inserted records

select * from course

6. Next, lets create new table called Course_Bkp and insert all the records from Course

select * into Course_Bkp from course

Note: We are not using any create statement to create the table and insert statement to insert the records. 

We are using SELECT * INTO statement  to create a table from an existing table by copying the existing table's columns and data.

SELECT * INTO <<NEW_TABLE_NAME>> FROM COURSE

When using the SELECT INTO statement in SQL Server, the new_table must not already exist. If it does already exist, the SELECT INTO statement will raise an error.

Lets view the table and data...

select * from Course_Bkp

7. Below query will help us to create the table with columns not data.

select * into Course_Bkp_Only_Columns from course where 1=0

Get the data now

select * from Course_Bkp_Only_Columns

Above query help us to get create the table with column without data, because 1 never equal to 0.

8. Next, We will create the table with specific column and insert the data. 

select id,title into Course_Bkp_Copy_Specific_Col from course

In the above query we are creating the table with two columns id and title and finally inserting data to that.

Lets get the data...

select * from Course_Bkp_Copy_Specific_Col

Great! We are done

That's it for this article, see you in next article with interesting concepts.