CODEWITHSUNDEEP
c#sql-server
Pomme De Terre
Pomme De Terre

Reputation: 149

Is it possible to update bunch of different Tables in DB with the same modification to all?

I want to duplicate all my tables in SQL Server, all table names would have had "temp" added at the beginning. And all of them would have had added an extra column (the same to all). I don't need whole code, just general idea how to do it.

Upvotes: 0

Views: 50

Answers (2)

Bart Hofland
Bart Hofland

Reputation: 3905

A straightforward way to go:

  1. You need to fetch the table names from your database (probably using INFORMATION_SCHEMA.TABLES).
  2. For each of those tables from step 1, you need to generate a corresponding SELECT ... INTO statement.
  3. You need to execute each generated SQL statement from step 2.

You already have a solution with a cursor. This is one without a cursor:

DECLARE @script VARCHAR(MAX) = '';
SELECT @script = @script + 'SELECT * INTO [temp'+ TABLE_NAME +'] FROM [' + TABLE_NAME + '];' + CHAR(13) + CHAR(10) FROM INFORMATION_SCHEMA.TABLES
EXEC (@script);

Remark: The CHAR(13) + CHAR(10) is not necessary; just added for readability if you want to check the script first (using PRINT instead EXEC).

Edit:

An additional question in the comments to add a checksum value in the resulting tables could be done as follows:

DECLARE @script VARCHAR(MAX) = '';
SELECT @script = @script + 'SELECT CHECKSUM(*) AS [__checksum], * INTO [temp'+ TABLE_NAME +'] FROM [' + TABLE_NAME + '];' + CHAR(13) + CHAR(10) FROM INFORMATION_SCHEMA.TABLES
EXEC (@script);

Using HASHBYTES instead of CHECKSUM is probably better, but it accepts only two parameters: the hash algorithm and a single value to hash. So in that case, you probably need to pass a string value by manually concatenating all the fields of your tables, and that may be somewhat troublesome to add in a dynamic query like mine. It would probably result in something more complex than just three lines...

Well, something like this, actually:

DECLARE @script NVARCHAR(MAX) = N'';

WITH
    [Columns] AS
    (
        SELECT
            TABLE_NAME AS [TableName],
            COLUMN_NAME AS [ColumnName],
            ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION) AS [ColSeq]
        FROM
            INFORMATION_SCHEMA.COLUMNS
    ),
    [Tables] AS
    (
        SELECT
            [TableName],
            CAST(N'[' + [ColumnName] + N']' AS NVARCHAR(MAX)) AS [ColumnList],
            [ColSeq]
        FROM
            [Columns] AS C
        WHERE
            [ColSeq] = (SELECT MAX([ColSeq])
                        FROM [Columns]
                        WHERE [TableName] = C.[TableName]) 

    UNION ALL
        SELECT T.[TableName], N'[' + C.[ColumnName] + N'], ' + T.[ColumnList], C.[ColSeq]
        FROM
            [Tables] AS T
            INNER JOIN [Columns] AS C ON C.[TableName] = T.[TableName] AND C.[ColSeq] = T.[ColSeq] - 1
    )
SELECT @script = @script + N'SELECT HASHBYTES(''md5'', CONCAT(N'''', ' + [ColumnList] + N')) AS [__checksum], * INTO [temp' + [TableName] + N'] FROM [' + [TableName] + N'];' + NCHAR(13) + NCHAR(10)
FROM [Tables]
WHERE [ColSeq] = 1;

EXEC (@script);

Remarks:

  • In the recursive CTE [Tables], which is used for concatenating the column names of each table in a comma-separated string value, I started at the last column and moved backwards to ease the filter condition in my main query.
  • I added an additional first parameter N'' to the CONCAT calls in the resulting @script contents, since the CONCAT function requires at least 2 arguments, which would be troublesome in this case when processing tables with just one column.

In this case, despite the somewhat worse performance, it might be clearer and easier to fall back to using a cursor, like @HasanMahmood suggested in his answer...

Upvotes: 1

Hasan Mahmood
Hasan Mahmood

Reputation: 978

try this code:

get all the table name form information schema and run a dynamic sql to create tables

DECLARE @script varchar(max)

DECLARE db_cursor CURSOR FOR 
SELECT script = 'Select * Into  [temp'+ TABLE_NAME +'] From ' + QUOTENAME(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES


OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @script  

WHILE @@FETCH_STATUS = 0  
BEGIN  

      EXEC(@script)
      --PRINT @script

      FETCH NEXT FROM db_cursor INTO @script
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

Upvotes: 1

Related Questions