November 30, 2010 at 3:46 am
Hello,
I would like to generate the dynamic queries as per our requirement.
DECLARE @table1 TABLE (Table1Pkey1 INT, Table1PKey2 INT, Table1Col1 NVARCHAR(100))
DECLARE @table2 TABLE (Table2Pkey1 INT, Table2PKey2 INT, Table2Col1 NVARCHAR(100))
DECLARE @systemTable TABLE (tableName NVARCHAR(100), columnName NVARCHAR(100))
--sample data:
INSERT INTO @systemTable
VALUES ('@table1', 'Table1Pkey1'),
('@table1', 'Table1PKey2'),
('@table2', 'Table2Pkey1'),
('@table2', 'Table2PKey2')
INSERT INTO @table1 VALUES (11,11,'Hello table1'),
(12,11,'How are you')
INSERT INTO @table2 VALUES (21,33,'Hello table2'),
(22,33,'How are you')
We want the result set to be like:
TableName, KeysWithValues, ColumnsToCheck
Table1, Table1Pkey1 = 11 AND Table1PKey2 = 11, Table1Col1
Table1, Table1Pkey1 = 12 AND Table1PKey2 = 11, Table1Col1
Table2, Table2Pkey1 = 21 AND Table2PKey2 = 33, Table2Col1
Table2, Table2Pkey1 = 22 AND Table2PKey2 = 33, Table2Col1
How can I achieve this, can you please guide me?
Regards,
Hardik
Thanks
December 1, 2010 at 2:00 am
Not sure on your requirement.
I had prepared the sample given, and below is what I can think of to produce the output given.
;with dy_tb as (
select distinct tableName from @systemTable
),
pk_cols as (
select row_number() over (partition by tableName order by columnName) as rn, *
from @systemTable),
tb_pk as (
select dy_tb.tableName, col1 = p1.columnName, col2 = p2.columnName
from dy_tb
left join pk_cols p1 on dy_tb.tableName = p1.tableName and p1.rn = 1
left join pk_cols p2 on dy_tb.tableName = p2.tableName and p2.rn = 2)
Select
TableName,
KeysWithValues = col1 + ' = ' + cast(Table1Pkey1 as varchar(50)) + ' AND ' +
col2 + ' = ' + cast(Table1Pkey2 as varchar(50)),
ColumnsToCheck = '???'
From tb_pk, @table1
Where tableName = '@table1'
Union All
Select
TableName,
KeysWithValues = col1 + ' = ' + cast(Table2Pkey1 as varchar(50)) + ' AND ' +
col2 + ' = ' + cast(Table2Pkey2 as varchar(50)),
ColumnsToCheck = '???'
From tb_pk, @table2
Where tableName = '@table2'
Would like to clarify:
1) Only 2 tables are used?
2) How to make sure the ColumnName in @systemTable match the value in respective table?
E.g. @systemTable.ColumnName match with @table1.Table1Pkey1 and not Table1Pkey2.
I'm using column "rn" to specify the column sequence, not sure it will meet your requirement.
3) What is the source for column ColumnsToCheck?
December 1, 2010 at 4:38 am
kpyap001 (12/1/2010)
Not sure on your requirement.I had prepared the sample given, and below is what I can think of to produce the output given.
;with dy_tb as (
select distinct tableName from @systemTable
),
pk_cols as (
select row_number() over (partition by tableName order by columnName) as rn, *
from @systemTable),
tb_pk as (
select dy_tb.tableName, col1 = p1.columnName, col2 = p2.columnName
from dy_tb
left join pk_cols p1 on dy_tb.tableName = p1.tableName and p1.rn = 1
left join pk_cols p2 on dy_tb.tableName = p2.tableName and p2.rn = 2)
Select
TableName,
KeysWithValues = col1 + ' = ' + cast(Table1Pkey1 as varchar(50)) + ' AND ' +
col2 + ' = ' + cast(Table1Pkey2 as varchar(50)),
ColumnsToCheck = '???'
From tb_pk, @table1
Where tableName = '@table1'
Union All
Select
TableName,
KeysWithValues = col1 + ' = ' + cast(Table2Pkey1 as varchar(50)) + ' AND ' +
col2 + ' = ' + cast(Table2Pkey2 as varchar(50)),
ColumnsToCheck = '???'
From tb_pk, @table2
Where tableName = '@table2'
Would like to clarify:
1) Only 2 tables are used?
2) How to make sure the ColumnName in @systemTable match the value in respective table?
E.g. @systemTable.ColumnName match with @table1.Table1Pkey1 and not Table1Pkey2.
I'm using column "rn" to specify the column sequence, not sure it will meet your requirement.
3) What is the source for column ColumnsToCheck?
Really thanks for your reply.
Ans 1: Because of sample data, I have added only two tables but in real time, we will have data dictionary to get the tables & its primary key columns (maps to @systemTable here).
Ans 2: Your approach using rownumber addresses our purpose however we don't the number of columns that we will encounter. E.g. table1 may have 3 columns as composite primary key but table2 may have only 1.
Ans 3: ColumnsToCheck will match with the values of the records i.e. table1.Table1Col1 or table2.Table2Col1.
E.g. for Table1Pkey1 = 11 AND Table1PKey2 = 11, the ColumnsToCheck will be 'Hello table1'.
Do let me know if I can be of any further assistance.
Thanks
December 6, 2010 at 9:47 am
Hi,
I would like to suggest the approach below:
1) For every tableX, add a row which defined the primary key columns.
e.g.
;with pk_cols as (
select row_number() over (partition by tableName order by columnName) as rn, *
from @systemTable)
INSERT INTO @table1
select
(Select columnName from pk_cols where rn = 1 and tableName = '@table1'),
(Select columnName from pk_cols where rn = 2 and tableName = '@table1'), '{Reserved}'
Notes:
a) Column data type need to be varchar.
b) May use table variable or temp table instead of change the actual table.
2) Cross join the (1) info to tableX.
e.g.
Select
TableName = '@Table1',
KeysWithValues = a.Table1Pkey1 + ' = ' + cast(b.Table1Pkey1 as varchar(50)) + ' AND ' +
a.Table1Pkey2 + ' = ' + cast(b.Table1Pkey2 as varchar(50))
From #pk_info a, @table1 b
Approach above is making use of tableX table structure (assuming you know it's columns in advance).
If tableX table structure is vary, I think cursor and dynamic pivot may required.
Dynamic Pivot: http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Notes: I think putting [Sequence] column in @SystemTable is better instead of row_number().
I also think of other alternative, which need to modify your existing table structure. E.g.
Create table DynSql (
TableName varchar(255),
IsTableDef bit, -- determine whether is PK column / value
PK1 varchar(255), -- either PK column name / value
PK2 varchar(255),
PK3 varchar(255),
PK4 varchar(255),
PK5 varchar(255),
FieldToCheck varchar(255)
)
With above table structure, I think it will be more easier to generate the dynamic query you want (using cross join of the same table).
Hope it helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply