How to combine multiple dynamic query

Question

Problem solved by my own answer below

I was referring to How to combine two dynamic sql with union ALL but it doesn't work

the problem of my query is that the union method couldn't work because it doesn't get the data from the variable on top, given the fact that it is alraedy executed : exec(insert1) and exec(insert2)

and the purpose of union is to get the db_name by selecting 'table_catalog' later,because if 'table_catalog' is selected before the comparison,the output will be wrong

here's the query

    Declare @PilotDBName nvarchar(255)
Declare @LiveDBName nvarchar(255)

Declare @Table1 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
Declare @Table2 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table3 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table4 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table5 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table6 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255), table_catalog nvarchar(255))  
Declare @Table7 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255), table_catalog nvarchar(255)) 

Set @PilotDBName = 'LON_HRMS'
Set @LiveDBName = 'VSM641SP3QC'

Declare @insert1 NVARCHAR(MAX) 
set @insert1 = '
Declare @Table1 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
INSERT INTO @Table1
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE'' ORDER BY VIEW_NAME '
EXEC(@insert1)

Declare @insert2 NVARCHAR(MAX)
Declare @SQLResult NVARCHAR(MAX)
Set @insert2 = '
Declare @Table2 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
INSERT INTO @Table2
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE'' ORDER BY VIEW_NAME '
Exec(@insert2)


Declare @insert5 NVARCHAR(MAX) = '
Declare @Table1 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
Declare @Table2 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
Declare @Table5 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
Insert into @Table5
select * From Table1
Union
select * from @Table2'
Exec(@insert1)Exec(@insert2)Exec(@insert5)

before i show the output, records in queries are:

insert 1: 243 rows insert 2: 1 rows insert 5: 0 rows

I'm not 100% sure what you're trying to do but INSERT and SET don't go together in the same statement. — ZLK
– ZLK, Commented Mar 28, 2017 at 4:28
It's because you are declaring table variables on the 2 query strings. remove the DECLARE and insert part and you should be good to go. — beejm
– beejm, Commented Mar 28, 2017 at 4:50
@BennjoeMordeno It doesn't work, the declaring statement is needed for my query, else it would prompt me to declare. Yes my table has already declared on the top but the statement is needed. — Jesse
– Jesse, Commented Mar 28, 2017 at 6:01
@BennjoeMordeno stackoverflow.com/questions/30891932/… that is why i needed to declare in the query — Jesse
– Jesse, Commented Mar 28, 2017 at 6:03

Jesse · Accepted Answer · 2017-03-28 09:18:18Z

I have found the answer myself, basically all I have to do is throw everything into one big chunk of dynamic sql

and the table variable must declared inside, whereas the rest can leave it outside the dynamic query

Declare @PilotDBName nvarchar(255)
Declare @LiveDBName nvarchar(255)
Set @PilotDBName = 'LON_HRMS'
Set @LiveDBName = 'VSM641SP3QC'

Declare @insert1 NVARCHAR(MAX) = '
Declare @Table1 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255)) 
Declare @Table2 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table3 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table4 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255))  
Declare @Table5 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255), Table_In nvarchar(255))  
Declare @Table6 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255), Table_In nvarchar(255))  
Declare @Table7 Table(TABLE_SCHEMA nvarchar(255)  , VIEW_NAME nvarchar(255), COLUMN_NAME nvarchar(255), table_type nvarchar(255), Table_In nvarchar(255)) 


INSERT INTO @Table1
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE'' ORDER BY VIEW_NAME 


INSERT INTO @Table2
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''BASE TABLE'' ORDER BY VIEW_NAME 

INSERT INTO @Table3
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''VIEW''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''VIEW'' ORDER BY VIEW_NAME  

INSERT INTO @Table4
SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME, b.table_type
FROM '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@LiveDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''VIEW''

EXCEPT 

SELECT a.TABLE_SCHEMA, a.VIEW_NAME, COLUMN_NAME,b.table_type
FROM '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE a Inner join '+@PilotDBName+'.information_schema.tables b 
on a.TABLE_NAME = b.TABLE_NAME
where b.table_type=''VIEW'' ORDER BY VIEW_NAME  

Insert into @Table5
select a.TABLE_SCHEMA, a.VIEW_NAME, a.COLUMN_NAME As Column_Missing,a.table_type, info.TABLE_CATALOG As Found_In  from @Table1 a , '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE info
Union
select b.TABLE_SCHEMA, b.VIEW_NAME, b.COLUMN_NAME As Column_Missing,b.table_type, info.TABLE_CATALOG As Found_In from @Table2 b , '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE info
order by table_catalog

Insert into @Table6
select a.TABLE_SCHEMA, a.VIEW_NAME, a.COLUMN_NAME As Column_Missing,a.table_type, info.TABLE_CATALOG As Found_In  from @Table3 a , '+@PilotDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE info
Union 
select b.TABLE_SCHEMA, b.VIEW_NAME, b.COLUMN_NAME As Column_Missing,b.table_type, info.TABLE_CATALOG As Found_In  from @Table4 b , '+@LiveDBName+'.INFORMATION_SCHEMA.VIEW_COLUMN_USAGE info


select * from @Table5 a 
Union 
select * from @Table6 b '
Exec(@insert1)

Collectives™ on Stack Overflow

How to combine multiple dynamic query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related