OPENQUERY SYNTAX-INSERTING INTO TABLE FROM LINKED SERVERS

[eshie003]

I am trying to create a new table within my SQL SVR database [Medicaid_Retro] called [UNM_Claims_REFERRAL_1] based on SQL SVR table [UNM_Claims_REFERRAL] and joining to Oracle via linked server NMPSEDW. Using the code below, I get the following error: "Invalid object name 'Medicaid_Retro.dbo.UNM_Claims_REFERRAL_1"

What is the proper syntax??

INSERT INTO [Medicaid_Retro].[dbo].[UNM_Claims_REFERRAL_1]
SELECT * FROM [Medicaid_Retro].[dbo].[UNM_Claims_REFERRAL] A,
(SELECT * FROM OPENQUERY(NMPSEDW,'SELECT CLM_SRVC_SRC_MBR_ID,
PROV_TAX_ID,CLM_SRVC_UMREF_ID,CLM_SRVC_PROC_CD,CLM_AUD_NBR,
CLM_AUD_LN_NBR
FROM PSDW.TB_PS_FCET_CLM_SRVC')) B
WHERE A.[Claim ID]=B.CLM_AUD_NBR
AND A.[Line_NO]=B.CLM_AUD_LN_NBR

 

[gmmastros]

Does the select query work if you remove the top line (INSERT INTO)?


-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

 

[eshie003]

yes, the select statement does work

 

[gmmastros]

Try...

SELECT * 
[!]INTO [Medicaid_Retro].[dbo].[UNM_Claims_REFERRAL_1][/!]
FROM [Medicaid_Retro].[dbo].[UNM_Claims_REFERRAL] A,
(SELECT * FROM OPENQUERY(NMPSEDW,'SELECT CLM_SRVC_SRC_MBR_ID,
PROV_TAX_ID,CLM_SRVC_UMREF_ID,CLM_SRVC_PROC_CD,CLM_AUD_NBR,
CLM_AUD_LN_NBR
FROM PSDW.TB_PS_FCET_CLM_SRVC')) B
WHERE A.[Claim ID]=B.CLM_AUD_NBR
AND A.[Line_NO]=B.CLM_AUD_LN_NBR

If the table does not exist, "Select into" will create it. If the table already exists, then you need to use "Insert Into Select".

Make sense?

-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

 

[eshie003]

It works! Thanks so much..I knew the syntax was incorrect and couldn't figure it out. You are the best!