DML Commands in SQL Server: INSERT (Part 2) -- Insert into a table from some other table

DML: Data Manipulation Language.


In the earlier session ( Part 1 for INSERT Command), we have discussed how to insert or store data into a table using simple commands.


Using the commands which were discussed in part1, only data can be inserted into table if we know the values. That is data can be inserted by explicitly specifying the values which needs to be stored.


Suppose we have a table  (TABLE_1) with all the required information and we want to insert data from that table (TABLE_1) to another table (TABLE_2), then this can be done by using the following approach:


Here is the syntax for inserting data from one table to another table:


INSERT INTO TABLE_2
(
     Column_1,
     Column_2,
     |             ,
     |             ,


     Column_N
)
SELECT
     Column_1,
     Column_2,
     |             ,
     |             ,


     Column_N
FROM TABLE_1
 Example:


Lets assume that I have lot of email accounts and I am maintaining different password for each account.


Here is the table I created to store my email ids and corresponding password:


CREATE TABLE MYPASSWORDS
(
            ACCOUNT                            VARCHAR(20),
            ACCOUNTID                        VARCHAR(100),
            PWD                                       VARCHAR(20)
)


and, I inserted following records as per the Insert syntax given in Part1.


INSERT INTO MYPASSWORDS values ('GMAIL','catchme@gmail.com', 'sghy639')
INSERT INTO MYPASSWORDS values('GMAIL','sqldeveloper@gmail.com', 'sqlpor739')
INSERT INTO MYPASSWORDS values('GMAIL','sqldeveloper@gmail.com', 'sqlpor739')
INSERT INTO MYPASSWORDS values('YAHOL','myyahooid@yahoo.com', 'olju67')
INSERT INTO MYPASSWORDS values('YAHOL','sqlmyid@yahoo.com', 'itryry67')


Data in MYPASSWORDS  table after executing the above queries is as follows:

Now, Lets say I have very huge data in MYPASSWORD table and I want to maintain a separate table for only my Gmail accounts. So, I created a table with the following structure:


CREATE TABLE MYGMAIL
(           ACCOUNTID                        VARCHAR(100),
            PWD                                        VARCHAR(20)
)

So, Now the table is ready and I want to insert data from MYPASSWORD Table into MYGMAIL table.

Instead of inserting records 1 by 1 using the insert query which was discussed in part1, its better to dump data from MYPASSWORD table into MYGMAIL table with a simple query:

Here is the query:

 
INSERT INTO MYGMAIL
(
            ACCOUNTID            ,
            PWD                          
)
SELECT        
            ACCOUNTID            ,
            PWD              
FROM            MYPASSWORDS
WHERE ACCOUNT='GMAIL'


After the execution of the above query, data in MYGMAIL table will be as follows:



















This is the simple way to insert data from 1 table to another table.

Summary:

In this article we have learnt how to insert data from one table to another table using INSERT Command.






This entry was posted in . Bookmark the permalink.

Leave a reply