?
Solved

Insert result from a SQL View to another SQL table

Posted on 2014-08-07
3
Medium Priority
?
255 Views
Last Modified: 2014-08-08
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?
0
Comment
Question by:webressurs
3 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 1000 total points
ID: 40245819
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
 
LVL 36

Assisted Solution

by:ste5an
ste5an earned 1000 total points
ID: 40245828
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
 
LVL 1

Author Closing Comment

by:webressurs
ID: 40248197
Perfect, thanks alot!!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question