Question 106020 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Is there a way to combine multiple query into one in SQL Server 2019?

I am wondering if there is a function in SQL Server or service in Azure to provide an opportunity to combine multiple queries into one. For example,

ALTER TABLE1 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
ALTER TABLE2 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
ALTER TABLE3 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5);
...

Is there a way to combine all these together into 1 query that can affect all the tables? the columns will be all same for every table and there are simply just too many tables to do copy and paste.

Thank you!

Tags:

2

Answers


  1. 0

    use the stored procedure sp_msforeachtable with filtering table.

    The placeholder `?’ is denoting the table.

    EXEC sp_msforeachtable
 @command1 ='ALTER TABLE ? 
    ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5)'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name in ( ''TABLE1'',''TABLE2''))'
    Login or Signup to reply.
  2. 0

    Using Dynamic SQL you can construct the required alter statements and aggregate them into a single string to execute:

    declare @Sql nvarchar(max);
select @Sql = String_Agg(
  'alter table dbo.' + QuoteName([name]) + ' ADD RetailValue AS (CAST(QtyAvailable AS int) * CAST(UnitPrice AS int) * 1.5); '
  ,'')
from sys.tables where name in ('table1','table2', 'table3')
  and schema_id = Schema_Id('dbo');

print @Sql;
exec (@Sql);

    See a demo Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search