Help wanted for a complex insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help wanted for a complex insert

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-20 : 17:26:20

Hi all,

I'm looking to automate the following process into a single transaction that can be run as a scheduled job daily. 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 SubscriberNumber
Where 'ActionRequested' = 'unsubscribe'

to get the unsubscribers.

But firstly I'd like help putting the following 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,

Jim

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-21 : 01:37:58

SSIS will definitly be a good solution. And easy one as well.
How and in what kind of information do you receive from subscribers that they want to unsubscribe?

thumbs up for SSIS :)

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-21 : 05:23:03

quote:
Originally posted by slimt_slimt

SSIS will definitly be a good solution. And easy one as well.
How and in what kind of information do you receive from subscribers that they want to unsubscribe?

thumbs up for SSIS :)

Or could I use OpenRowset? And would this code be difficult to place into a single transaction?

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-21 : 09:18:56

quote:
Originally posted by slimt_slimt

SSIS will definitly be a good solution. And easy one as well.
How and in what kind of information do you receive from subscribers that they want to unsubscribe?

thumbs up for SSIS :)

I forgot to say, the info will be int (their mobile/cell no's), and so far, it comes in website format, so at the moment it's literally a copy/paste into a column in Excel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources