Copy from all tables, insert into all tables (sp_MSforeachtable alternative)

Question

I've decided to share my experience in trying to copy data from all tables into the same name tables in a different schema. I'm sure my experience can help others seeking mass table operations without using the unsupported and frankly limited sp_MSforeachtable.

Objective: Copy data from all tables in my database to identically named tables with different schema

One quick note about sp_MSforeachtable. Most of the time when people ask questions here in SO about this stored procedure, there's some reply or another saying that we shouldn't use unsupported features. This is simply not true, what we shouldn't do is have practices and design decisions based on unsupported features, mostly because they might go away. But at some specific point in time, if an unsupported feature exists and does exactly what we need to do right then, one-off style, then just consider yourself lucky and by all means use it, just be careful about unexpected behaviors. When using such features, best to stick with simple operations whose output is quickly and easily verifiable.

With that said, and also because there's some real limitations to sp_MSforeachtable, I am presenting a different but not too complicated way of executing statements against all (or specific) tables in our database and I'm using my problem as an example.

score 2 · Answer 1 · answered Nov 02 '12 at 10:56

I often approach this problem by generating queries that return statements, and then copy paste these statements into a new query window and executing them. I like this approach because I see my statements before they're executed and can also use the parser to quickly identify issues with them. With this in mind, here's my query:

SELECT DISTINCT ''
    + ' INSERT INTO ' + 'dbo.' + QUOTENAME(name)
    + ' (' + dbo.COLUMN_NAMES('dbo', name) + ')'
    + ' SELECT ' + dbo.COLUMN_NAMES('dbo', name)
    + ' FROM ' + 'db_owner.' + QUOTENAME(name)
FROM sys.tables
-- add your own WHERE clauses to only execute against specific tables

It's that simple. This will return a list of INSERT INTO ... SELECT FROM... statements that I can simply copy paste into a new query window.

If you're wondering why not just using SELECT *, it's simply because if your table has ID columns then you will need to explicitly name the columns and also add SET_IDENTITY statements before and after.

The COLUMN_NAMES function is a pretty self-explanatory function that returns comma separated column names for the specified table:

CREATE FUNCTION COLUMN_NAMES
(
    @tableschema VARCHAR(MAX),
    @tablename VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

RETURN (
    REPLACE(
        (SELECT QUOTENAME(COLUMN_NAME) AS 'data()'
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA=@tableschema AND TABLE_NAME=@tablename ORDER BY ORDINAL_POSITION FOR XML PATH('')),
         ' ',', '))

END
GO

Copy from all tables, insert into all tables (sp_MSforeachtable alternative)

1 Answers1