• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

sql insert tables using loops

How do Insert into multiple tables using loop?

Alter PROCEDURE sp_MoveData_StateTransaction
@id INT = NULL OUT
AS
BEGIN
INSERT INTO [dbo].[State_Transactions]
           ([title_app_id]
            ,[operator_id]
            , location_id
            , record_type_code
            , state_trans_type_code
             ,[transaction_datetime]
             ,[effective_date])
     SELECT [title_app_id]
                  ,[dealer_id]
                  ,'TEC'
                  , '10'
                  , 'T3'
                   ,Getdate()
                   ,Getdate()
       FROM [t21_maryland_dev].[dbo].[title_app] ta
  where ta.[deal_status_id] = 3
  and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id])
  SET  @id = @@identity
  INSERT INTO [dbo].[State_Transactions_Liens](trans_id) values (@id)  
END
0
Meinhoonaa
Asked:
Meinhoonaa
  • 3
  • 3
1 Solution
 
drmweaverCommented:
In 2008 the Do While Looping has been updated to use the merge commands. Review this and you should be all set
http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
TempDBACommented:
What do you want here. Can you explain more?
In case you want to insert several times using the same stored procecedure, suppose 10 times, you can run the stored procedure in a loop.

declare @a int = 0;
while (@a < 10)
begin
      exec sp_MoveData_StateTransaction
end

you can modify it as per your requirement. Or you can simply modify the stored procedure if the inserting value is always same and if you are just doing it to get the identity value. In that case, output clause with inserted table comes handy.
0
 
MeinhoonaaAuthor Commented:
I think I dont need a loop if I can overcome the following error.
Msg 3902, Level 16, State 1, Procedure sp_MoveData_StateTransaction, Line 29
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Code below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Sam Fecto>
-- Create date: <01/02/2013,,>
-- Description:      <Transfer data from Title App tables to State Transaction tables>
-- =============================================
--exec sp_MoveData_StateTransaction
Alter PROCEDURE sp_MoveData_StateTransaction
@id INT = NULL OUT
AS
BEGIN
INSERT INTO [dbo].[State_Transactions]
           ([title_app_id]
            ,[operator_id]
            , location_id
            , record_type_code
            , state_trans_type_code
             ,[transaction_datetime]
             ,[effective_date])
     SELECT [title_app_id]
                  ,[dealer_id]
                  ,'TEC'
                  , '10'
                  , 'T3'
                   ,Getdate()
                   ,Getdate()
       FROM [t21_maryland_dev].[dbo].[title_app] ta
  where ta.[deal_status_id] = 3
  and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id])
  commit
  --SET  @id = @@identity
  --INSERT INTO [dbo].[State_Transactions_Liens](trans_id) values (@id)
BEGIN
  INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
  SELECT trans_id
       FROM [dbo].[State_Transactions] ta
  where 1=1
  and not exists (select 1 from [dbo].[State_Transactions_Liens] st where st.trans_id = ta.trans_id)  
END
END
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
TempDBACommented:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Sam Fecto>
-- Create date: <01/02/2013,,>
-- Description:      <Transfer data from Title App tables to State Transaction tables>
-- =============================================
--exec sp_MoveData_StateTransaction
Alter PROCEDURE sp_MoveData_StateTransaction
@id INT = NULL OUT
AS
BEGIN
BEGIN tran
INSERT INTO [dbo].[State_Transactions]
           ([title_app_id]
            ,[operator_id]
            , location_id
            , record_type_code
            , state_trans_type_code
             ,[transaction_datetime]
             ,[effective_date])
     SELECT [title_app_id]
                  ,[dealer_id]
                  ,'TEC'
                  , '10'
                  , 'T3'
                   ,Getdate()
                   ,Getdate()
       FROM [t21_maryland_dev].[dbo].[title_app] ta
  where ta.[deal_status_id] = 3
  and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id])
 commit tran
  --SET  @id = @@identity
  --INSERT INTO [dbo].[State_Transactions_Liens](trans_id) values (@id)
BEGIN
  INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
  SELECT trans_id
       FROM [dbo].[State_Transactions] ta
  where 1=1
  and not exists (select 1 from [dbo].[State_Transactions_Liens] st where st.trans_id = ta.trans_id)  
END
END
0
 
TempDBACommented:
But putting a transaction here doesn't seems logical. You might need to insert at both the tables else not right. So, move the second code  within the transaction block, remove begin end for them.
0
 
MeinhoonaaAuthor Commented:
How do I move the second code  within the transaction block, remove begin end for them?

Can you help?
0
 
MeinhoonaaAuthor Commented:
You mean like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Sam Fecto>
-- Create date: <01/02/2013,,>
-- Description:      <Transfer data from Title App tables to State Transaction tables>
-- =============================================
--exec sp_MoveData_StateTransaction_rev
Alter PROCEDURE sp_MoveData_StateTransaction_rev
@id INT = NULL OUT
AS
BEGIN
 BEGIN tran
INSERT INTO [dbo].[State_Transactions]
           ([title_app_id]
            ,[operator_id]
            , location_id
            , record_type_code
            , state_trans_type_code
             ,[transaction_datetime]
             ,[effective_date])
     SELECT [title_app_id]
                  ,[dealer_id]
                  ,'TEC'
                  , '10'
                  , 'T3'
                   ,Getdate()
                   ,Getdate()
       FROM [t21_maryland_dev].[dbo].[title_app] ta
  where ta.[deal_status_id] = 3
  and not exists (select 1 from [dbo].[State_Transactions] st where st.[title_app_id] = ta.[title_app_id])
  commit tran
BEGIN tran
  INSERT INTO [dbo].[State_Transactions_Liens](trans_id)
  SELECT trans_id
       FROM [dbo].[State_Transactions] ta
  where 1=1
  and not exists (select 1 from [dbo].[State_Transactions_Liens] st where st.trans_id = ta.trans_id)  
END
0
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now