将EXEC语句传递给APPLY作为参数
问题描述:
我需要从多个数据库中获取具有相同模式的表的数据。为此,我在其中一个数据库中创建了这些表的同义词。数据库的数量将随着时间而增长。所以,抓取数据的过程应该是灵活的。我写了下面的代码片段来解决问题:将EXEC语句传递给APPLY作为参数
WHILE @i < @count
BEGIN
SELECT @synonymName = [Name]
FROM Synonyms
WHERE [ID] = @i
SELECT @sql = 'SELECT TOP (1) *
FROM [dbo].[synonym' + @synonymName + '] as syn
WHERE [syn].[Id] = tr.[Id]
ORDER BY [syn].[System.ChangedDate] DESC'
INSERT INTO @tmp
SELECT col1, col2
FROM
(
SELECT * FROM TableThatHasRelatedDataFromAllTheSynonyms
WHERE [Date] > @dateFrom
) AS tr
OUTER APPLY (EXEC(@sql)) result
SET @i = @i + 1
END
我也很欣赏,了解如何简化解决方案的任何想法。
答
我的解决方案很简单。只需将所有查询放入字符串并执行它即可。不幸的是,它的工作速度比复制/过去所有同义词的代码慢3倍。
WHILE @i < @count
BEGIN
SELECT @synonymName = [Name]
FROM Synonyms
WHERE [ID] = @i
SELECT @sql = 'SELECT col1, col2
FROM
(
SELECT * FROM TableThatHasRelatedDataFromAllTheSynonyms
WHERE [Date] > ''' + @dateFrom + '''
) AS tr
OUTER APPLY (SELECT TOP (1) *
FROM [dbo].[synonym' + @synonymName + '] as syn
WHERE [syn].[Id] = tr.[Id]
ORDER BY [syn].[System.ChangedDate] DESC) result'
INSERT INTO @tmp
EXEC(@sql)
SET @i = @i + 1
END
答
实际上,最好将所有表中的数据导入到一个表中(可能需要额外的源表名称列)并使用它。导入可以通过SP或SSIS包来执行。
关于最初的问题 - 你可以通过TVF包装执行exec语句(使用exec ..进入其中)来实现它。
UPD:正如在注释中注意到,exec在TVF内部不起作用。所以,如果你真的不想要更改数据库结构,你需要使用大量的表,我建议:
- 或从同义词选择所有数据表***到变量(如我看你选择只有一行)并使用它们
- 或者为完整语句(使用插入等)准备动态SQL,并在此使用临时表而不是表变量。
我会试试看,明天希望它能帮助我。你能给出更多关于“SP或SSIS”的细节吗?其实我没有明白你的意思。不幸的是,我无法将所有表格合并为一个,因为它们足够大。 (SELECT * FROM Table1) - 使用Intel Xeon处理器和12 Gb RAM的服务器上的查询需要30到60秒,并且我有多个表。使用“外部应用”查询需要2-3秒的时间 – 2012-04-10 17:54:49
SP - 存储过程。 SSIS - SQL Server集成服务。 TVF - 表值函数。 – oryol 2012-04-11 08:42:11
我试图将查询提取到TVF中时遇到了另一个问题:在函数中无效使用副作用运算符'EXECUTE STRING'。 – 2012-04-12 07:09:58