Create a table from an EXEC statement - Driving me mad!!

Question

Post reply

Create a table from an EXEC statement - Driving me mad!!

Adam Feneley

Valued Member

Points: 59
More actions
September 29, 2006 at 3:11 am

#115751

I have an application that has been designed using a number of DB's for different "instances" of the application. These DB's contain the same tables etc, but hold information for a specific business area. The application works fins as the majority of the time users are only using on instance.
I now have the need to span these different DB's. I have created a table to hold a list of all the DB's so that I can easliy add a new entry if we create a new "instance".
I am using the below code to create a table depending on the database that has been selected. The codes runs fine as it is (Insert into statement commented out), but fails as soon as I try to insert the result from the EXEC statement into a table.
It's driving me crazy, can anyone spot what I have done wrong?
DECLARE @InstanceDB as varchar(100)
DECLARE @InstanceTable TABLE (Code varchar(100),FinancialYear varchar(100),ServiceID int)
Update Spreadsheet.Upload_Data
Set CC = left(CC_Service_ID,21)
Update Spreadsheet.Upload_Data
Set ServiceID = right(CC_Service_ID,5)
--Find which db the uploaded data is for
SELECT TOP 1 @InstanceDB = a.InstanceDB
FROM InternalInvoicing_ReportData.dbo.InstanceList as a
RIGHT OUTER JOIN Spreadsheet.Upload_Data
ON a.InstanceName = Spreadsheet.Upload_Data.Instance_Name
Print @InstanceDB
SET @InstanceDB = 'InternalInvoicing_' + @InstanceDB
--Insert Into @InstanceTable(FinancialYear,Code,ServiceID)
--Select *
--From (
EXEC('SELECT FLATHIERARCHY.FinancialYear,
CostCentre.Code,
CostCentreService.ServiceID
FROM ' + @InstanceDB + '.dbo.FlatHierarchy() AS FLATHIERARCHY
LEFT JOIN ' + @InstanceDB + '.dbo.HierarchyCostCentre as HierarchyCostCentre
ON HierarchyCostCentre.HierarchyID = FLATHIERARCHY.ID
LEFT JOIN ' + @InstanceDB + '.dbo.CostCentre as CostCentre
ON CostCentre.ID = HierarchyCostCentre.CostCentreID
LEFT JOIN ' + @InstanceDB + '.dbo.CostCentreService as CostCentreService
ON CostCentreService.HierarchyCostCentreID = HierarchyCostCentre.ID')
--)
Thanks,
Adam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

INSERT INTO table variable from EXEC not allowed. It can be done on Temptables.

Try this out

DECLARE @InstanceDB as varchar(100)

CREATE TABLE #InstanceTable (Code varchar(100),FinancialYear varchar(100),ServiceID int)

---DECLARE @InstanceTable TABLE (Code varchar(100),FinancialYear varchar(100),ServiceID int)

Update Spreadsheet.Upload_Data

Set CC = left(CC_Service_ID,21)

Update Spreadsheet.Upload_Data

Set ServiceID = right(CC_Service_ID,5)

--Find which db the uploaded data is for

SELECT TOP 1 @InstanceDB = a.InstanceDB

FROM InternalInvoicing_ReportData.dbo.InstanceList as a

RIGHT OUTER JOIN Spreadsheet.Upload_Data

ON a.InstanceName = Spreadsheet.Upload_Data.Instance_Name

Print @InstanceDB

SET @InstanceDB = 'InternalInvoicing_' + @InstanceDB

Insert Into @InstanceTable(FinancialYear,Code,ServiceID)

EXEC('SELECT FLATHIERARCHY.FinancialYear,

CostCentre.Code,

CostCentreService.ServiceID

FROM ' + @InstanceDB + '.dbo.FlatHierarchy() AS FLATHIERARCHY

LEFT JOIN ' + @InstanceDB + '.dbo.HierarchyCostCentre as HierarchyCostCentre

ON HierarchyCostCentre.HierarchyID = FLATHIERARCHY.ID

LEFT JOIN ' + @InstanceDB + '.dbo.CostCentre as CostCentre

ON CostCentre.ID = HierarchyCostCentre.CostCentreID

LEFT JOIN ' + @InstanceDB + '.dbo.CostCentreService as CostCentreService

ON CostCentreService.HierarchyCostCentreID = HierarchyCostCentre.ID')

Ram

Adam Feneley Valued Member Points: 59 More actions · Answer 2

Thanks the temp table route seems to work fine. I have found that you can use table variables in an exec statements, but they need to be declared in the EXEC statement itself and they doesn't exist after the EXEC statement! So I think this has limited use.

Thanks,

Adam

Sergiy SSC Guru Points: 110208 More actions · Answer 3

I believe it was a typo in Ram's script.

You must read:

Insert Into #InstanceTable(FinancialYear,Code,ServiceID)

_____________
Code for TallyGenerator

Ram Ram Ten Centuries Points: 1079 More actions · Answer 4

Ram Ram

Ten Centuries

Points: 1079

September 29, 2006 at 4:46 am

#663272

Thanks Sergiy! Good catch

Ram