|
여러개의 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
|
