Insert Statement not working

[arodri]

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:

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

 

[gmmastros]

Can you post the exact error message you are getting?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

Sure, sql tried to execute the query for a few seconds then gives me the following error:

Msg 156, Level 15, State 1, Line 70
Incorrect syntax near the keyword 'Insert'.

 

[gbaughma]

If that's an exact quote, you need a space between SELECT and *

(probably not it, just stating the obvious)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[arodri]

Yeah I tried that and that's not it.

Thanks for the idea though.

 

[markros]

Instead of EXEC (@SQL) can you try
PRINT @SQL and post the output?

PluralSight Learning Library

 

[davejam]

Do you have permission or have you set permission to INSERT into the linked server table?

daveJam

easy come, easy go!!!

 

[SQLBill]

It 'appears' to me that there is one too many ')'. Try taking out one of these and see if you still get the error:

GROUP BY Sale.Site, SaleItems.item

''))



))  --take out one of these


Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type 
from #SiteWatchTable

-SQLBill


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[arodri]

I thought about the quotes too, but I don't think that's the problem. I tried taking out one of those quotes but I still get the same error. Also, the query works perfectly if I take out the Insert into statment and the
"Select*
INTO
#SWTable
From
"

So I don't think quotes are the problem. I didn't add any extra quotes when I added the two statements.


I did Print(@sql) and here is the output:

Select*
INTO
#SWTable
From

(Select Site,Item, Qty, 'Type'='Balance'
FROM OPENQUERY 
(
LINKEDSERVER,' 
Select Bal as Qty, BalInventory.Site, BalInventory.Item
From BalInventory Where YearMonth = ''21202''
')

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 >= ''02/01/2012''

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 (VNVENTORYITEMS.InventoryID = INVENTORY.ObjID)
 AND (INVENTORYITEMS.SITE = INVENTORY.SITE)
WHERE INVENTORY.LOGDATE >= ''02/01/2012''

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 >= ''02/01/2012''
GROUP BY Sale.Site, SaleItems.item

'))



))


Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type 
from #SWTable 


Drop table #SWTable

We have another query that I've worked on that has the exact same linked server and insert into #SWTable that works just fine when I execute it, so I don't think permissions is the issue.

 

[markros]

If you remove #INTO #SWTable in the above, does it work by its own?

What happens if you create this table in advance and use
INSERT INTO #SWTable
SELECT ...

instead?

PluralSight Learning Library

 

[arodri]

No luck

Here is the message I get:

Msg 102, Level 15, State 1, Line 67
Incorrect syntax near ')'.

I already have the table created that this data needs to go into, so I took out the #SWTable and just put the following code right after SET @SQL = '

Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type 
From

 

[markros]

I suggest using style 112 with convert function as ISO date format is most safe format.

This aside, does the select work on its own? E.g if you don't do any INSERT, does select only work?

PluralSight Learning Library

 

[arodri]

Can you explain the date style I should use? I'm not familiar with them so I'm not sure what you mean.

But yes, the select statement alone without any insert statements works just fine. That's why I'm so confused as to why adding a simple insert statement would mess up the query.

 

[SQLBill]

So you are saying that if you take the INSERT and everything below it out, the code works. And if you run just the select from the INSERT it also works. Is that correct?

If so, it's possible there is a 'hidden' character.

I suggest highlighting everything from the last two close parenthesis to the end of the script and deleting it.

Then re-type (don't copy/paste) the INSERT and DROP TABLE code again.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[markros]

You need to use style 112 when you're using CONVERT function with date as 112 is the only 'safe' format when used with dates (ISO format).

PluralSight Learning Library

 

[arodri]

I deleted everything after the last two close parenthesis and re-typing it but stll no luck.

If I comment out the insert statement everything works normall. The query pulls all the data I need perfectly. But as soon as I add that statement back in it always gives me the same error:

Msg 156, Level 15, State 1, Line 70
Incorrect syntax near the keyword 'Insert'.


Super frustrating!

A better way for me to insert this data into the sql table was to do it via a dtsx package in visual studio, but for some reason when I insert this stored procedure in the package it doesn't detect any columns so I can't map anything.

 

[SQLBill]

Okay, try this....put a GO between the INSERT and the DROP statement. If that doesn't work add a GO between the )) and the INSERT statement.

(Grasping at straws here)

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[arodri]

SQLBill you are a life saver!! I had no idea how I was going to get this if this didn't work.
Adding the "GO" between the )) and the INSERT statement did it.


Thank you sooo much everyone! I really appreciate all of your help.

 

[SQLBill]

Weird....but I'm glad we got it working for you.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875