Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Loop to insert multiple records

Posted on 2008-10-06
5
Medium Priority
?
465 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 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