Database testing

[Anusuya S]

HI

I am a part of database testing team. I have imported a tables.csv file which contains two fields 1. table name- all the tables of a database 2. Primary column- primary column name of the respective tables.
I am trying to write a sql query for getting the table name, primary column ,count (primary column) from table name group by primary column ;
The challenge is i need to make a loop ,so that each time the table name and primary column has to be fetched from the tables.csv file.

So ,My final output has to be something like as below. The idea is to get the below value for all table names and its corresponding primary column as per tables.csv.

table name, primary column, count of primary column

[djjeavons]

Hi

Not sure I fully understand the output that you are looking for. Can you provide an example of the input file and an example of what you would expect the output to be.

HTH

[jwenting]

and what do you mean by "count of primary column"?

[Anusuya S]

Hi

Have attached a sample file with an example. Hope it clarifies .

Cheers

[djjeavons]

Hi

While this may not be the most performant option it will give you the results you are looking for. Basically, it uses a cursor to loop through your Tables CSV structure to then determine what data to select. It then groups data from each table and adds the results to a new table called TableResults that contains the structure you have outlined in your sample. Be sure to create this table first.

--Empty TableResults
DELETE FROM TableResults

/*
Use a cursor to loop through Tables (those imported from csv file) and for each table
select data from that table grouping by the primary column and grabbing the count of values,
then insert these into the temporary table.
*/
DECLARE @SqlStatement varchar(max) --Used to store the sql statement that will insert data to the table variable
DECLARE @TableName varchar(50) -- Used to store the current table name from tables
DECLARE @PrimaryColumn varchar(50) -- Used to store the current primary column name from tables

DECLARE TableCursor CURSOR FOR
    SELECT TableName, PrimaryColumn FROM Tables

    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName, @PrimaryColumn


    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SqlStatement = 'INSERT INTO TableResults SELECT ''' + @TableName + ''' As TableName, ''' + @PrimaryColumn + ''' As PrimaryColumn, ' + @PrimaryColumn + ', COUNT(' + @PrimaryColumn + ') AS 
        CountOfPrimaryColumn FROM ' + @TableName + ' GROUP BY ' + @PrimaryColumn

        EXECUTE(@SqlStatement)

        FETCH NEXT FROM TableCursor INTO @TableName, @PrimaryColumn
    END

    CLOSE TableCursor
    DEALLOCATE TableCursor

SELECT * FROM TableResults

HTH

[Anusuya S]

Hi

Many thanks for your time. But the following query gives the error

"Msg 208, Level 16, State 1, Line 1
Invalid object name 'TableResults'."

**DELETE from TableResults
DECLARE @SqlStatement varchar(max)
DECLARE @TableName varchar(50)
DECLARE @PrimaryColumn varchar(50)
DECLARE TableCursor CURSOR FOR
SELECT TableName,PrimaryColumn FROM tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,@PrimaryColumn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStatement = 'INSERT INTO TableResults SELECT ''' + @TableName + ''' As TableName, ''' + @PrimaryColumn + ''' As PrimaryColumn, '+ @PrimaryColumn + ', COUNT('+ @PrimaryColumn +') AS COUNTOfPrimaryColumn FROM ' + @TableName + ' GROUPBY ' + @PrimaryColumn
EXECUTE(@SqlStatement)
FETCH NEXT FROM TableCursor INTO @TableName, @PrimaryColumn
END
CLOSE TableCursor
DEALLOCATE TableCursor
SELECT * FROM TableResults****

[djjeavons]

Did you create the TableResults table as mentioned in my post?

[Anusuya S]

Yes,I have created the TableResults tables as follows

Create table TableResults
(
TableName varchar(100),
PrimaryColumn varchar(100),
COUNTOfPrimaryColumn int,
);

Now the error message is

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'dept'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Location'.

This 'dept' and 'Location' are the primary column values of imported csv table.

[djjeavons]

Hi

There are four columns in the TableResults table:

CREATE TABLE [dbo].[TableResults](
    [TableName] [varchar](50) NULL,
    [PrimaryColumn] [varchar](50) NULL,
    [ColumnValue] [varchar](50) NULL,
    [CountOfPrimaryColumn] [int] NULL
)

[Anusuya S]

Hi

Have created as follows

CREATE TABLE TableResults1
(
[TableName] varchar NULL,
[PrimaryColumn] varchar NULL,
[ColumnValue] varchar NULL,
[CountOfPrimaryColumn] [int] NULL
)

And the following code

DELETE from TableResults1
DECLARE @SqlStatement varchar(max)
DECLARE @TableName varchar(50)
DECLARE @PrimaryColumn varchar(50)
DECLARE TableCursor CURSOR FOR
SELECT TableName,Primarycolumn FROM tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName,@PrimaryColumn
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlStatement = 'INSERT INTO TableResults1 SELECT ''' + @TableName + ''' As TableName, ''' + @PrimaryColumn + ''' As PrimaryColumn, '+ @PrimaryColumn + ', COUNT('+ @PrimaryColumn +') AS COUNTOfPrimaryColumn FROM ' + @TableName + ' GROUPBY ' + @PrimaryColumn
EXECUTE(@SqlStatement)
FETCH NEXT FROM TableCursor INTO @TableName, @PrimaryColumn
END
CLOSE TableCursor
DEALLOCATE TableCursor
SELECT * FROM TableResults1

******Error Log

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'dept'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Location'**.****