i trying run script:
declare @client varchar(50) declare @sql varchar(max) declare @dbreporting varchar(500) declare @dbsignet varchar(500) declare @databasename varchar(100) set @sql = '' declare db_cursor cursor select name sys.databases name '%reporting%' , not name '%uat%' , not name '%test%' , not name '%demo%' , not name '%staging%' , not name '%server%' , state_desc <> 'offline' open db_cursor fetch next db_cursor @databasename while @@fetch_status = 0 begin set @client = replace(replace(@databasename, 'sourcingplatform_', ''), '_reporting', '') set @dbsignet = 'sourcingplatform_' + @client + '_signet_tradeflow' set @dbreporting = 'sourcingplatform_' + @client + '_reporting' set @sql = @sql + 'insert sts_branding.[dbo].[s2c_keystats] ([project] ,[datadate] ,[eventtypename] ,[countofallevents] ,[createdwithinlast3months] ,[createdwithinlast6months] ,[createdwithinlast12months]) values select ''' + @client + ''' client, convert(date, getdate()), eventtypename collate latin1_general_ci_as, count(id) countofallevents, (select count(e3.id) ' + @dbreporting + '..reports_sourcing_event e3 datediff(month,createdate, getdate()) <= 3 , e.eventtypename = e3.eventtypename) createdlast3months, (select count(e6.id) ' + @dbreporting + '..reports_sourcing_event e6 datediff(month,createdate, getdate()) > 3 , datediff(month,createdate, getdate()) <= 6 , e.eventtypename = e6.eventtypename) createdlast6months, (select count(e12.id) ' + @dbreporting + '..reports_sourcing_event e12 datediff(month,createdate, getdate()) > 6 , datediff(month,createdate, getdate()) <= 12 , e.eventtypename = e12.eventtypename) createdlast12months, (select count(e13.id) ' + @dbreporting + '..reports_sourcing_event e13 datediff(month,createdate, getdate()) > 12 , e.eventtypename = e13.eventtypename) createdover12months ' + @dbreporting + '..reports_sourcing_event e group eventtypename union ' fetch next db_cursor @databasename end close db_cursor deallocate db_cursor set @sql = substring(@sql, 0, len(@sql) - len('union ')) + ' order client, eventtypename collate latin1_general_ci_as' --print @sql exec(@sql) however, getting syntax error.
i have printed @sql variable , code generated looks me. missing simple here? or way off want achieve?
what want achieve script goes through each db referenced in first select , values , insert them table.
let me know if need anymore information me, @ @ point appreciated.
you should post generated query, think looks like:
insert sts_branding.[dbo].[s2c_keystats] ([project] ,[datadate] ,[eventtypename] ,[countofallevents] ,[createdwithinlast3months] ,[createdwithinlast6months] ,[createdwithinlast12months]) values -- remove this, it's incorrect in combination select select (lots of selects) union insert sts_branding.[dbo].[s2c_keystats] ([project] ,[datadate] ,[eventtypename] ,[countofallevents] ,[createdwithinlast3months] ,[createdwithinlast6months] ,[createdwithinlast12months]) select (lots of selects) this not possible, want union selects not insert. should begin initialling @sql insert statement (outside cursor). inside cursor can use set @sql = @sql + ... doing, without insert statement.
also, please note substring 1 based in sql, not 0 in, example, c#.
Comments
Post a Comment