CODEWITHSUNDEEP
sqlssmssql-insertdatabase-cursor
Caveman42
Caveman42

Reputation: 709

executing an insert into within a cursor loop

I am trying to insert values into a temp table from within a cursor loop. It is hard to explain so I will just show what I have so far.

declare @sqlStatement varchar(max)
declare @tmpTable table (
    Table_Name varchar(max)
    ,Count int
)
declare cur CURSOR FAST_FORWARD FOR
    Select
        'Select ''' + TABLE_NAME + ''' [Table_Name],COUNT(*) [Count] From [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
    from information_schema.tables
    Where TABLE_TYPE = 'BASE TABLE'

OPEN cur
FETCH NEXT FROM cur
INTO @sqlStatement

WHILE @@FETCH_STATUS = 0
BEGIN
    declare @tmp varchar(max) = 'INSERT INTO @tmpTable ' + @sqlStatement 
    exec @tmp
    Select * From @tmpTable

    fetch next from cur
    into @sqlStatement
END

CLOSE cur
DEALLOCATE cur

And I am getting the error

The name 'INSERT INTO @tmpTable Select 'table' [Table_Name],COUNT(*) [Count] From [dbo].[table]' is not a valid identifier.

But I don't see anything wrong with that statement. I think it may have to do with the escape characters in within the string?

Upvotes: 0

Views: 2331

Answers (3)

K-Man
K-Man

Reputation: 11

There are couple of issues, your while loop is missing 'FETCH NEXT FROM cur INTO @sqlStatement' which will lead it to indefinite loop. But this is not really the problem here. use exec ( ) with brackets it should solve the problem.

Or if I was you I will try to achieve this using a set based statement like following :

declare @tmpTable  table (
    Table_Name varchar(max),
    CountX int
)

insert into @tmpTable   
SELECT sc.name +'.'+ ta.name TableName
 ,SUM(pa.rows) CountX
 FROM sys.tables ta
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 ORDER BY SUM(pa.rows) DESC

 select * from @tmpTable

Upvotes: 1

woot
woot

Reputation: 7616

Try using an actual temp table instead of a variable. Dynamic SQL needs to reference database objects.

create table #tmpTable (
    Table_Name varchar(max)
   ,Count int
)

Upvotes: 1

Alex K.
Alex K.

Reputation: 175926

The format for the exec call must include parentheses:

exec(@tmp)

You will also need to change to a #temp table as a table variable will not be within the scope of the exec'd batch.

Upvotes: 2

Related Questions