Exec An SQL query in a table in Database 'B' from database 'A'

Question

i have this Table sequence_data in database A . The table has create script for sequences . No I want to create all these sequences in another database B. If it is the same database i can just use a while loop and execute this one by one using the id field. BUT HOW DO I DO THIS if I want to create this sequences on DATABASE B.

Answer 1

Build the script from the source table using STRING_AGG and execute it using sp_executesql, qualified with the target database name. This will avoid the need for a cursor:

DECLARE @SQL nvarchar(MAX) = (
    SELECT STRING_AGG(Code, '')
    FROM DatabaseA.dbo.YourSequenceTable);
EXEC DatabaseB..sp_executesql @SQL;
GO

If STRING_AGG is unavailable (pre-SQL Server 2017), substitute FOR XML for the aggregate string concatenation:

DECLARE @SQL nvarchar(MAX) = (SELECT
    (SELECT Code
    FROM DatabaseA.dbo.YourSequenceTable
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    );
EXEC DatabaseB..sp_executesql @SQL;
GO

Answer 2

First thing that comes to my mind is a CURSOR and sys.sp_executesql. Create a cursor that runs through all the creation scripts. Each time a new script is about to be executed, prefix it with the using for the right database and let it execute.

Example

USE [source database];

CREATE TABLE dbo.sequence_data (code VARCHAR(MAX) NOT NULL);

INSERT INTO dbo.sequence_data (code)
VALUES
('CREATE SEQUENCE dbo.first_one START WITH 1 INCREMENT BY 1;'),
('CREATE SEQUENCE dbo.second_one START WITH 1 INCREMENT BY 1;'),
('CREATE SEQUENCE dbo.third_one START WITH 1 INCREMENT BY 1;');

DECLARE creation_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT code
FROM   dbo.sequence_data;

DECLARE @sql NVARCHAR(MAX);

OPEN creation_cursor;

FETCH NEXT FROM creation_cursor
INTO @sql;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = CONCAT(N'USE [target database]; ', @sql);

    EXECUTE sys.sp_executesql @stmt = @sql;

    FETCH NEXT FROM creation_cursor
    INTO @sql;
END;

CLOSE creation_cursor;
DEALLOCATE creation_cursor;

DROP TABLE dbo.sequence_data;

Of course, if this is a one time thing and there is no real need for automating this: SELECT all the creation scripts, put them in a SSMS-query, connect to the desired target database and execute the script.