January 21, 2015 at 12:36 pm
I am trying to think of a way to read a control table, build the SQL statement for each line, and then execute them all, without using a cursor.
To make it simple... control table would look like this:
CREATE TABLE [dbo].[Control_Table](
[Server_Name] [varchar](50) NOT NULL,
[Database_Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC,
[Database_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
So if we then load:
insert into zt_Planning_Models_Plant_Include_Control_Table
values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
Then you would build a SQL script that would end up looking like the following (note all the columns are the same):
insert into master_models
Select * from r2d2.planning1.dbo.models
insert into master_models
select * from r2d2.planning7.dbo.models
insert into master_models
Select * from deathstar.planning3.dbo.models
January 21, 2015 at 1:48 pm
Quick suggestion, use FOR XML PATH to concatenate the output into one dynamic sql string
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Control_Table') IS NOT NULL DROP TABLE dbo.Control_Table;
CREATE TABLE [dbo].[Control_Table](
[Server_Name] [varchar](50) NOT NULL,
[Database_Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC,
[Database_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into dbo.Control_Table(Server_Name,Database_Name)
values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
DECLARE @SQL_STR NVARCHAR(MAX) =
(SELECT
N'insert into master_models
SELECT * FROM ' + CT.Server_Name + NCHAR(46) + CT.Database_Name + N'.dbo.models;
'
FROM dbo.Control_Table CT
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')
PRINT @SQL_STR;
--EXEC (@SQL_STR);
Output
insert into master_models
SELECT * FROM deathstar.planing3.dbo.models;
insert into master_models
SELECT * FROM r2d2.planing1.dbo.models;
insert into master_models
SELECT * FROM r2d2.planing7.dbo.models;
January 21, 2015 at 1:50 pm
This great article gives you a few options to concatenate row values:
Concatenating Row Values in Transact-SQL[/url]
I prefer the XML one.
All you have to do is concatenate the column servername and database name and put the relevant parts of the SQL statement before and after the result.
This will give you a result set with one row, which you can store in a variable. Then you can execute the SQL statement with sp_executesql.
edit: it seems Eirikur was a bit faster than me and did supply the SQL code. What a show-off 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2015 at 11:29 am
Thanks for both of your replies.. During lunch I read through the article. Will try and get back to this project tomorrow.
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply