September 20, 2010 at 2:00 pm
Hi all,
I'm looking to automate the following process into a single transaction that can run. At the moment, we manually create set of results from an Excel spreadsheet of subscribers who wish to be unsubscribed, but I imagine the solution will involve creation of a new table, an SSIS task that imports the data from the .xls into that table, and a then a simple select to get the unsubscribers.
But firstly I'd like help putting this all in ONE transaction.
We generate a result set of those unsubscribers (by number) as follows
SELECT *
FROM (
SELECT '26724748719' AS PHONE UNION
SELECT '27837649964' AS PHONE UNION
SELECT '27824635373' AS PHONE UNION
SELECT '27729281470' AS PHONE
) a
---------- each subscriber number had to be MANUALLY entered!!----
then we verify the unsubscription....
INNER JOIN Outqueue2 b
ON b.phone = a.phone AND
b.msg = 'Your membership has been cancelled, DO NOT REPLY.'
ORDER BY a.phone
/*********************************************************************************************************************************************
**********************************************************************************************************************************************
*********************************************************************************************************************************************/
--DBSRVR1.Outnow
Then we confirm the data and insert...
---------------------------
-- Insert Confirmation MT
---------------------------
GO
INSERT INTO DBSRVR2.Queues.dbo.Outqueue
( phone, routing, service, keyword, keyid, msg, result, origin )
SELECT a.phone, c.freeOffAggMt AS routing,
ISNULL(d.service, 0) AS service,
ISNULL(d.keyword, 0) AS keyword,
ISNULL(d.keyid, 0) AS keyid,
'Your membership has been cancelled, DO NOT REPLY.' AS msg,
61 AS result, 3 AS origin
FROM (
SELECT '26724748719' AS PHONE UNION
SELECT '27837649964' AS PHONE UNION
SELECT '27824635373' AS PHONE UNION
SELECT '27729281470' AS PHONE
) a
INNER JOIN DBSRVR1.OUTNOW.DBO.Phonelist b (NOLOCK)
ON b.phone = a.phone
INNER JOIN DBSRVR1.OUTNOW.DBO.DefineMtRoutes c (NOLOCK)
ON c.aggregator = b.aggregator AND
c.network = b.network
LEFT JOIN DBSRVR1.OUTNOW.DBO.Services d (NOLOCK)
ON d.userid = b.userid AND
d.service = b.srvspref
/*********************************************************************************************************************************************
**********************************************************************************************************************************************
*********************************************************************************************************************************************/
---------------------------
-- Insert into BLOCK
---------------------------
GO
INSERT INTO DBSRVR2.QUEUES.DBO.Block
( phone, aggregator, comment )
SELECT x.phone, 0 AS aggregator, 'WASP complaint (BKM)' AS comment
-- SELECT COUNT(*) AS total
FROM (
SELECT '27724748719' AS PHONE UNION
SELECT '26724748719' AS PHONE UNION
SELECT '27837649964' AS PHONE UNION
SELECT '27824635373' AS PHONE UNION
SELECT '27729281470' AS PHONE
) x
LEFT JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (y.phone IS NULL)
And finally verify....
---------------------------
-- Check results
---------------------------
GO
UPDATE y
SET comment = 'WASPA complaint (BKM)'
-- SELECT y.*
FROM (
SELECT '26724748719' AS PHONE UNION
SELECT '27837649964' AS PHONE UNION
SELECT '27824635373' AS PHONE UNION
SELECT '27729281470' AS PHONE
) x
INNER JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (comment LIKE '%comment provided')
Cheers,
BLB
September 20, 2010 at 2:58 pm
I would create a stored procedure which starts with getting data into sql server from excel:
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
then add the rest of your code and schedule it to run at needed interval.
September 20, 2010 at 3:10 pm
AlexSQLForums (9/20/2010)
I would create a stored procedure which starts with getting data into sql server from excel:SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
then add the rest of your code and schedule it to run at needed interval.
Ahhhhhhhhh yes, I'd forgotten about OpenRowset - and the code itself, what clean up would it need to squeeze into a tran?
September 21, 2010 at 8:13 am
it would look something like this:
do not run this on production since it was just written and never tested.
you can have a static table but in this case it's a temp table.
create proc import_unsubs as
IF OBJECT_ID('tempdb..#phone') IS NOT NULL
BEGIN
drop table #phone
END
ELSE
BEGIN
CREATE TABLE #phone(vcphone varchar(11))
END
--Start Transaction
BEGIN TRANSACTION
--sample
SELECT * INTO #phone FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\phone.xls', 'SELECT * FROM [unsubs$]')
--check if step succeeds otherwise raiserror and rollback transaction.
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in importing unsubs from phone.xls.', 16, 1)
RETURN
END
select vcphone from #phone INNER JOIN Outqueue2 b
ON b.phone = a.phone AND
b.msg = 'Your membership has been cancelled, DO NOT REPLY.'
ORDER BY a.phone
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in joining #phone-Outqueue2.', 16, 1)
RETURN
END
INSERT INTO DBSRVR2.Queues.dbo.Outqueue
( phone, routing, service, keyword, keyid, msg, result, origin )
SELECT a.phone, c.freeOffAggMt AS routing,
ISNULL(d.service, 0) AS service,
ISNULL(d.keyword, 0) AS keyword,
ISNULL(d.keyid, 0) AS keyid,
'Your membership has been cancelled, DO NOT REPLY.' AS msg,
61 AS result, 3 AS origin
FROM (select vcphone from #phone)a
INNER JOIN DBSRVR1.OUTNOW.DBO.Phonelist b (NOLOCK)
ON b.phone = a.phone
INNER JOIN DBSRVR1.OUTNOW.DBO.DefineMtRoutes c (NOLOCK)
ON c.aggregator = b.aggregator AND
c.network = b.network
LEFT JOIN DBSRVR1.OUTNOW.DBO.Services d (NOLOCK)
ON d.userid = b.userid AND
d.service = b.srvspref
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error inseting into DBSRVR2.Queues.dbo.Outqueue.', 16, 1)
RETURN
END
/*********************************************************************************************************************************************
**********************************************************************************************************************************************
*********************************************************************************************************************************************/
---------------------------
-- Insert into BLOCK
---------------------------
INSERT INTO DBSRVR2.QUEUES.DBO.Block
( phone, aggregator, comment )
SELECT x.phone, 0 AS aggregator, 'WASP complaint (BKM)' AS comment
-- SELECT COUNT(*) AS total
FROM (select vcphone from #phone)x
LEFT JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (y.phone IS NULL)
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error inserting into DBSRVR2.QUEUES.DBO.Block.', 16, 1)
RETURN
END
UPDATE y
SET comment = 'WASPA complaint (BKM)'
-- SELECT y.*
FROM (select vcphone from #phone) x
INNER JOIN Block y (NOLOCK)
ON y.phone = x.phone AND
y.aggregator = 0
WHERE (comment LIKE '%comment provided')
-- Rollback the transaction if there were any errors
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error updating Y.', 16, 1)
RETURN
END
--commands completed successfully
--commit the transaction....
COMMIT Transaction
drop table #phone
September 21, 2010 at 9:52 am
Looks great Alex! Would I be able to export the results out to a csv/xls for reporting?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply