I am writing a sql script in sql server that creates dynamic tables based on data. I was initially getting an error " The object name 'GBIPS-I-DB324D.CoreReferenceStaging.dbo.FinData2000_1' contains more than the maximum number of prefixes. The maximum is 2." while running the original query. GBIPS-I-DB324D is linked server object.
I tried to use execute statement and now getting an error Incorrect syntax near the keyword 'USE'.
Original Query
BEGIN
set nocount on
declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
set @startYear = 2000;
set @startQuarter = 1;
while(@startYear < 2018)
begin
set @startQuarter = 1;
while(@startQuarter < 5)
begin
set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
set @sql = '
drop table [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + '];
create table [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit );
insert into [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + ']
select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
where YEAR(fi.periodenddate) = ' + cast(@startYear as varchar) + ' and QUARTER(fi.periodenddate) = ' + cast(@startQuarter as varchar)
EXEC sp_executesql @sql
set @startQuarter += 1
end
set @startYear += 1;
end
end
modified query
BEGIN
set nocount on
declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
set @startYear = 2000;
set @startQuarter = 1;
while(@startYear < 2018)
begin
set @startQuarter = 1;
while(@startQuarter < 5)
begin
set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
set @sql = '
EXECUTE (USE CoreReferenceStaging;drop table [dbo].[' + @tableName + ']) AT [GBIPS-I-DB324D]
EXECUTE (USE CoreReferenceStaging;create table [dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit )) AT [GBIPS-I-DB324D]
insert into [GBIPS-I-DB324D].[CoreReferenceStaging].[dbo].[' + @tableName + ']
select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
where YEAR(fi.periodenddate) = ' + cast(@startYear as varchar) + ' and QUARTER(fi.periodenddate) = ' + cast(@startQuarter as varchar)
EXEC sp_executesql @sql
set @startQuarter += 1
end
set @startYear += 1;
end
end
