Database testing

Question

Anusuya S 0 Newbie Poster

10 Years Ago

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

database-design microsoft:sql-server

3 Contributors
9 Replies
252 Views
2 Days Discussion Span
Latest Post 10 Years Ago Latest Post by Anusuya S

Dani AI

Generated 9 Months Ago

Common causes for the "Incorrect syntax near 'dept'." error in this kind of dynamic SQL are: a missing space or token (for example assembling "GROUPBY" instead of "GROUP BY"), not quoting identifiers that need it, or the imported CSV containing column values rather than the actual column names. ' cursor approach is fine, but the dynamic string needs careful quoting and validation before execution. 's question about "count of primary column" is simply the count of rows per distinct primary-column value (GROUP BY that column).

Recommended fixes and a safe pattern to follow:

Ensure the Tables/CSV row really contains the column NAME (not a sample value).
Always quote identifiers with QUOTENAME() to handle spaces/reserved words and prevent injection.
Convert the column value to a single textual type when storing it in a results table (some columns may be numeric/date/etc).
PRINT the generated SQL before EXEC to see exactly what is being executed.

Example pattern (uses QUOTENAME + sp_executesql and verifies the column exists). Adjust TableResults to match desired column sizes.

-- loop variables
DECLARE @t nvarchar(128), @c nvarchar(128), @sql nvarchar(max);

DECLARE cur CURSOR FOR SELECT TableName, PrimaryColumn FROM Tables;
OPEN cur; FETCH NEXT FROM cur INTO @t, @c;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @t AND COLUMN_NAME = @c)
  BEGIN
    SET @sql = N'INSERT INTO TableResults (TableName, PrimaryColumn, ColumnValue, CountOfPrimaryColumn)
SELECT N''' + REPLACE(@t,'''','''''') + N''', N''' + REPLACE(@c,'''','''''') + N''',
       CAST(' + QUOTENAME(@c) + N' AS nvarchar(4000)), COUNT(*) 
FROM ' + QUOTENAME(@t) + N'
GROUP BY ' + QUOTENAME(@c) + N';';
    PRINT @sql;  -- inspect for mistakes
    EXEC sp_executesql @sql;
  END
  ELSE
    PRINT 'Skipping ' + @t + ': column ' + @c + ' not found';
  FETCH NEXT FROM cur INTO @t, @c;
END
CLOSE cur; DEALLOCATE cur;

Troubleshooting notes: if PRINT shows "GROUPBY" or other malformed tokens, fix the string concatenation (missing spaces are the usual culprit). If a column is schema-qualified (dbo.Table), store schema and table separately or split the name when using QUOTENAME. This approach addresses the errors reported while keeping the loop approach suggested by and clarifies the "count" intent raised by .

All 9 Replies

djjeavons 113 partis vestri scientia

10 Years Ago

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 1,905 duckman

10 Years Ago

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

djjeavons 113 partis vestri scientia

10 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Anusuya S 0 Newbie Poster · Answer 1 · 2015-10-08T01:35:31+00:00

Anusuya S 0 Newbie Poster

10 Years Ago

Hi

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

Cheers

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with vnd.openxmlformats-officedocument.spreadsheetml.sheet files.

Sql_sample.xlsx (9.39 KB)

Anusuya S 0 Newbie Poster · Answer 2 · 2015-10-09T05:38:34+00:00

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 113 partis vestri scientia · Answer 3 · 2015-10-09T06:31:08+00:00

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

Anusuya S 0 Newbie Poster · Answer 4 · 2015-10-09T08:05:53+00:00

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 113 partis vestri scientia · Answer 5 · 2015-10-09T08:55:52+00:00

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 0 Newbie Poster · Answer 6 · 2015-10-09T11:21:27+00:00

Hi

Have created as follows

CREATE TABLE TableResults1
(
[TableName] NULL,
[PrimaryColumn] NULL,
[ColumnValue] 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'**.****

Database testing

Dani AI

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers