Search code examples
sqlsql-server-2016

Copy specific columns from one table to another table, and include the source tablename

I have this newly created table in SQL Server with 3 columns ID, Name, Source.

Basically this table will be populated with data from other different tables, each specifically taking in their record IDs and record Names. I believe this can be easily achieved with an INSERT INTO SELECT statement.

I would like to find out on how to populate the Source column. This column is supposed to indicate which table the data came from. For example, Source in table A has 3 records, which I then copied the ID and Name columns from this table, and put it into my destination table.

At the same time, the 3 new records will have their Source column set, indicating it came from Table A. Then I will proceed to do the same for other tables.

Solution

  • You can use the constant string as follows:

    INSERT INTO your_table
SELECT id, name, 'TableA' as source
  FROM tableA