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

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?

You can insert using a Sub-query as follows:

INSERT INTO new_table (columns....)
SELECT columns....
FROM initial_table where column=value

From here:

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

I think this statement might do what you want.

INSERT INTO newTableName (SELECT column1, column2, column3 FROM oldTable);

Try this:

INSERT INTO newTable SELECT * FROM initial_Table

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

 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")

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.

You can use a select into statement. See more at W3Schools.