Insert all values of a table into another table in SQL

Question

I am trying to insert all values of one table into another  But the insert statement accepts values  but i would like it to accept a select   from the initial Table  Is this possible

User · Answer

If you are transferring a lot data permanently, i.e not populating a temp table, I would recommend using SQL Server Import/Export Data for table-to-table mappings.

Import/Export tool is usually better than straight SQL when you have type conversions and possible value truncation in your mapping. Generally, the more complex your mapping, the more productive you are using an ETL tool like Integration Services (SSIS) instead of direct SQL.

Import/Export tool is actually an SSIS wizard, and you can save your work as a dtsx package.

User · Answer

You can insert using a Sub-query as follows   INSERT INTO new table  columns      SELECT columns     FROM initial table where column value

User · Answer

Dim ofd As New OpenFileDialog                 ofd Filter      mdb   MDB                  ofd FilterIndex    2                  ofd FileName    bd1 mdb                  ofd Title    SELECCIONE LA BASE DE DATOS ORIGEN  bd1 mdb                   ofd ShowDialog                   Dim conexion1    Driver  Microsoft Access Driver    mdb   DBQ     ofd FileName                 Dim conn As New OdbcConnection                   conn ConnectionString   conexion1                 conn Open                   EN ESTE CODIGO SOLO SE AGREGAN LOS DATOS              Dim ofd2 As New OpenFileDialog             ofd2 Filter      mdb   MDB              ofd2 FilterIndex    2              ofd2 FileName    bd1 mdb              ofd2 Title    SELECCIONE LA BASE DE DATOS DESTINO  bd1 mdb               ofd2 ShowDialog               Dim conexion2    Driver  Microsoft Access Driver    mdb   DBQ     ofd2 FileName             Dim conn2 As New OdbcConnection               conn2 ConnectionString   conexion2             Dim cmd2 As New OdbcCommand             Dim CADENA2 As String              CADENA2    INSERT INTO EXISTENCIA IN      ofd2 FileName      SELECT   FROM EXISTENCIA IN      ofd FileName                     cmd2 CommandText   CADENA2             cmd2 Connection   conn2             conn2 Open               Dim dA2 As New OdbcDataAdapter             dA2 SelectCommand   cmd2             Dim midataset2 As New DataSet             dA2 Fill midataset2   EXISTENCIA

User · Answer

You can use a select into statement   See more at W3Schools

User · Answer

From here   SELECT   INTO new table name  IN externaldatabase   FROM old tablename

User · Answer

I think this statement might do what you want   INSERT INTO newTableName  SELECT column1  column2  column3 FROM oldTable

User · Answer

There is an easier way where you don t have to type any code  Ideal for Testing or One-time updates        Step 1    Right click on table in the explorer and select  Edit top 100 rows        Step 2    Then you can select the rows that you want  Ctrl   Click or Ctrl   A   and Right click and Copy   Note  If you want to add a  where  condition   then Right Click on Grid -  Pane -  SQL Now you can edit Query and add WHERE condition  then Right Click again -   Execute SQL  your required rows will be available to select on bottom       Step 3    Follow Step 1 for the target table       Step 4    Now go to the end of the grid and the last row will have an asterix     in first column  This row is to add new entry   Click on that to select that entire row and then PASTE  Ctrl   V   The cell might have a Red Asterix  indicating that it is not saved       Step 5    Click on any other row to trigger the insert statement  the Red Asterix will disappear    Note - 1  If the columns are not in the correct order as in Target table  you can always follow Step 2  and Select the Columns in the same order as in the Target table  Note - 2 - If you have Identity columns then execute SET IDENTITY INSERT sometableWithIdentity ON and then follow above steps  and in the end execute SET IDENTITY INSERT sometableWithIdentity OFF

User · Answer

The insert statement actually has a syntax for doing just that  It s a lot easier if you specify the column names rather than selecting     though   INSERT INTO new table  Foo  Bar  Fizz  Buzz  SELECT Foo  Bar  Fizz  Buzz FROM initial table -- optionally WHERE       I d better clarify this because for some reason this post is getting a few down-votes   The INSERT INTO     SELECT FROM syntax is for when the table you re inserting into   new table  in my example above  already exists  As others have said  the SELECT     INTO syntax is for when you want to create the new table as part of the command   You didn t specify whether the new table needs to be created as part of the command  so INSERT INTO     SELECT FROM should be fine if your destination table already exists

User · Answer

Try this   INSERT INTO newTable SELECT   FROM initial Table

[sql] Insert all values of a table into another table in SQL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005