set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[LOAD_ORG_MEMBERS] 
	
AS
BEGIN

	SET NOCOUNT ON;

	if exists (select * from dbo.sysobjects where id = object_id(N'[ORG_MEMBERS_OLD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [ORG_MEMBERS_OLD]
	    
	if exists (select * from dbo.sysobjects where id = object_id(N'[TEMP_ORG_MEMBERS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [TEMP_ORG_MEMBERS]


	CREATE TABLE [TEMP_ORG_MEMBERS](
	[CONSTIT_ID] [char](10) NOT NULL,
	[SORT_NAME] [varchar](200) NULL,
	[FULLNAME] [varchar](200) NULL,
	[LASTNAME] [varchar](200) NULL,
	[FIRSTNAME] [varchar](200) NULL,
	[MIDDLENAME] [varchar](200) NULL,
	[BIRTHDATE] [varchar](50) NULL,
	[BUSINESS_PHONE] [varchar](100) NULL,
	[HOME_PHONE] [varchar](100) NULL,
	[FAX] [varchar](100) NULL,
	[OTHER_PHONE] [varchar](100) NULL,
	[CELL_PHONE] [varchar](100) NULL,
	[ADDRESS1] [varchar](200) NULL,
	[ADDRESS2] [varchar](200) NULL,
	[ADDRESS3] [varchar](200) NULL,
	[CITY] [varchar](100) NULL,
	[STATE] [varchar](100) NULL,
	[POSTAL_CODE] [varchar](100) NULL,
	[COUNTRY] [varchar](100) NULL,
	[EMAIL_ADDRESS] [varchar](250) NULL,
	[DUES] [varchar](25) NULL,
	[MEMBERSINCE] [varchar](50) NULL,
	[COMPANY_NAME] [varchar](250) NULL,
	[TITLE] [varchar](250) NULL,
	[SECTION] [varchar](250) NULL,
	[PRIMARYTECHDISC] [varchar](250) NULL,
	[ACCOUNTNAME] [varchar](250) NULL
) ON [PRIMARY]

    -- Insert statements for procedure here
	INSERT INTO [TEMP_ORG_MEMBERS]
           ([CONSTIT_ID]
           ,[SORT_NAME]
           ,[FULLNAME]
           ,[LASTNAME]
           ,[FIRSTNAME]
           ,[MIDDLENAME]
           ,[BIRTHDATE]
           ,[BUSINESS_PHONE]
           ,[HOME_PHONE]
           ,[FAX]
           ,[OTHER_PHONE]
           ,[CELL_PHONE]
           ,[ADDRESS1]
           ,[ADDRESS2]
           ,[ADDRESS3]
           ,[CITY]
           ,[STATE]
           ,[POSTAL_CODE]
           ,[COUNTRY]
           ,[EMAIL_ADDRESS]
           ,[DUES]
           ,[MEMBERSINCE]
           ,[COMPANY_NAME]
           ,[TITLE]
           ,[SECTION]
           ,[PRIMARYTECHDISC]
			,[ACCOUNTNAME])
	(SELECT * 
FROM OPENQUERY(PROD,'select distinct  
  m.u##CONSTIT_ID, 
  m.SORT_NAME,
   c.IN_LABELNAME,
   c.LASTNAME,
   case when trim(c.nickname) is not null then c.nickname else c.firstname end as firstname,
   c.MI,
   TO_CHAR(c.birth_dt,''DD-MON''),
   c.phone1 as business_phone,
   c.phone2 as home,
   c.phone3 as fax,
   c.phone4 as alt_home,
   c.phone5 as cell,
   a.addr_attn,
   a.ADDR_LINE1,
   a.ADDR_LINE2,
   a.CITY,
   a.STATE,
   a.ZIP,
   a.COUNTRY,
   e.E_ADDR,
   DECODE (m.paid_in_full,1,''Paid'',''Unpaid'') as Paid,   
   TO_CHAR(m.JOIN_DT,''DD-MON-YYYY''),
   c.CO_FULLNAME,
   c.indiv_title,
   m1.section_name,
   c1.CODE_DESC,
    ''AspNetSqlMembershipProvider:''||m.u##constit_id
from MI.MBMEMBER m,
MI.CONSTIT c,
MI.EADDRESS e,
MI.ADDRESS a,
(select m2.u##constit_id, s.section_name from MI.mbmember m2, MI.mbsecreg s where
m2.u##class_code = ''SECTION'' 
and m2.u##code_value = s.u##section
and m2.start_dt <= SYSDATE
and m2.end_dt >= SYSDATE
and m2.cancel_dt is null
and m2.cancel_reason = '' ''
and m2.u##member_type = ''PRIMARY'') m1,
(select c2.u##constit_id, t.code_desc from MI.cncatcod c2, MI.CNCODTAB t where 
t.u##cat_code = ''SCLVL1PRIM''
and c2.u##cat_code = t.u##cat_code
and c2.u##code_value = t.u##code_value) c1
where m.u##class_code = ''ORG''
and m.start_dt <= SYSDATE
and m.end_dt >= SYSDATE
and m.u##constit_id = c.u##constit_id
and m.u##constit_id = e.u##constit_id
and m.u##constit_id = m1.u##constit_id (+)
and e.u##e_label=''EMAIL''
and e.e_default = 1
and m.u##mail_iaid = a.u##iaid
and m.cancel_dt is null
and m.cancel_reason = '' ''
and m.u##CONSTIT_ID = c1.u##constit_id (+)'));

EXEC SP_RENAME 'ORG_MEMBERS', 'ORG_MEMBERS_OLD'
EXEC SP_RENAME 'TEMP_ORG_MEMBERS', 'ORG_MEMBERS'


	
END

quote:

---

Originally posted by asgast

you don't go playing with tables like this on sql server

make 2 tables
in the begining of procedure truncate the data from the _old one
repopulate the ORG_MEMBERS table

will work faster, will work much stable

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

---

quote:

---

Originally posted by asgast

you don't go playing with tables like this on sql server
<snip>

---