How to Load Unknown Schema Tables from Source to Target Using SQL

When integrating data from external systems, it’s common to encounter situations where the source schema isn’t known in advance. Instead of manually inspecting and copying each table, you can use dynamic SQL to automate the process. This article walks you through how to identify tables in a source schema and load them into a target schema using SQL Server.

Load Unknown Schema Tables from Source to Target Using SQL

🔍 Step 1: Identify Tables in the Source Schema

First, you need to retrieve the list of tables from the source schema. SQL Server provides a system view called INFORMATION_SCHEMA.TABLES that contains metadata about all tables.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
  AND TABLE_SCHEMA = 'source_schema';

--Replace 'source_schema' with the actual name of your source schema.

🧠 Step 2: Generate Dynamic SQL for Each Table

To copy each table from the source to the target schema, you can use dynamic SQL. This allows you to build and execute SQL statements at runtime.

Here’s how to do it using a cursor:

DECLARE @TableName NVARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

DECLARE table_cursor CURSOR FOR
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
  AND TABLE_SCHEMA = 'source_schema'

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = '
    SELECT * INTO target_schema.' + @TableName + '
    FROM source_schema.' + @TableName

    EXEC sp_executesql @SQL

    FETCH NEXT FROM table_cursor INTO @TableName
END

CLOSE table_cursor
DEALLOCATE table_cursor

This script copies both the structure and the data of each table from the source to the target schema.

🧰 Step 3: Customize for Your Environment

Depending on your database setup, you might need to adjust the script:

• Different Databases: For PostgreSQL, Oracle, or MySQL, the syntax and system views will differ.
• Cross-Database Copying: If the source and target are in different databases or servers, consider using linked servers or ETL tools like SSIS or Apache NiFi.
• Data-Only Transfer: If you only want to copy data (not structure), use INSERT INTO target_schema.table SELECT * FROM source_schema.table.

🛡️ Step 4: Add Error Handling and Logging (Optional)

For production environments, it’s wise to add error handling and logging to track which tables were successfully copied and which failed.

✅ Final Thoughts

This dynamic SQL approach is a powerful way to automate schema migration when the structure isn’t known ahead of time. It saves time, reduces manual errors, and scales well for large numbers of tables.

If you’re working with a different database system or need help adapting this to your setup, I’d be happy to help tailor it further.