Dave Dave - 4 months ago 22
SQL Question

Inserting data into a table from a Dynamic SQL script

I am trying to run this 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 FOR
SELECT name
FROM sys.databases
WHERE name like '%reporting%'
AND NOT Name Like '%UAT%'
AND NOT Name Like '%Test%'
AND NOT Name Like '%Demo%'
AND NOT Name like '%staging%'
AND NOT Name like '%server%'
AND state_desc <> 'offline'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @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 INTO STS_Branding.[dbo].[S2C_KeyStats]
([Project]
,[DataDate]
,[EventTypeName]
,[CountOfAllEvents]
,[CreatedWithinLast3Months]
,[CreatedWithinLast6Months]
,[CreatedWithinLast12Months])
VALUES

SELECT ''' + @Client + ''' AS Client, convert(date, getdate()), EventTypeName collate Latin1_General_CI_AS,
count(id) as CountOfAllEvents,

(select COUNT(e3.ID)
from ' + @DBReporting + '..REPORTS_Sourcing_Event E3
where DATEDIFF(month,CreateDate, GETDATE()) <= 3
and E.EventTypeName = E3.EventTypeName) as CreatedLast3Months,

(select COUNT(e6.ID)
from ' + @DBReporting + '..REPORTS_Sourcing_Event E6
where DATEDIFF(month,CreateDate, GETDATE()) > 3
and DATEDIFF(month,CreateDate, GETDATE()) <= 6
and E.EventTypeName = E6.EventTypeName) as CreatedLast6Months,

(select COUNT(e12.ID)
from ' + @DBReporting + '..REPORTS_Sourcing_Event E12
where DATEDIFF(month,CreateDate, GETDATE()) > 6
and DATEDIFF(month,CreateDate, GETDATE()) <= 12
and E.EventTypeName = E12.EventTypeName) as CreatedLast12Months,

(select COUNT(e13.ID)
from ' + @DBReporting + '..REPORTS_Sourcing_Event E13
where DATEDIFF(month,CreateDate, GETDATE()) > 12
and E.EventTypeName = E13.EventTypeName) as CreatedOver12Months

FROM ' + @DBReporting + '..REPORTS_Sourcing_Event E

Group By EventTypeName

UNION '

FETCH NEXT FROM db_cursor INTO @databasename
END

CLOSE db_cursor
DEALLOCATE db_cursor

SET @sql = substring(@sql, 0, LEN(@sql) - len('UNION ')) + ' ORDER BY Client, EventTypeName collate Latin1_General_CI_AS'

--PRINT @SQL
exec(@SQL)


However, I am getting a syntax error.

I have printed the @SQL variable and the code generated looks good to me. Am I missing something really simple here? or am I way off what I want to achieve?

What I want to achieve is a script that goes through each DB referenced in the first select and get the values and insert them into my table.

Let me know if you need anymore information to help me, any help at all at this point would be greatly appreciated.

Answer

You should post the generated query, but I think it looks something like:

INSERT INTO STS_Branding.[dbo].[S2C_KeyStats]
           ([Project]
           ,[DataDate]
           ,[EventTypeName]
           ,[CountOfAllEvents]
           ,[CreatedWithinLast3Months]
           ,[CreatedWithinLast6Months]
           ,[CreatedWithinLast12Months])
VALUES -- Remove this, it's incorrect in combination with SELECT     
SELECT   (lots of selects)

UNION

INSERT INTO STS_Branding.[dbo].[S2C_KeyStats]
           ([Project]
           ,[DataDate]
           ,[EventTypeName]
           ,[CountOfAllEvents]
           ,[CreatedWithinLast3Months]
           ,[CreatedWithinLast6Months]
           ,[CreatedWithinLast12Months])

SELECT   (lots of selects)

This is obviously not possible, you want to union the selects not the insert. So you should begin by initialling @SQL with the insert statement (outside the cursor). Inside the cursor you can use SET @SQL = @SQL + ... as you are already doing, but without the insert statement.

Also, please note substring is 1 based in SQL, not 0 as in, for example, C#.