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.

T-SQL:

PL/SQL:

 

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)

T-SQL:

PL/SQL:

Creative Commons License
Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle) by Carlos Ferreira, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

About Carlos Ferreira

Comments

  1. mahboobe says:

    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 o.name 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!

      Carlos

Leave a Reply

%d bloggers like this: