If I simply try to INSERT to [##TempTable_ContractNameInfo] based on my OPENQUERY, it works fine. However, when I add the INNER JOIN to [dbo].[##TempTable_AccountBillTo] I get...

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

Why?

Can anyone please help me with this?

Thanks for your review and am hopeful for a reply.

IF	OBJECT_ID('tempdb..##TempTable_AccountBillTo')					IS NOT	NULL	DROP TABLE	##TempTable_AccountBillTo
;
IF	OBJECT_ID('tempdb..##TempTable_ContractNameInfo')				IS NOT	NULL	DROP TABLE	##TempTable_ContractNameInfo
;

--
--	Create Temporary Table [##TempTable_AccountBillTo] which will be used to store the 
--	Account Number and its corresponding Bill To from the following Oracle Tables:
--						[DIVISION]
--						[GROUP_M]
--						[CONTRACT_SPAN]
--
CREATE TABLE	[##TempTable_AccountBillTo]
(
AccountNbr				VARCHAR(10),
BusinessUnit			VARCHAR(2),
BillTo					VARCHAR(2)
)
;

--
--	Load Temp Table [##TempTable_AccountBillTo] with the correct data elements which
--	will be used to identify the Bill To information associated with an Account
--

INSERT
INTO	[dbo].[##TempTable_AccountBillTo]
		(
		AccountNbr,
		BusinessUnit,
		BillTo
		)
SELECT	*
FROM	OPENQUERY
		(RPDMHF,
		'SELECT	DISTINCT
				CASE
					WHEN	DIVISION.BILL_TO	=	''C ''
						THEN	DIVISION.ROLLUP_NBR
					WHEN	DIVISION.BILL_TO	=	''G ''
						THEN	DIVISION.GROUP_NBR
					WHEN	DIVISION.BILL_TO	=	''I ''
						THEN	CONTRACT_SPAN.CONTRACT_NBR
				END AS											"AccountNbr",
				DIVISION.BUSINESS_UNIT							"BusinessUnit",
				DIVISION.BILL_TO
		 FROM	AMIOWN.DIVISION
		 INNER JOIN	AMIOWN.GROUP_M
				ON	GROUP_M.GROUP_NBR				=			DIVISION.GROUP_NBR
 		 INNER JOIN	AMIOWN.CONTRACT_SPAN
				ON	CONTRACT_SPAN.DIVISION_NBR		=			DIVISION.DIVISION_NBR
		 WHERE	(CONTRACT_SPAN.VOID					=			'' ''
		 AND	 CONTRACT_SPAN.YMDEFF				<=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD''))
		 AND	 CONTRACT_SPAN.YMDEND				>=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD'')))
		 OR		(CONTRACT_SPAN.VOID					<>			'' ''
		 AND	 CONTRACT_SPAN.YMDTRANS				<=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD''))
		 AND	 CONTRACT_SPAN.YMDTRANS				>=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD'')))
		'
		)
;

--	DO NOT FORGET TO FILTER OUT TPA GROUPS!!! BUT USE V_LOB IN ORDER TO DO THAT!!! DO NOT DO NOT DO NOT HARD CODE THE TPA GROUPS!!!

--SELECT	*
--FROM	[dbo].[##TempTable_AccountBillTo]
--;

DELETE
FROM	[dbo].[##TempTable_AccountBillTo]
WHERE	[##TempTable_AccountBillTo].[BusinessUnit]		=		'TP'
;

--
--	Create Temporary Table [##TempTable_ContractNameInfo] which will be used to store the 
--	Account Number and its corresponding Name Information from the following Oracle Tables:
--						[MEMBER]
--						[MEMBER_SPAN]
--
CREATE TABLE	[##TempTable_ContractNameInfo]
(
ContractNbr				VARCHAR(10),
MemberNbr				VARCHAR(12),
FirstName				VARCHAR(26),
LastName				VARCHAR(36),
MiddleName				VARCHAR(26),
Prefix					VARCHAR(10),
Suffix					VARCHAR(10),
Gender					VARCHAR(2),
BirthDate				DECIMAL(8,0)
)
;

--
--	Load Temp Table [##TempTable_ContractNameInfo] with the correct data elements which
--	will be used to identify the Contract Information associated with an Account
--

INSERT
INTO	[dbo].[##TempTable_ContractNameInfo]
		(
		ContractNbr,
		MemberNbr,
		FirstName,
		LastName,
		MiddleName,
		Prefix,
		Suffix,
		Gender,
		BirthDate
		)
SELECT	*
FROM	OPENQUERY
		(RPDMHF,
		'SELECT	DISTINCT
				MEMBER.CONTRACT_NBR								"ContractNbr",
				MEMBER.MEMBER_NBR,
				MEMBER.NAME_FIRST,
				MEMBER.NAME_LAST,
				MEMBER.NAME_MIDDLE,
				MEMBER.NAME_PREFIX,
				MEMBER.NAME_SUFFIX,
				MEMBER.SEX,
				MEMBER.YMDBIRTH
		 FROM	AMIOWN.MEMBER
		 INNER JOIN	AMIOWN.MEMBER_SPAN
				ON	MEMBER_SPAN.MEMBER_NBR			=			MEMBER.MEMBER_NBR
				AND	MEMBER_SPAN.CLASS_X				=			''CH''
		 WHERE	(MEMBER_SPAN.VOID					=			'' ''
		 AND	 MEMBER_SPAN.YMDEFF					<=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD''))
		 AND	 MEMBER_SPAN.YMDEND					>=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD'')))
		 OR		(MEMBER_SPAN.VOID					<>			'' ''
		 AND	 MEMBER_SPAN.YMDTRANS				<=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD''))
		 AND	 MEMBER_SPAN.YMDTRANS				>=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD'')))
		'
		)														AS	MemberName
INNER JOIN	[dbo].[##TempTable_AccountBillTo]
		ON	[##TempTable_AccountBillTo].[AccountNbr]	=		[MemberName].[ContractNbr]
;


SELECT	*
FROM	[dbo].[##TempTable_ContractNameInfo]
;

You need to specify the columns you want.. 

Try giving the Open Query an alias (say X) and use the alias (X.*)

Try

INSERT
INTO	[dbo].[##TempTable_ContractNameInfo]
		(
		ContractNbr,
		MemberNbr,
		FirstName,
		LastName,
		MiddleName,
		Prefix,
		Suffix,
		Gender,
		BirthDate
		)
SELECT	MemberName.*
FROM	OPENQUERY
		(RPDMHF,
		'SELECT	DISTINCT
				MEMBER.CONTRACT_NBR								"ContractNbr",
				MEMBER.MEMBER_NBR,
				MEMBER.NAME_FIRST,
				MEMBER.NAME_LAST,
				MEMBER.NAME_MIDDLE,
				MEMBER.NAME_PREFIX,
				MEMBER.NAME_SUFFIX,
				MEMBER.SEX,
				MEMBER.YMDBIRTH
		 FROM	AMIOWN.MEMBER
		 INNER JOIN	AMIOWN.MEMBER_SPAN
				ON	MEMBER_SPAN.MEMBER_NBR			=			MEMBER.MEMBER_NBR
				AND	MEMBER_SPAN.CLASS_X				=			''CH''
		 WHERE	(MEMBER_SPAN.VOID					=			'' ''
		 AND	 MEMBER_SPAN.YMDEFF					<=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD''))
		 AND	 MEMBER_SPAN.YMDEND					>=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD'')))
		 OR		(MEMBER_SPAN.VOID					<>			'' ''
		 AND	 MEMBER_SPAN.YMDTRANS				<=			TO_NUMBER(TO_CHAR(SYSDATE	-	365, ''YYYYMMDD''))
		 AND	 MEMBER_SPAN.YMDTRANS				>=			TO_NUMBER(TO_CHAR(SYSDATE	+	120, ''YYYYMMDD'')))
		'
		)														AS	MemberName
INNER JOIN	[dbo].[##TempTable_AccountBillTo]
		ON	[##TempTable_AccountBillTo].[AccountNbr]	=		[MemberName].[ContractNbr]
;

Why do you need a join, BTW?

Hi,

Check this thread.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/73fdf8fc-d904-480f-8c7b-ffb759212f8b/openquery-with-inner-join?forum=transactsql