CODEWITHSUNDEEP
sql-servert-sql
djangofan
djangofan

Reputation: 29669

Using a loop to change schema of tables in a SQL Server database

I have a question about using a loop to change schema of tables in a SQL Server database.

This code is failing due to an error and I can't figure out what is wrong with it.

The error is:

Error: Incorrect syntax near '|'.

And here is the T-SQL code is:

SELECT DISTINCT TABLE_NAME
Into   #Temp 
FROM  INFORMATION_SCHEMA.TABLES

Declare @Name varchar2
Declare @mQuery varchar2

While (Select Count(*) From #Temp) > 0
Begin

    Select Top 1 @Name = Name From #Temp;

    SET @mQuery = 'ALTER SCHEMA schema1 TRANSFER schema99.'  || @Name  ||  ';' ;

    dbms_output.put_line( @mQuery );

    --sp_executesql @mQuery;

    Delete #Temp Where Name = @Name;

End

Upvotes: 0

Views: 594

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Use PRINT instead of dbms_output.put_line, and + instead of ||:

SET @mQuery = 'ALTER SCHEMA schema1 TRANSFER schema99.'  + @Name  +  ';' ;

PRINT @mQuery;

Upvotes: 1

Anton Kovalenko
Anton Kovalenko

Reputation: 21507

String concatenation in tsql is +, not ||.

Upvotes: 1

Related Questions