Hello,
I have a stored procedure using a linked server that keeps returning a syntax error near the INSERT part of the statment - could someone please look it over and let me know. I've ran the actual query and it works just fine, but after I added the following code It's not working. I just need to take the results from the query and input them into the LubeInventory table but I guess there's something wrong with what I added.
Select*
INTO
#SWTable
From
and
Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type
from #SWTable
Drop table #SWTable
The full code:
I have a stored procedure using a linked server that keeps returning a syntax error near the INSERT part of the statment - could someone please look it over and let me know. I've ran the actual query and it works just fine, but after I added the following code It's not working. I just need to take the results from the query and input them into the LubeInventory table but I guess there's something wrong with what I added.
Select*
INTO
#SWTable
From
and
Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type
from #SWTable
Drop table #SWTable
The full code:
Code:
Begin
DECLARE @SQL VarChar(max)
Declare @MaxDate datetime
Declare @YearMonth int
Set @MaxDate = (Select Convert(DateTime, Stuff([YearMonth], 2, 0, '0') + '01')
FROM OPENQUERY (LINKEDSERVER,'
Select Max(YearMonth) as YearMonth
From Inventory '))
Set @YearMonth = (Select YearMonth
FROM OPENQUERY
(
LINKEDSERVER,'
Select Max(YearMonth) as YearMonth
From Inventory
'))
SET @SQL = '
Select*
INTO
#SWTable
From
(Select Site,Item, Qty, ''Type''=''Balance''
FROM OPENQUERY
(
LINKEDSERVER,''
Select Bal as Qty, Inventory.Site, Inventory.Item
From Inventory Where YearMonth = ''''' + CONVERT(varchar(10), @YearMonth) + '''''
'')
Union ALL
(Select site, item, Qty, ''Type''=''Received''
FROM OPENQUERY
(
LINKEDSERVER,''
SELECT Sum(Inventoryitems.Received) as Qty, INVENTORYITEMS.SITE as site, InventoryItems.item
FROM INVENTORYITEMS INNER JOIN INVENTORY ON (INVENTORYITEMS.InventoryID = INVENTORY.ObjID)
AND (INVENTORYITEMS.SITE = INVENTORY.SITE)
WHERE INVENTORY.LOGDATE >= ''''' + CONVERT(char(10), @MaxDate, 101) + '''''
Group by INVENTORYITEMS.SITE, InventoryItems.item
''))
Union ALL
(Select site, item, Qty, ''Type''=''Adjusted''
FROM OPENQUERY
(
LINKEDSERVER,''
SELECT Sum((Inventoryitems.Adjusted)) as Qty, INVENTORYITEMS.SITE as site, InventoryItems.item
FROM INVENTORYITEMS INNER JOIN INVENTORY ON (INVENTORYITEMS.InventoryID = INVENTORY.ObjID)
AND (INVENTORYITEMS.SITE = INVENTORY.SITE)
WHERE INVENTORY.LOGDATE >= ''''' + CONVERT(char(10), @MaxDate, 101) + '''''
Group by INVENTORYITEMS.SITE, InventoryItems.item
'')
Union ALL
(Select Site, Item, Qty, ''type''=''Sales''
FROM OPENQUERY
(
LINKEDSERVER,''
SELECT Sum(SALEITEMS.QTY) as Qty, Sale.Site, SaleItems.item
FROM SALE INNER JOIN SALEITEMS ON (SALE.OBJID = SaleItems.saleid) AND (SALE.SITE = SaleItems.SITE)
WHERE SALE.LOGDATE >= ''''' + CONVERT(char(10), @MaxDate, 101) + '''''
GROUP BY Sale.Site, SaleItems.item
''))
))
Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type
from #SiteWatchTable
Drop table #SiteWatchTable
'
Exec(@SQL)
End