Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.




SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)



About Carlos Ferreira


  1. i have 33 tables for 1 day for example c1_00,c1_01,…
    i must find all from all_tables then create temp_table and insert them to new table
    please help me

    • Hi Mahboobe-

      Thanks for visiting! Its tough to give direct advice without the specifics but I hope the the following will help:

      CREATE TABLE #test
      col1 varchar(max)

      exec sp_MSforeachtable
      @command1 = ‘INSERT INTO #test select col1 from ?’,
      @Whereand = ‘ and like ”c1_%”’,
      @postcommand = ‘INSERT into result (col1) SELECT col1 from #test; drop table #test’

      Just to explain this a bit further: The sp_Msforeachtable procedure will iterate thru all the tables in the current database and dynamically build an sql statement that is listed in @command1. It will substitute the table name where you will see ‘?’. As it is iterating over the tables, it is extracting the data from each table and inserting it into a single temporary table. The @whereand provides a filter on the table names that this command is executed on. In this case is is filtering the tables to the names that look like ‘c1_’. Finally when everything is completed it executes the command in the postcommand variable. It will in essence copy the data from the temporary table into the final resulting table and then drop the temporary table.

      I hope this helps!


  2. Muhammad Nadeem AKhter says

    i want to insert my data in sql server with plsql ……..kindly give me some idea how i insert plsql code running on
    table name is data_table
    std_id char:=’4′;
    std_name varchar2(200):=’Smith’;
    std_location varchar2(200):=’England’;
    insert into data_table(data_id,data_name,data_location) values (std_id,std_name,std_location)

    • Hi Muhammad – try something like this:

      CREATE TABLE data_table(
      std_id char NOT NULL,
      std_name varchar2(200) NOT NULL,
      std_location varchar2(200) NOT NULL
      insert into data_table(std_id, std_name, std_location) values (‘4’, ‘Smith’, ‘England’);
      select * from data_table

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: