CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Adam
Adam

Reputation: 1493

Invalid Object Name in Stored Procedure when using insert into

EDIT:

I have two tables Table 1 and Table 2 that are linked by GUIDs and I want to search Table 2 for any GUIDs that are not in Table 1 and insert them

I am trying to use an insert into select from where statement as shown below:

insert into WebCatalog.Published.DemoTracking (RCompanyLink, Purchased, DemoDate)
Select RC.Link, Purchased = 0, DemoDate = GETDATE()
from WebCatalog.Published.RCompany RC
where RC.Link != WebCatalog.Published.DemoTracking.RCompanyLink and RC.DemoLicense = 1

I keep getting an Invalid Object Name error when it is in a stored procedure and a the multipart identifier cannot be bound in reference to 

WebCatalog.Published.DemoTracking.RCompanyLink

error when I try to execute it by itself.

Am I formatting my statement incorrectly?

Upvotes: 0

Views: 922

Answers (2)

Andy
Andy

Reputation: 8562

The problem is that in SQL you must tell the server how the data relates; it doesn't infer it automatically. What you have looks like it might work in an orm and using Linq2Sql (a .Net technology).

I'd recommend using NOT EXISTS which I believe performs better than IN.

INSERT INTO webcatalog.published.demotracking 
            (rcompanylink, 
             purchased, 
             demodate) 
SELECT RC.link, 
       Purchased = 0, 
       DemoDate = Getdate() 
FROM   webcatalog.published.rcompany RC 
WHERE  NOT EXISTS (SELECT *
         FROM   webcatalog.published.demotracking dt WHERE dt.rcompanylink = RC.link) 
       AND RC.demolicense = 1

Upvotes: 0

Satpal
Satpal

Reputation: 133403

Instead of 

 RC.Link != WebCatalog.Published.DemoTracking.RCompanyLink

Use

 RC.Link not in ( Select RCompanyLink from WebCatalog.Published.DemoTracking )

Complete code

INSERT INTO webcatalog.published.demotracking 
            (rcompanylink, 
             purchased, 
             demodate) 
SELECT RC.link, 
       Purchased = 0, 
       DemoDate = Getdate() 
FROM   webcatalog.published.rcompany RC 
WHERE  RC.link NOT IN (SELECT rcompanylink 
                       FROM   webcatalog.published.demotracking) 
       AND RC.demolicense = 1

Upvotes: 1

Related Questions