Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert Statement not working

Status
Not open for further replies.

arodri

Technical User
Jul 9, 2010
121
US
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:


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
 
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
 
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'.
 
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
 
Yeah I tried that and that's not it.

Thanks for the idea though.
 
Do you have permission or have you set permission to INSERT into the linked server table?

daveJam

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

Code:
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
 
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:

Code:
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.
 
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 = '

Code:
Insert Into dbo.LubeInventory(Site,Item,Qty,Type)
Select site,item,qty,type 
From
 
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
 
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.
 
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
 
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.
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top