Error in SQL Server 2005 Syntax

Question

Here is the SQL:

CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int)

INSERT INTO dbo.TmpFeesToRules1(Name, LookupId)
SELECT DISTINCT Name, 0 FROM Lending.Fee
UNION SELECT DISTINCT Name, 0 FROM Lending.ApplicationFee

INSERT INTO dbo.tblLookup (LookupType, LookupCode, LookupDesc, EditFlag, DeletedFlag, DefaultFlag)
SELECT 'FEE_CODE', Name,  Name, 0, 0, 0
FROM dbo.TmpFeesToRules1

UPDATE dbo.TmpFeesToRules1
SET
    LookupId = L.LookupID
FROM
    dbo.tblLookup L
INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = L.LookupDesc
WHERE
    L.LookupType = 'FEE_CODE'

CREATE TABLE dbo.TmpFeesToRules2(FeeId int, Name varchar)

INSERT INTO dbo.TmpFeesToRules2(FeeId, Name)
SELECT FeeId, Name FROM Lending.ApplicationFee

GO

UPDATE Lending.ApplicationFee 
SET Lending.ApplicationFee.FeeTypeId = T.LookupId
FROM dbo.TmpFeesToRules2 TT
INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
WHERE Lending.ApplicationFee.FeeId = TT.FeeId

DROP TABLE dbo.TmpFeesToRules1
DROP TABLE dbo.TmpFeesToRules2

Here is the error:

Invalid object name 'dbo.TmpFeesToRules2'.
Execute failed, return code: 1

Any ideas?

Martin Smith · Accepted Answer · 2010-07-28 13:05:39Z

6

Nothing to do with Go or semicolons,

You are updating Lending.ApplicationFee but it doesn't appear in the FROM clause.

UPDATE Lending.ApplicationFee 
SET Lending.ApplicationFee.FeeTypeId = T.LookupId
FROM dbo.TmpFeesToRules2 TT
INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
WHERE Lending.ApplicationFee.FeeId = TT.FeeId

I guess this should be something like

UPDATE Lending.ApplicationFee 
SET Lending.ApplicationFee.FeeTypeId = T.LookupId
FROM Lending.ApplicationFee join dbo.TmpFeesToRules2 TT ON <Some Condition>
INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
WHERE Lending.ApplicationFee.FeeId = TT.FeeId

answered Jul 28, 2010 at 13:05

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SWeko Over a year ago

And I think this is where the error is :) in spite of all the CREATE/INSERT/GO debate

Adam Houldsworth Over a year ago

+1 I've deleted my input into that debate to avoid clouding the answer.

Martin Smith Over a year ago

You should get a badge for that I think!

Adam Houldsworth Over a year ago

@Martin I didn't have enough votes to get that "delete your own post" badge, but up-voting you gave me Civic Duty lol, btw you don't work in Manchester do you?

Martin Smith Over a year ago

Nope, no Manchester connections. It seems there's quite a few Martin Smiths about.

|

SWeko · Accepted Answer · 2010-07-28 12:59:40Z

2

Instead of

CREATE TABLE dbo.TmpFeesToRules2(FeeId int, Name varchar)

INSERT INTO dbo.TmpFeesToRules2(FeeId, Name)
SELECT FeeId, Name FROM Lending.ApplicationFee

you could just do:

SELECT FeeId, Name 
INTO dbo.TmpFeesToRules2 
FROM Lending.ApplicationFee

That will create and populate the table in one step

answered Jul 28, 2010 at 12:59

SWeko

30.9k11 gold badges74 silver badges107 bronze badges

1 Comment

SWeko Over a year ago

I don't think so. And I don't think the problem is in the GO. GO is not a SQL command per se, it just tells the tool to send the code to the database

Collectives™ on Stack Overflow

Error in SQL Server 2005 Syntax

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related