Solved

Loop to insert multiple records

Posted on 2008-10-06
5
462 Views
Last Modified: 2013-11-27
Hi all, if I have the following, how would I set this up to loop through and insert multiple rows??   Like in the Select statement I could have a JB_ID with several lines to it, so then I would need the insert to insert multiple lines.. in Visual Studio 2005 VB??

Thanks
Dim BP_Req As Integer
        Dim IT_ID As String
        Dim BP_Desc As String
        Dim BP_Print_Desc As String
        Dim BP_DispOrder As Integer
 
        Dim sql6 As String = "Select BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquip where JB_ID = @JB_ID"
        Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataCARsConnectionString").ConnectionString)
            Dim cmd As New SqlCommand(sql5, conn)
            cmd.Parameters.AddWithValue("@JB_ID", JB_ID)
            Dim reader As SqlClient.SqlDataReader
            cmd.Connection.Open()
            reader = cmd.ExecuteReader
            reader.Read()
            BP_Req = reader("BP_Req")
            IT_ID = reader("IT_ID")
            BP_Desc = reader("BP_Desc")
            BP_Print_Desc = reader("BP_Print_Desc")
            BP_DispOrder = reader("BP_DispOrder")
            cmd.Connection.Close()
        End Using
 
        Dim sql2 As String = "INSERT  INTO [bomPriEquip] (BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) VALUES (@BP_Req, @IT_ID, @BP_Desc, @BP_Print_Desc, @BP_DispOrder)"
        Using conn As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("HCISDataCARsConnectionString").ConnectionString)
            Dim cmd As New SqlCommand(sql1, conn)
            cmd.Parameters.Add(New SqlParameter("@BP_Req", BP_Req))
            cmd.Parameters.Add(New SqlParameter("@IT_ID", IT_ID))
            cmd.Parameters.Add(New SqlParameter("@BP_Desc", BP_Desc))
            cmd.Parameters.Add(New SqlParameter("@BP_Print_Desc", BP_Print_Desc))
            cmd.Parameters.Add(New SqlParameter("@BP_DispOrder", BP_DispOrder))
            cmd.Parameters.Add(New SqlParameter("@JB_IDNew", JB_IDNew))
            conn.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()
        End Using

Open in new window

0
Comment
Question by:ratkinso666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 22656013
Do it in one T-SQL command, rather than two:

INSERT  INTO [bomPriEquip] (BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder)
Select BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquip where JB_ID = @JB_ID

This will put the results of the SELECT query straight into the INSERT statement - regardless of how many rows there are.

Hope this helps,

Rob
0
 

Author Comment

by:ratkinso666
ID: 22659392
I am trying this:
INSERT  INTO [bomPriEquipT] (JB_ID, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) values (@JB_IDNew, (Select BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquip where JB_ID = @JB_ID))

but am getting this error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.

I have to replace the JB_ID with the new one...
0
 

Author Comment

by:ratkinso666
ID: 22659467
I got it to work like this,
INSERT  INTO [bomPriEquipT] (JB_ID, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder) Select @JB_IDNew, BP_Req, IT_ID, BP_Desc, BP_Print_Desc, BP_DispOrder from bomPriEquipT where JB_ID = @JB_ID

Thanks,
Randy
0
 

Author Closing Comment

by:ratkinso666
ID: 31503579
Thank you for your EXCELLENT solution!!!
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 22665028
No worries. I'm pleased it's working for you.

And yes, you can't combine the VALUES and SELECT statements. But you can make a SELECT statement which just includes whatever you need to make it essentially the same as using VALUES.

Rob
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 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