Vic (vikrantp (AT) gmail (DOT) com) writes:
What may be the best idea is to construct a view over the common columns:

CREATE VIEW testcases AS
SELECT testcase = 'accounting', [01-RecordClass], [02-RecordAction],
...
FROM accounting
UNION ALL
SELECT 'fundstructures', [01-RecordClass], [02-RecordAction],
...
FROM fundstructures
UNION ALL
...

This presumes that you don't have too many of these tables, as SQL Server
only manages 256 tables in one query.

You would probably have to define dynamically from that file produced by
the Perl script. But from there you could work with the view with static
queries.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 26, 2:14 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thanks everyone for helping meon this. I modified the query I
mentioned earlier to handle 'transaction' seperately (as it was
working for all other object classes) So here is my cursor and the
whole sql query which looks like below. I move on to the next task now
in the same project so might need some sql help later. Thanks

DECLARE @Cmd varchar(5000)
DECLARE @ObjectClass VARCHAR(200)
DECLARE @ObjectClass1 VARCHAR(200)

-- create temp-table to hold the result
CREATE table #result (ObjectClass varchar (100),
ObjectType varchar(200),
ActionType varchar(50),
CountNumber int)

CREATE table #tranresult (ObjectClass varchar (100),
ObjectType varchar(200),
ActionType varchar(50),
CountNumber int)

DECLARE CursorTemplate CURSOR
FAST_FORWARD FOR
SELECT DISTINCT ObjectClass
FROM releases.dbo.SchemaFields
where ObjectClass <> 'transaction'
ORDER BY ObjectClass

OPEN CursorTemplate
FETCH NEXT FROM CursorTemplate INTO @ObjectClass1

WHILE (@@FETCH_STATUS = 0)
BEGIN
Set @Cmd = 'execute releases.dbo.spLoaderCoverageObjectTypeActionType
''TestData6061'''+','+''''+@ObjectClass1+''''+','+ '151'
print @Cmd
INSERT #result
EXEC (@Cmd)
FETCH NEXT FROM CursorTemplate INTO @ObjectClass1
END
CLOSE CursorTemplate
DEALLOCATE CursorTemplate

insert #tranresult
EXECUTE releases.dbo.spLoaderCoverageObjectTypeActionType
'TestData6061','[transaction]' , 151

SELECT *
FROM #result

union

select *
from #tranresult
where ObjectClass like 'tran%'