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.

score 2 · Accepted Answer · answered Jan 05 '23 at 11:36

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

score 1 · Answer 2 · answered Jan 05 '23 at 07:34

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.

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

2 Answers2