SQLTeam.com | Weblogs | Forums

Problem inserting into a table


#1

Hi
I have a cursor that is not working it seems to work up until it goes to insert the first record in the table. The cursor loops through a list of table names and then for each table that is inserts into a table it adds the stuff in thee the dynamic SQL So I am thinking that the syntax is wrong.
Can someone tell me is this is right?

 OPEN x
FETCH NEXT FROM x INTO @tableTH
PRINT 'I am in place 1'

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  N'Processing table ' + LTRIM(RTRIM( @tableTH))
PRINT 'I am in place 2'

SET @SQL = 'select ''' + @tableTH + '''
, ''' + @tableTH + '''
,ISNULL((select top 1   1     from ' +  @tableTH + ' group by file_no having count(file_no) > 0)   ,0)
,ISNULL((select top 1   0     from ' +  @tableTH + ' group by file_no having count(file_no) > 1)   ,1)
,''xx''
,10
,10
,15
,5
,0
,0
,0
,0
,0
,0
,1
'
PRINT 'I am in place 3'
 PRINT @SQL
INSERT INTO [DMPCRU].[dbo].[TableHelper] EXECUTE (@SQL)
 PRINT 'I am in place 4'
--EXEC (@SQL)
FETCH NEXT FROM x INTO @tableTH
END
CLOSE x
DEALLOCATE x

#2

should probably be something like

EXECUTE ('INSERT INTO [DMPCRU].[dbo].[TableHelper] '+@SQL)

#3

Thanks for the reply, but that did not work


#4

How about posting the result of

PRINT @SQL

#5

And the DDL for:
TableHelper


#6

Hi

I was able to do it like this

Please let me know if it works

Thanks

Code

CREATE TABLE harish_abc
(
sample VARCHAR(100) NULL
)

DECLARE @tableTH VARCHAR(100)

DECLARE @SQL NVARCHAR(max) = ''

DECLARE x CURSOR FOR
SELECT 'prathima'
UNION ALL
SELECT 'anil'
UNION ALL
SELECT 'harry'

OPEN x

FETCH next FROM x INTO @tableTH

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'select ''' + @tableTH + ''''

  INSERT INTO harish_abc 
  EXECUTE (@SQL) 

  FETCH next FROM x INTO @tableTH 

END

CLOSE x

DEALLOCATE x


#7

I found out that someone put a table that was prefixed with a ~ and that is what was throwing it off.
But your code example here did work thank you.