Copy a table to another table with different structure

I have 2 tables in the DB:


The Table1_Temp was generated from a CSV. it is almost identical to Table1 but different because all Table1_Temp fields are VARCHAR and it has some irrelevant fields.

  • Case statement in Join Clause
  • SET versus SELECT when assigning variables?
  • Difference of create Index by using include column or not using
  • Summing subsets with SQL
  • SPID of a SqlConnection (SQL-Server) in ADO.NET
  • in SQL SELECT statement :conditionally removing comma operator from field
  • I need to move the Data from Table1_Temp to Table1 but to keep the structure of Table1, and disregard the unnecessary fields from Table1_Temp.

    How can I do it?

    2 Solutions collect form web for “Copy a table to another table with different structure”

    Choose the columns to use and cast them to the necessary type in your select

    insert into table1 (col1, col2, col3)
    select cast(col1 as signed), col5, col7
    from Table1_Temp

    if both tables are on a different database (and different column)

    INSERT INTO db1.table1 (Acol1, Acol2, Acol3)
    SELECT Bcol1 AS Acol1, Bcol2 AS Acol2, Bcol3 AS Acol3
    FROM db2.table1_temp 

    This will only work if both databases are under 1 server (in this instance “localhost”)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.