Agfdhyk

I want to build a dynamic query based on structure provided in another table.

Here is an example how I want to build query based on above table-

select tbl1.col1,tbl1.col2,

tbl2.t2col1,tbl2.t2col2,tbl2.t2col3,tbl2.t2col3,

tbl3.t3col1,tbl3.t3col2,tbl3.t3col3,tbl3.t3col4

from tbl1 

inner join tbl2 on tbl1.col1=tbl2.t2col1 and tbl1.col2=tbl2.t2col2

inner join tbl3 on tbl1.col1=tbl3.t3col1 and tbl1.col2 = tbl3.t2col2

Prepare Dynamic SQL statement, which should be generated from your table based on conditions.

SELECT 'SELECT * FROM '+ tablename+

        ' INNER JOIN '+joinTable+ ' ON '....

FROM table

1) Configuration table structure

CREATE TABLE [dbo].[ConfigQuery](

       [TableName] [varchar](max) NULL, -- Hold the table Name

       [ColumnName] [varchar](max) NULL, -- Column Nam for the Table

       [JoinColumn] [varchar](500) NULL, -- Join Column for the current  table

[JoinTable] [varchar](500) NULL, -- Join table for the current table

       [JoinTblColumn] [varchar](500) NULL, -- Column name of Join table with respect to current table column

       [SiteName] [varchar](500) NULL, -- Site name 

       [RowId] [int] NULL, -- For the purpose of loop, as CURSOR not supported in SQL DATA WAREHOUSE

       [ColAlias] [varchar](5000) NULL – Alias name for the column

       )

2) Store Procedure to Move data from External Table to Staging Table

CREATE PROCEDURE MoveDataFromExternalToStagingTable

@StagingTable varchar(8000),-- Staging Table name

@SiteName varchar(8000) -- Site Name e.g LGW, PRW

AS

BEGIN 



DECLARE @FILE_CNT INT = (SELECT count(*) from ConfigQuery where SiteName= @SiteName); -- Hold total no.of rows count

Declare @query varchar(8000) =''; -- Columns for the query

Declare @from_query varchar(8000) =''; -- From condtion for query



Declare @JoinQuery varchar(8000) =''; -- Join condition for query

Declare @whereCon varchar(8000) =''; -- Where condition for query

Declare @finalQuery varchar(8000) =''; -- Final query @query + @from_query + @JoinQuery + @whereCon

DECLARE @LOOP_CNT INT = 1; 

declare       @j int=0;







--  While loop replaces cursor 

WHILE @LOOP_CNT <= @FILE_CNT 

BEGIN 

DECLARE @v_TableName NVARCHAR(4000) = (SELECT TableName FROM ConfigQuery WHERE rowId = @LOOP_CNT); -- Hold the table name for current row id

DECLARE @v_ColumnName NVARCHAR(4000) = (SELECT ColumnName FROM ConfigQuery WHERE rowId = @LOOP_CNT);  -- Hold the column name for table of current  row id

DECLARE @v_JoinTable NVARCHAR(4000) = (SELECT JoinTable FROM ConfigQuery WHERE rowId = @LOOP_CNT);  -- Hold the join table name for table of current  row id

DECLARE @v_JoinColumn NVARCHAR(4000) = (SELECT JoinColumn FROM ConfigQuery WHERE rowId = @LOOP_CNT);  -- Hold the join column name for table of current  row id

DECLARE @v_JoinTblColumn NVARCHAR(4000) = (SELECT JoinTblColumn FROM ConfigQuery WHERE rowId = @LOOP_CNT); -- Hold the join table column name for table of current  row id

DECLARE @v_Alias NVARCHAR(4000) = (SELECT ColAlias FROM ConfigQuery WHERE rowId = @LOOP_CNT); -- Hold the alias name for column 



DECLARE @first_TableName NVARCHAR(4000) = (SELECT TableName FROM ConfigQuery WHERE rowId = 1);

if @LOOP_CNT != @FILE_CNT

BEGIN

IF @v_Alias != NULL OR @v_Alias != ''

       set @query += @v_TableName +'.'+@v_ColumnName +' AS '+@v_Alias +' , ';

ELSE

       set @query += @v_TableName +'.'+@v_ColumnName +' , ';

END

else

BEGIN

       IF @v_Alias != NULL OR @v_Alias != ''

              set @query += @v_TableName +'.'+@v_ColumnName +' AS '+@v_Alias;

       ELSE

              set @query += @v_TableName +'.'+@v_ColumnName;

END





if @LOOP_CNT=1

BEGIN

set @from_query += 'from '+@v_TableName;



END

else

BEGIN

DECLARE @prev_TableName NVARCHAR(4000) = (SELECT TableName FROM ConfigQuery WHERE rowId = @LOOP_CNT-1);





if(@prev_TableName = @v_TableName) 

       BEGIN





              if @v_JoinColumn != null or @v_JoinColumn != ''

              BEGIN 

               if @j=0

              begin

                           set @joinquery +=  @v_JoinTable+'.'+@v_JoinTblColumn+' = '+@v_TableName+'.'+@v_JoinColumn;

              set @j=1

              end

              else

              begin

                    set @joinquery += ' and ' + @v_JoinTable+'.'+@v_JoinTblColumn+' = '+@v_TableName+'.'+@v_JoinColumn;

              end

              END

END

ELSE

       BEGIN

set @joinquery += ' inner join '+ @v_TableName + ' on ';

set @j=0

if @v_JoinTblColumn != null

set @joinquery +=  @v_JoinTable+'.'+@v_JoinTblColumn+' = '+@v_TableName+'.'+@v_JoinColumn;

END



END



SET @LOOP_CNT +=1;

END



Print @query

Print @from_query 

Print @joinquery

set @finalQuery = 'Insert into '+ @StagingTable + ' select '+@query + @from_query + @JoinQuery



exec(@finalQuery) -- executes the final query



END

--------------



Exec MoveDataFromExternalToStagingTable @StagingTable =’ Stagingtable’),-- Staging Table name

      @SiteName =’LGW’

Result statement of the SP-

Insert into Stagingtable select tbl1.col1 AS Column1 , tbl1.col2 , tbl2.t2Col1 , tbl2.t2Col2 , tbl2.t2Col3 AS Column3 , tbl2.t2Col4 , tbl3.t3Col1 , tbl3.t3Col2 , tbl3.t3Col3 , tbl3.t3Col4

from tbl1

inner join tbl2 on tbl1.col1 = tbl2.t2Col1 and tbl1.col2 = tbl2.t2Col2 inner join tbl3 on tbl2.t2Col1 = tbl3.t3Col3 and tbl1.col1 = tbl3.t3Col1 and tbl1.col2 = tbl3.t3Col2