SQL SERVER: SELECT INTO Statement to copy data from one table and insert into new table

Introduction: In this article I am going to share how to copy/select data from all or selected columns of one table and insert into a new  table in SQL. 


Description: The SELECT INTO statement selects/copies data from one table (Source) and inserts it into a new table (Destination).

Implementation: Let’s create a sample table and perform the desired operation.

  --Create a table(Source)
GO
CREATE TABLE tbBooks
(
    BookId        INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    BookName   VARCHAR(100),
    Author         VARCHAR(100),
    Publisher      VARCHAR(100),
    BookPrice     DECIMAL(10,2)
)

--Add some dummy data into the table
GO
INSERT INTO tbBooks VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Sahil','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya Publication',800)

 --Check inserted data
SELECT * FROM tbBooks 

BookId
BookName
Author
Publisher
BookPrice
1
Asp.Net
Ajay
Rozy Publication
1200.00
2
C#.Net
Sahil
Jai Publication
1000.00
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00
6
PHP
Parvesh
Maya Publication
800.00

Note: We can copy data from all or any number of columns from one table and insert into a new table that will be created automatically.

Copy all data from one table and insert into a new table:


SELECT * INTO tbSelectedBooks FROM tbBooks

Note: A new table "tbSelectedBooks" will automatically be created with the column names and data types taken from the SELECT statement. All the data from source table(tbBooks) will get copied into destination table(tbSelectedBooks).

--Check inserted data

SELECT * FROM tbSelectedBooks

BookId
BookName
Author
Publisher
BookPrice
1
Asp.Net
Ajay
Rozy Publication
1200.00
2
C#.Net
Sahil
Jai Publication
1000.00
3
VB.Net
Nancy
Rozy Publication
970.00
4
MVC
Sahil
Amar Publication
1480.00
5
JAVA
Supreet
Sam Publication
850.00
6
PHP
Parvesh
Maya Publication
800.00


Copy only certain columns data from one table and insert to a new table:


SELECT BookName, BookPrice INTO tbBookList FROM tbBooks

--Check inserted data

SELECT * FROM tbBookList

BookName
BookPrice
Asp.Net
1200.00
C#.Net
1000.00
VB.Net
970.00
MVC
1480.00
JAVA
850.00
PHP
800.00


Copy data from one table and insert to new table based on condition:

Suppose we want to copy and insert only those records from tbBooks table where Book price is greater than 1000 then the query will be as:


SELECT BookName, BookPrice INTO tbBookListDemo FROM tbBooks WHERE BookPrice >1000

--Check inserted data
SELECT * FROM tbBookListDemo

BookName
BookPrice
Asp.Net
1200.00
MVC
1480.00


Change column names in the new table to be created:

By default SELECT INTO Statement creates a new table with the same column names as defined in source table. But we can change the name using AS clause:

SELECT BookName, BookPrice AS Price, Author AS AuthorName INTO tbBookData FROM tbBooks

SELECT * FROM tbBookData

BookName
Price
AuthorName
Asp.Net
1200.00
Ajay
C#.Net
1000.00
Sahil
VB.Net
970.00
Nancy
MVC
1480.00
Sahil
JAVA
850.00
Supreet
PHP
800.00
Parvesh

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..