January 26, 2003 at 4:15 pm
I want to fill a DBtemp table with data but want to check first if some data exists in
another history table (before filling DBtemp table).
The DBtemp table will then be further prcocessed.
EXEC ('INSERT INTO DBTemp (TableName, trxYearMonthStart, nmbrtrx)
@ProcessTable,
SELECT CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+ ''01'',
COUNT(*)
FROM ' + @ProcessTable + ' GROUP BY
CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+''01''
')
First problem, I want to write @ProcessTable (name of the table to be processed) into table DBtemp but having syntax problems near @ProcessTable?
Second thing, how can I check if data (column TableName=Transactions and trxYearMonthStart=01/04/2002) exists in table history and therefore not load anymore this data into table DBTemp?
Thanks for any hints
mipo
January 26, 2003 at 5:16 pm
something like
declare @sql varchar(1000)
select @sql = 'insert DBTemp (TableName, trxYearMonthStart, nmbrtrx)'
select @sql = @sql + ' ''' + @ProcessTable + ''''
select @sql = @sql + ', ''' + CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+ '01'''
select @sql = @sql + ', count(*)'
select @sql = @sql + ' from + @ProcessTable
select @sql = @sql + ' group by ''' + CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+ '01'''
exec (@sql)
display @sql before trying to run it.
Cursors never.
DTS - only when needed and never to control.
January 27, 2003 at 5:14 am
Try
declare @sql varchar(1000)
select @sql = 'insert DBTemp (TableName, trxYearMonthStart, nmbrtrx) '+
'select '+@ProcessTable+','+
'CONVERT(varchar(6), a.'+@SelectedColumn+', 112)+''01'','+
'count(*) '+
'from '+@ProcessTable+' a '+
'left outer join history h on h.TableName = '''+@ProcessTable+''' '+
'and CONVERT(varchar(6), h.'+@SelectedColumn+', 112)+''01'' = CONVERT(varchar(6), a.'+@SelectedColumn+', 112)+''01'' '+
'where h.TableName IS NULL '+
'group by CONVERT(varchar(6), a.'+@SelectedColumn+', 112)+''01'''
exec (@sql)
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2003 at 8:00 am
you will require quotes around @ProcessTable in the column list (i.e. 'select '''+ @ProcessTable +''', ...... )
and square brackets in the join clause ( if you use table names with spaces )
January 27, 2003 at 8:02 am
you will require quotes around @ProcessTable in the column list (i.e. 'select '''+ @ProcessTable +''', ...... )
and square brackets in the join clause ( if you use table names with spaces )
January 28, 2003 at 4:16 pm
Thanks for your help. If running statement below I get a syntax error but cannot find out which one. Someone has an idea?
declare @sql varchar(1000)
select @sql = 'INSERT DBCleanerTemp (TableName, trxYearMonthStart, nmbrtrx)'
select @sql = @sql + ' ''' + @ProcessTable + ''''
select @sql = @sql + ', '' + CONVERT(varchar(6), ' + @SelectedColumn + ', 112) + ''01'''
select @sql = @sql + ', count(*)'
select @sql = @sql + ' from ' + @ProcessTable
select @sql = @sql + ' group by '' + CONVERT(varchar(6), ' + @SelectedColumn + ', 112) + ''01'''
print @sql
exec (@sql)
INSERT DBCleanerTemp (TableName, trxYearMonthStart, nmbrtrx) 'opms..Transactions', ' + CONVERT(varchar(6), TransactionDateStamp, 112) + '01', count(*) from opms..Transactions group by ' + CONVERT(varchar(6), TransactionDateStamp, 112) + '01'
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'opms..Transactions'.
mipo
January 29, 2003 at 3:42 am
You are missing the 'select' keyword
declare @sql varchar(1000)
select @sql = 'INSERT DBCleanerTemp (TableName, trxYearMonthStart, nmbrtrx)'
select @sql = @sql + ' Select ''' +
@ProcessTable + ''''
select @sql = @sql + ', '' + CONVERT(varchar(6), ' + @SelectedColumn + ', 112) + ''01'''
select @sql = @sql + ', count(*)'
select @sql = @sql + ' from ' + @ProcessTable
select @sql = @sql + ' group by '' + CONVERT(varchar(6), ' + @SelectedColumn + ', 112) + ''01'''
print @sql
exec (@sql)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply