0

So basically I have a table that looks like this. I have to migrate data from source database tables to its corresponding target database tables.

    SourceTableName                    DenormalizedU4SMTable             TargetTableName

1)  Lookup_AcademicTimeType                Time                          ACTimeType
2)  Lookup_TimeDurationType                Time                          ACTimeDurationType
3)  TimeStructure_ACAcademicTime           Time                          ACAcademicTime
4)  TimeStructure_AdmissionsTime           Time                          AdmissionsTime
5)  TimeStructure_BillingTime              Time                          BillingTime

Now i want to iterate through each row of this table in such a way that so I can copy the data from Lookup_AcademicTimeType to ACTimeType. So the target database tables have some extra columns like Create Date, ModifyDate, etc.

INSERT INTO TARGET_TABLE (HERE I WANT TO AUTOMATICALLY BRING ALL COLUMN OF SPECIFIC TARGET TABLE)
    SELECT *, **THOSE EXTRA COLUMN** 
    FROM SOURCE_TABLE

I can do this one by one for each table but i want to do this dynamically for all the tables in one go. Can anyone tell me how i can do this??

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
astroluv
  • 798
  • 1
  • 8
  • 25
  • Please add a tag about the database product you are using. Have you tried `INSERT INTO TARGET_TABLE SELECT **columns** FROM SOURCE_TABLE`? It may work if the columns in the select corresponds to ALL columns in the target. – Conffusion Aug 27 '20 at 12:36
  • Hi I'm using SQL Server 2016. I did this for each table one by one, but i want to know how i can do this dynamically? For all tables? And also how i can put the name of the columns in the insert statement? @Conffusion – astroluv Aug 27 '20 at 12:40
  • If you want to generate SQL for all tables, all columns I would suggest to write a stored procedure which uses the metadata table to loop over all tables and columns to generate the SQL statements. I don't know SQL Server but there must be a way to retrieve the meta data of your tables (sysobjects?). See https://stackoverflow.com/questions/887370/sql-server-extract-table-meta-data-description-fields-and-their-data-types – Conffusion Aug 27 '20 at 14:36

0 Answers0