여러개의 sql 문장을 정해진 시간에 매일 돌려야 하는 일이 있는데 어떻케 해야 하나요?
EM에서 Job을 TSQL 형태로 집어 넣으려니 너무 길어서 하나에 들어 가질 않구 여러개로 쪼개서 넣으니 처음것만 돌다 에러가 나고, DTS에서 빼비작 해 봤는데 이것도 자꾸 에러가 나구 해서 어떻케 하는게 가장 좋은 방법인지 혹시 아시는 분 계시면 좀 도와 주세요.....
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MEMBERSHIP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[membership] GO
set concat_null_yields_null OFF --turn off at end of script GO
CREATE TABLE [dbo].[membership] ( [FRID] [numeric] (10, 0) NOT NULL , [TITLE] [char] (8) NULL, [NAME] [varchar] (65) NULL , [NAME2] [varchar] (65) NULL , [PRFTTL] [varchar] (65) NULL , [EXTLIN] [varchar] (65) NULL , [STNO] [varchar] (10) NULL , [STREET] [varchar] (40) NULL , --now (40) [ADDRESS1] [varchar] (80) NULL, --address1 is the concat of stno + street, now (80) [APT] [varchar] (10) NULL , [CITY] [varchar] (20) NULL , [STATE] [varchar] (4) NULL , [ZIP] [varchar] (8) NULL , [ZIPEXT] [varchar] (4) NULL , [CLSDAT] [datetime] NULL , [PROGRM] [varchar] (4) NOT NULL , [CLSAMT] [decimal](13, 2) NULL , [FNAME] [varchar] (65) NULL , --new length to accommodate alfid values (up from 20, then 40) [LNAME] [varchar] (65) NULL, --new length to accommodate alfid values (up from 30, then 40) [CNTCAT] [char] (6) NULL, --new field from yleoview [ALFID] [char] (65) NOT NULL, [NAMIND] [char] (1) NULL, [EMAIL] [varchar] (80) NULL --added 12/9 ) ON [PRIMARY] GO
insert into membership(frid, title, name, name2, prfttl, extlin, stno, street, address1, apt, city, state, zip, zipext, clsamt, progrm, clsdat, fname, lname, cntcat, alfid, namind, email)
SELECT frdonmst.frid, fradrmst.title, fradrmst.name, fradrmst.name2, fradrmst.prfttl, fradrmst.extlin, fradrmst.stno, fradrmst.street,
"address1" = (RTRIM(ISNULL(fradrmst.stno,'')))+' '+ (RTRIM(ISNULL(fradrmst.street, ' '))),
fradrmst.apt, fradrmst.city, fradrmst.state, fradrmst.zip, fradrmst.zipext, frfinprg.clsamt, frfinprg.progrm, frfinprg.clsdat, fradrmst.fname, fradrmst.lname, yleoview.cntcat, frnamalf.alfid, frnamalf.namind, fradreml.email
FROM {oj ((FUNDPROD_LS.DDEV6.dbo.frdonmst frdonmst INNER JOIN FUNDPROD_LS.DDEV6.dbo.fradrmst fradrmst ON frdonmst.frid = fradrmst.frid and frdonmst.adrseq = fradrmst.adrseq)
INNER JOIN FUNDPROD_LS.DDEV6.dbo.frnamalf frnamalf ON frnamalf.frid = fradrmst.frid and frnamalf.adrseq = fradrmst.adrseq
INNER JOIN FUNDPROD_LS.DDEV6.dbo.frfinprg frfinprg ON fradrmst.frid = frfinprg.frid)
LEFT OUTER JOIN FUNDPROD_LS.DDEV6.dbo.yleoview yleoview ON frdonmst.frid = yleoview.frid
LEFT OUTER JOIN FUNDPROD_LS.DDEV6.dbo.fradreml fradreml ON --outer join for fradreml added 12/9 frdonmst.frid = fradreml.frid}
WHERE frdonmst.donsta NOT LIKE ('D%') AND frdonmst.frid not in (select frid from FUNDPROD_LS.DDEV6.dbo.frsflmst where supcat = 'NOBN') AND ((frfinprg.progrm = 'FC' AND frfinprg.clsamt >= 40 AND frfinprg.clsdat >= '2003-09-30') OR (frfinprg.progrm = 'SC' AND frfinprg.clsamt >= 35 AND frfinprg.clsdat >= '2003-09-30'))
ORDER BY frdonmst.frid GO
update membership set fname = CASE WHEN charindex(';', alfid) > 0 and namind > 1 THEN rtrim(reverse(substring(reverse(alfid),1, CHARINDEX(';', REVERSE(alfid))-1))) WHEN charindex(';', alfid) > 0 and namind = 1 THEN fname END GO
update membership set lname = CASE WHEN CHARINDEX(';', alfid) > 0 and namind > 1 THEN SUBSTRING(alfid, 1, (CHARINDEX(';', alfid)-1)) WHEN charindex(';', alfid) > 0 and namind = 1 THEN lname END GO
update membership set street = LTRIM(RTRIM(street)) GO update membership set stno = LTRIM(RTRIM(stno)) GO update membership set address1 = LTRIM(RTRIM(address1)) GO update membership set fname = UPPER(fname) GO update membership set lname = UPPER(lname) GO
CREATE TABLE namex ( [FRID] [numeric](10, 0) NOT NULL , [NAME] [varchar] (65) NULL , [STNO] [varchar] (10) NULL , [STREET] [varchar] (40) NULL, [STREET1] [varchar] (40) NULL , [ADDRESS1] [varchar] (80) NULL , [PROGRM] [varchar] (4) NOT NULL , [ALFID] [char] (65) NOT NULL ) GO
insert into namex(frid, name, stno, street, street1, address1, progrm, alfid) select frid, name, stno, street, street, address1, progrm, alfid from membership where (street like ('% St %') or street like ('% St') or street like ('% St.') or street like ('% St. %') or street like ('St %') or street like ('%Sq.%') --gets almost all the %Vlg% instances too. Use ' W%' for the rest? or street like ('% St., %') or street like ('% Ave.') or street like ('% Ave') or street like ('% Ave. %') or street like ('% Ave., %') or street like ('% Ave %') or street like ('% Dr %') or street like ('% Dr') or street like ('% Dr.') or street like ('% Dr., %') or street like ('% Dr. %') or street like ('% Rd %') or street like ('% Rd.') or street like ('% Rd') or street like ('% Rd., %') or street like ('% Rd. %') or street like ('% Bl') or street like ('% Blvd') or street like ('% Blvd.') or street like ('% Blvd %') or street like ('% Blvd., %') or street like ('% Blvd. %') or street like ('% Ct') or street like ('% Ct.') or street like ('% Ct. %') or street like ('% Ct., %') or street like ('% Ct %') or street like ('% Plz') or street like ('% Plz.') or street like ('% Plz %') or street like ('% Plz. %') or street like ('% Plz., %') or street like ('% Pkway%') or street like ('% Pkwy%') or street like ('% Pky%') or street like ('% Pk.%') or street like ('% Ln%') or street like ('% Ln.%') or street like ('% Ln.,%') or street like ('% Ln. %')) --('%Ln%') can't use, get Lincoln et al GO
--columns for strings to be taken from street1:
alter table namex add STREET2 varchar(40) NULL alter table namex add STREET3 varchar(40) NULL alter table namex add ADDRESSX varchar(80) NULL GO
update namex set street3 = CASE WHEN RIGHT(RTRIM(street1), 1) in ('E', 'W', 'N', 'S') and SUBSTRING(REVERSE(street1), 2, 1) in (' ', ',', '.') THEN RIGHT(street1, 1) WHEN RIGHT(RTRIM(street1), 2) in ('E.', 'W.', 'N.', 'S.', 'NE', 'NW', 'SE', 'SW', 'So', 'No') --8 and SUBSTRING(REVERSE(street1), 3, 1) in (' ', ',', '.') THEN RIGHT(street1, 2) WHEN RIGHT(RTRIM(street1), 3) in ('So.', 'No.', 'Vlg') --0. Any other 3 char ideas? and SUBSTRING(REVERSE(street1), 4, 1) in (' ', ',', '.') THEN RIGHT(street1, 3) WHEN RIGHT(RTRIM(street1), 4) in ('East', 'West', 'Vlg.', 'Vlge', 'N.E.', 'N.W.', 'S.E.', 'S.W.') --4 and SUBSTRING(REVERSE(street1), 5, 1) in (' ', ',', '.') THEN RIGHT(street1, 4) WHEN RIGHT(RTRIM(street1), 5) in ('North', 'South', 'Vlge.') --3 and SUBSTRING(REVERSE(street1), 6, 1) in (' ', ',', '.') THEN RIGHT(street1, 5) ELSE NULL END GO
--2: Remember, for these replacements, LEN = RTRIM or you'll lose the character at the end of prev string in field
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 2)) WHERE RIGHT(RTRIM(street1), 2) in (' E', ' W', ' N', ' S') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 3)) WHERE RIGHT(RTRIM(street1), 3) in (' NE', ' NW', ' SE', ' SW', ' E.', ' W.', ' N.', ' S.', ' So', ' No') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 4)) WHERE RIGHT(RTRIM(street1), 4) in (' Vlg', ' NW', ' SE', ' SW', ' E.', ' W.', ' N.', ' S.', ' So', ' No') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 5)) WHERE RIGHT(RTRIM(street1), 5) in (' East', ' West', ' Vlg.', ' Vlge', ' N.E.', ' N.W.', ' S.E.', ' S.W.') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 6)) WHERE RIGHT(RTRIM(street1), 6) in (' North', ' South', ' Vlge.') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 1)) WHERE RIGHT(RTRIM(street1), 1) in (',', '`', '-') GO
update namex set street2 = CASE WHEN RIGHT(RTRIM(street1), 2) in ('St', 'Rd', 'Ct', 'Dr', 'Ln', 'Bl') and SUBSTRING(REVERSE(street1), 3, 1) in (' ', ',', '.') THEN RIGHT(street1, 2) WHEN RIGHT(RTRIM(street1), 3) in ('St.', 'Rd.', 'Ct.', 'Dr.', 'Ln.', 'Ave', 'Plz', 'Pk.', 'Pky', 'Vlg', 'Sq.') and SUBSTRING(REVERSE(street1), 4, 1) in (' ', ',', '.') THEN RIGHT(street1, 3) WHEN RIGHT(RTRIM(street1), 4) in ('Blvd', 'Ave.', 'Road', 'Lane', 'Gln.', 'Glen', 'Pky.', 'Pkwy', 'Vlg.', 'Vlge') and SUBSTRING(REVERSE(street1), 5, 1) in (' ', ',', '.') THEN RIGHT(street1, 4) WHEN RIGHT(RTRIM(street1), 5) in ('Blvd.', 'Place', 'Drive', 'Plaza', 'Pkway', 'Vlge.') and SUBSTRING(REVERSE(street1), 6, 1) in (' ', ',', '.') THEN RIGHT(street1, 5) ELSE NULL END GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 3)) WHERE RIGHT(RTRIM(street1), 3) in (' St', ' Dr', ' Rd', ' Ct', ' Bl') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 4)) WHERE RIGHT(RTRIM(street1), 4) in (' St.', ' Rd.', ' Dr.', ' Ct.', ' Ln.', ' Gln.', ' Ave', ' Plz', ' Pk.', ' Pky', ' Vlg', ' Sq.') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 5)) WHERE RIGHT(RTRIM(street1), 5) in (' Ave.', ' Blvd', ' Pky.', ' Pkwy', ' Vlg.', ' Vlge') GO
UPDATE namex SET street1 = LEFT(street1, (LEN(street1) - 6)) WHERE RIGHT(RTRIM(street1), 6) in (' Blvd.', ' Place', 'Drive', 'Plaza', ' Pkwy.', ' Pkway', ' Vlge.') GO
exec searchandreplace6 namex, street2, 'Pk.', 'Park' --run before period replacement and before Parkway exec searchandreplace6 namex, street2, '.', '' exec searchandreplace6 namex, street2, 'St', 'Street' exec searchandreplace6 namex, street2, 'Pkway', 'Parkway' --1st exec searchandreplace6 namex, street2, 'Pkwy', 'Parkway' --2nd exec searchandreplace6 namex, street2, 'Pky', 'Parkway' --3rd exec searchandreplace6 namex, street2, 'Pk', 'Parkway' --4th exec searchandreplace6 namex, street2, 'Dr', 'Drive' exec searchandreplace6 namex, street2, 'Rd', 'Road' exec searchandreplace6 namex, street2, 'Ct', 'Court' exec searchandreplace6 namex, street2, 'Sq', 'Square' exec searchandreplace6 namex, street2, 'Ave', 'Avenue' exec searchandreplace6 namex, street2, 'Vlge', 'Village' --1st exec searchandreplace6 namex, street2, 'Vlg', 'Village' --2nd exec searchandreplace6 namex, street2, 'Vl', 'Village' --3rd exec searchandreplace6 namex, street2, 'Blvd', 'Boulevard' --1st exec searchandreplace6 namex, street2, 'Bl', 'Boulevard' --2nd exec searchandreplace6 namex, street2, 'Plz', 'Plaza' exec searchandreplace6 namex, street3, 'Vlge.', 'Village' --1st exec searchandreplace6 namex, street3, 'Vlge', 'Village' --2nd exec searchandreplace6 namex, street3, 'Vlg.', 'Village' --3rd exec searchandreplace6 namex, street3, 'Vlg', 'Village' --4th exec searchandreplace6 namex, street3, 'Vl', 'Village' --5th
update namex set addressx = (RTRIM(ISNULL(stno,'')))+' '+ (RTRIM(ISNULL(street1, ''))) + ' '+ (RTRIM(ISNULL(street2, '')))+ ' '+(RTRIM(ISNULL(street3, ''))) GO
update membership set address1 = namex.addressx from membership, namex where membership.frid = namex.frid and membership.alfid = namex.alfid and membership.progrm = namex.progrm and membership.progrm = 'SC' GO
update membership set address1 = namex.addressx from membership, namex where membership.frid = namex.frid and membership.alfid = namex.alfid and membership.progrm = namex.progrm and membership.progrm not in ('SC') GO
update membership set address1 = LTRIM(address1) GO
drop table namex GO
|