Insert result from a SQL View to another SQL table

Hi!

This SQL View returns almost 700 records:

SELECT  
      [fromNumber]
      ,[Tel]
      ,[msg]
      ,[PriceGroup]
      ,[Optional1]
      ,[Optional2]
      ,[CampaignID]
  FROM [myServer].[dbo].[vw_SendMessages]

Open in new window


What I need to do is to run this Stored Procedure for each record in the SQL View above:

ALTER PROCEDURE [dbo].[spSendMessage] 
(
	@FromNumber numeric(18),
	@Tel numeric(18),
	@Msg nvarchar(1000),
	@PriceGroup int,
	@Optional1 Text = NULL,
	@Optional2 numeric(18) = NULL,
	@CampaignId int = NULL
)
AS
BEGIN
	SET NOCOUNT ON;

	Insert into SMS_Out 
	(fromNumber, Tel, Msg, PriceGroup, Operator, Optional1, Optional2, CampaignID) 
	values 
	(@FromNumber, @Tel, @Msg, @PriceGroup, '0', @Optional1, @Optional2, @CampaignId)

END

Open in new window


How can I run the spSendMessage Stored Procedure for each record (result) in the SQL View?
LVL 1
webressursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Why you need to run the SP

Since you can directly insert into SMS_Out from the view
Insert into SMS_Out 
	(fromNumber, Tel, Msg, PriceGroup, Operator, Optional1, Optional2, CampaignID)
Select [fromNumber]  ,[Tel]
      ,[msg]
      ,[PriceGroup]
     ,'0'
      ,[Optional1]
      ,[Optional2]
      ,[CampaignID] from  [myServer].[dbo].[vw_SendMessages]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Well, when you need to insert your data via this procedure, then you need a cursor.  
DECLARE @NO_ERROR INT = 0;

DECLARE @FromNumber NUMERIC(18);
DECLARE @Tel NUMERIC(18);
DECLARE @Msg NVARCHAR(1000);
DECLARE @PriceGroup INT;
DECLARE @Optional1 TEXT;
DECLARE @Optional2 NUMERIC(18);
DECLARE @CampaignId INT;

DECLARE [Messages] CURSOR
FOR
    SELECT  fromNumber ,
            Tel ,
            msg ,
            PriceGroup ,
            Optional1 ,
            Optional2 ,
            CampaignID
    FROM    myServer.dbo.vw_SendMessages;

OPEN [Messages];
FETCH NEXT FROM [Messages] INTO @FromNumber, @Tel, @Msg, @PriceGroup, @Optional1, @Optional2, @CampaignId;
WHILE @@FETCH_STATUS = @NO_ERROR
    BEGIN
        EXECUTE dbo.spSendMessage @FromNumber, @Tel, @Msg, @PriceGroup, @Optional1, @Optional2, @CampaignId;		
        FETCH NEXT FROM [Messages] INTO @FromNumber, @Tel, @Msg, @PriceGroup, @Optional1, @Optional2, @CampaignId;
    END;

CLOSE [Messages];
DEALLOCATE [Messages];

Open in new window


Another approach would be using a SendMessages procedure.
CREATE TYPE MessagesTableType AS TABLE  
	(
		FromNumber NUMERIC(18),
		Tel NUMERIC(18),
		Msg NVARCHAR(1000),
		PriceGroup INT,
		Optional1 TEXT ,
		Optional2 NUMERIC(18) ,
		CampaignId INT 
		);
GO

CREATE PROCEDURE dbo.p_SendMessages
    (
      @Messages MessagesTableType READONLY 
    )
AS
    SET NOCOUNT ON;

    INSERT  INTO SMS_Out
            ( fromNumber ,
              Tel ,
              Msg ,
              PriceGroup ,
              Operator ,
              Optional1 ,
              Optional2 ,
              CampaignID
            )
            SELECT  *
            FROM    @Messages;
GO

DECLARE @Messages AS MessagesTableType;

INSERT  INTO @Messages
        SELECT  fromNumber ,
                Tel ,
                msg ,
                PriceGroup ,
                Optional1 ,
                Optional2 ,
                CampaignID
        FROM    myServer.dbo.vw_SendMessages;

EXECUTE dbo.p_SendMessages @Messages;

Open in new window


and last but not least - but it may bust your architectural design of table access - insert it directly.
INSERT  INTO SMS_Out
        ( fromNumber ,
          Tel ,
          Msg ,
          PriceGroup ,
          Operator ,
          Optional1 ,
          Optional2 ,
          CampaignID
        )
        SELECT  fromNumber ,
                Tel ,
                msg ,
                PriceGroup ,
                Optional1 ,
                Optional2 ,
                CampaignID
        FROM    myServer.dbo.vw_SendMessages;

Open in new window


btw, sp is a "reserved" prefix for system procedures. Also TEXT is deprecated, use NVARCHAR(MAX) instead. Also the mix of @Msg being NVARCHAR() and Optional1 being TEXT is not optimal, cause surprising. Then a phone number does not necessarily consists of digits only.
0
webressursAuthor Commented:
Perfect, thanks alot!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.