|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
For/Next loop to process INSERT INTO statementprevious recommendations. Previous Post: =================== I have the following code which does the following: 1. Deletes all rows having a value of "0" in column C 2. Uploads the data in Row 2 to my SQL Server What I need for the code to do is to upload all rows on the worksheet. My thought is that I might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on each row having data (those not deleted by the DeleteBlankRows procedure)? Here's my code: ======================== Private Sub DeleteBlankRows() Dim lastrow As Long Dim r As Long lastrow = Range("C" & Rows.Count).End(xlUp).Row For r = lastrow To 2 Step -1 If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then ActiveSheet.Rows(r).Delete End If Next End Sub Sub InsertData() Dim oConn As Object Dim sSQL As String Application.ScreenUpdating = False Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") Set oConn = CreateObject("ADODB.Connection") oConn.Open = "Provider=sqloledb;" & _ "Data Source=xx.x.xx.xx;" & _ "Initial Catalog=xxx_xxx;" & _ "User Id=xxxx;" & _ "Password=xxxx" sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL oConn.Close Set oConn = Nothing End Sub Thanks in advance. Try changing
sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value & "', '" & _ Range("F2").Value & "')" oConn.Execute sSQL to For i = 2 To Range("A65536").End(xlUp).Row sSQL = "INSERT INTO Upload_Specific " & _ "([Location], [Product Type], [Quantity], [Product Name], [Style], [Features]) " & _ " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _ Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _ Range("E"&i).Value & "', '" & _ Range("F"&i).Value & "')" oConn.Execute sSQL Next i HTH, Bernie MS Excel MVP Show quoteHide quote "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message news:uQqUzX5IHHA.1816@TK2MSFTNGP06.phx.gbl... >I reposted this because I was unable to achieve desired results from previous recommendations. > > Previous Post: > =================== > I have the following code which does the following: > > 1. Deletes all rows having a value of "0" in column C > 2. Uploads the data in Row 2 to my SQL Server > > What I need for the code to do is to upload all rows on the worksheet. My thought is that I might > need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on how/where > would I modify the code to enable it to (loop through) upload all rows, or iterate on each row > having > data (those not deleted by the DeleteBlankRows procedure)? > > > > Here's my code: > ======================== > Private Sub DeleteBlankRows() > > Dim lastrow As Long > Dim r As Long > lastrow = Range("C" & Rows.Count).End(xlUp).Row > For r = lastrow To 2 Step -1 > If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then > ActiveSheet.Rows(r).Delete > End If > Next > > End Sub > > Sub InsertData() > Dim oConn As Object > Dim sSQL As String > Application.ScreenUpdating = False > Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") > Set oConn = CreateObject("ADODB.Connection") > oConn.Open = "Provider=sqloledb;" & _ > "Data Source=xx.x.xx.xx;" & _ > "Initial Catalog=xxx_xxx;" & _ > "User Id=xxxx;" & _ > "Password=xxxx" > sSQL = "INSERT INTO Upload_Specific " & _ > "([Location], [Product Type], [Quantity], [Product Name], [Style], > [Features]) " & _ > " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & > Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value > & "', '" & _ > Range("F2").Value & "')" > oConn.Execute sSQL > oConn.Close > Set oConn = Nothing > End Sub > > Thanks in advance. > > Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts (and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks again for you help and quick response. :) Show quoteHide quote "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message news:OJ9VN15IHHA.4848@TK2MSFTNGP04.phx.gbl... > Try changing > > sSQL = "INSERT INTO Upload_Specific " & _ > "([Location], [Product Type], [Quantity], [Product Name], [Style], > [Features]) " & _ > " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" > & > Range("C2").Value & "', '" & Range("D2").Value & "', '" & > Range("E2").Value > & "', '" & _ > Range("F2").Value & "')" > oConn.Execute sSQL > > to > > For i = 2 To Range("A65536").End(xlUp).Row > sSQL = "INSERT INTO Upload_Specific " & _ > "([Location], [Product Type], [Quantity], [Product Name], [Style], > [Features]) " & _ > " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', > '" & _ > Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _ > Range("E"&i).Value & "', '" & _ > Range("F"&i).Value & "')" > oConn.Execute sSQL > Next i > > HTH, > Bernie > MS Excel MVP > > > "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message > news:uQqUzX5IHHA.1816@TK2MSFTNGP06.phx.gbl... >>I reposted this because I was unable to achieve desired results from >>previous recommendations. >> >> Previous Post: >> =================== >> I have the following code which does the following: >> >> 1. Deletes all rows having a value of "0" in column C >> 2. Uploads the data in Row 2 to my SQL Server >> >> What I need for the code to do is to upload all rows on the worksheet. >> My thought is that I might need a For/Next Loop, but I'm not sure where >> in the code to place it. Any ideas on how/where would I modify the code >> to enable it to (loop through) upload all rows, or iterate on each row >> having >> data (those not deleted by the DeleteBlankRows procedure)? >> >> >> >> Here's my code: >> ======================== >> Private Sub DeleteBlankRows() >> >> Dim lastrow As Long >> Dim r As Long >> lastrow = Range("C" & Rows.Count).End(xlUp).Row >> For r = lastrow To 2 Step -1 >> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then >> ActiveSheet.Rows(r).Delete >> End If >> Next >> >> End Sub >> >> Sub InsertData() >> Dim oConn As Object >> Dim sSQL As String >> Application.ScreenUpdating = False >> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") >> Set oConn = CreateObject("ADODB.Connection") >> oConn.Open = "Provider=sqloledb;" & _ >> "Data Source=xx.x.xx.xx;" & _ >> "Initial Catalog=xxx_xxx;" & _ >> "User Id=xxxx;" & _ >> "Password=xxxx" >> sSQL = "INSERT INTO Upload_Specific " & _ >> "([Location], [Product Type], [Quantity], [Product Name], [Style], >> [Features]) " & _ >> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" >> & >> Range("C2").Value & "', '" & Range("D2").Value & "', '" & >> Range("E2").Value >> & "', '" & _ >> Range("F2").Value & "')" >> oConn.Execute sSQL >> oConn.Close >> Set oConn = Nothing >> End Sub >> >> Thanks in advance. >> >> > > Doctor Jones,
I have to believe that there is a way to move an entire table into a database without looping. But I have no experience with SQL, and have never used code like yours - still, I'm glad to hear that my (probably sub-optimal) code worked out for you. Bernie MS Excel MVP Show quoteHide quote "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message news:uhXwiI6IHHA.816@TK2MSFTNGP06.phx.gbl... > Bernie -- Thanks a Bunch -- that did the trick Brillantly!! > > I was getting "Wrapped Around the Axle" trying to deal with Stored Procedures and Bulk Inserts > (and whatnot) -- what I originally had worked (somewhat), but just needed some tweaking -- thanks > again for you help and quick response. :) > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message > news:OJ9VN15IHHA.4848@TK2MSFTNGP04.phx.gbl... >> Try changing >> >> sSQL = "INSERT INTO Upload_Specific " & _ >> "([Location], [Product Type], [Quantity], [Product Name], [Style], >> [Features]) " & _ >> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & >> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value >> & "', '" & _ >> Range("F2").Value & "')" >> oConn.Execute sSQL >> >> to >> >> For i = 2 To Range("A65536").End(xlUp).Row >> sSQL = "INSERT INTO Upload_Specific " & _ >> "([Location], [Product Type], [Quantity], [Product Name], [Style], >> [Features]) " & _ >> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', '" & _ >> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _ >> Range("E"&i).Value & "', '" & _ >> Range("F"&i).Value & "')" >> oConn.Execute sSQL >> Next i >> >> HTH, >> Bernie >> MS Excel MVP >> >> >> "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message >> news:uQqUzX5IHHA.1816@TK2MSFTNGP06.phx.gbl... >>>I reposted this because I was unable to achieve desired results from previous recommendations. >>> >>> Previous Post: >>> =================== >>> I have the following code which does the following: >>> >>> 1. Deletes all rows having a value of "0" in column C >>> 2. Uploads the data in Row 2 to my SQL Server >>> >>> What I need for the code to do is to upload all rows on the worksheet. My thought is that I >>> might need a For/Next Loop, but I'm not sure where in the code to place it. Any ideas on >>> how/where would I modify the code to enable it to (loop through) upload all rows, or iterate on >>> each row having >>> data (those not deleted by the DeleteBlankRows procedure)? >>> >>> >>> >>> Here's my code: >>> ======================== >>> Private Sub DeleteBlankRows() >>> >>> Dim lastrow As Long >>> Dim r As Long >>> lastrow = Range("C" & Rows.Count).End(xlUp).Row >>> For r = lastrow To 2 Step -1 >>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then >>> ActiveSheet.Rows(r).Delete >>> End If >>> Next >>> >>> End Sub >>> >>> Sub InsertData() >>> Dim oConn As Object >>> Dim sSQL As String >>> Application.ScreenUpdating = False >>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") >>> Set oConn = CreateObject("ADODB.Connection") >>> oConn.Open = "Provider=sqloledb;" & _ >>> "Data Source=xx.x.xx.xx;" & _ >>> "Initial Catalog=xxx_xxx;" & _ >>> "User Id=xxxx;" & _ >>> "Password=xxxx" >>> sSQL = "INSERT INTO Upload_Specific " & _ >>> "([Location], [Product Type], [Quantity], [Product Name], [Style], >>> [Features]) " & _ >>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', '" & >>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & Range("E2").Value >>> & "', '" & _ >>> Range("F2").Value & "')" >>> oConn.Execute sSQL >>> oConn.Close >>> Set oConn = Nothing >>> End Sub >>> >>> Thanks in advance. >>> >>> >> >> > > Bernie,
There are a plethora of methods to Insert an entire Table, but your For/Next code does exactly what I needed it to do. Several other methods (OPENROWSET -- BULK INSERT) weren't working for me -- based on SQL Server Security Settings and/or other issues. Thanks again for your help! :) Show quoteHide quote "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message news:eHaaO0DJHHA.4848@TK2MSFTNGP04.phx.gbl... > Doctor Jones, > > I have to believe that there is a way to move an entire table into a > database without looping. But I have no experience with SQL, and have > never used code like yours - still, I'm glad to hear that my (probably > sub-optimal) code worked out for you. > > Bernie > MS Excel MVP > > > "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message > news:uhXwiI6IHHA.816@TK2MSFTNGP06.phx.gbl... >> Bernie -- Thanks a Bunch -- that did the trick Brillantly!! >> >> I was getting "Wrapped Around the Axle" trying to deal with Stored >> Procedures and Bulk Inserts (and whatnot) -- what I originally had worked >> (somewhat), but just needed some tweaking -- thanks again for you help >> and quick response. :) >> >> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message >> news:OJ9VN15IHHA.4848@TK2MSFTNGP04.phx.gbl... >>> Try changing >>> >>> sSQL = "INSERT INTO Upload_Specific " & _ >>> "([Location], [Product Type], [Quantity], [Product Name], [Style], >>> [Features]) " & _ >>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', >>> '" & >>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & >>> Range("E2").Value >>> & "', '" & _ >>> Range("F2").Value & "')" >>> oConn.Execute sSQL >>> >>> to >>> >>> For i = 2 To Range("A65536").End(xlUp).Row >>> sSQL = "INSERT INTO Upload_Specific " & _ >>> "([Location], [Product Type], [Quantity], [Product Name], [Style], >>> [Features]) " & _ >>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "', >>> '" & _ >>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _ >>> Range("E"&i).Value & "', '" & _ >>> Range("F"&i).Value & "')" >>> oConn.Execute sSQL >>> Next i >>> >>> HTH, >>> Bernie >>> MS Excel MVP >>> >>> >>> "Doctorjones_md" <xxxDoctorjones_md***@xxxyahoo.com> wrote in message >>> news:uQqUzX5IHHA.1816@TK2MSFTNGP06.phx.gbl... >>>>I reposted this because I was unable to achieve desired results from >>>>previous recommendations. >>>> >>>> Previous Post: >>>> =================== >>>> I have the following code which does the following: >>>> >>>> 1. Deletes all rows having a value of "0" in column C >>>> 2. Uploads the data in Row 2 to my SQL Server >>>> >>>> What I need for the code to do is to upload all rows on the worksheet. >>>> My thought is that I might need a For/Next Loop, but I'm not sure where >>>> in the code to place it. Any ideas on how/where would I modify the >>>> code to enable it to (loop through) upload all rows, or iterate on each >>>> row having >>>> data (those not deleted by the DeleteBlankRows procedure)? >>>> >>>> >>>> >>>> Here's my code: >>>> ======================== >>>> Private Sub DeleteBlankRows() >>>> >>>> Dim lastrow As Long >>>> Dim r As Long >>>> lastrow = Range("C" & Rows.Count).End(xlUp).Row >>>> For r = lastrow To 2 Step -1 >>>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then >>>> ActiveSheet.Rows(r).Delete >>>> End If >>>> Next >>>> >>>> End Sub >>>> >>>> Sub InsertData() >>>> Dim oConn As Object >>>> Dim sSQL As String >>>> Application.ScreenUpdating = False >>>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking") >>>> Set oConn = CreateObject("ADODB.Connection") >>>> oConn.Open = "Provider=sqloledb;" & _ >>>> "Data Source=xx.x.xx.xx;" & _ >>>> "Initial Catalog=xxx_xxx;" & _ >>>> "User Id=xxxx;" & _ >>>> "Password=xxxx" >>>> sSQL = "INSERT INTO Upload_Specific " & _ >>>> "([Location], [Product Type], [Quantity], [Product Name], [Style], >>>> [Features]) " & _ >>>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "', >>>> '" & >>>> Range("C2").Value & "', '" & Range("D2").Value & "', '" & >>>> Range("E2").Value >>>> & "', '" & _ >>>> Range("F2").Value & "')" >>>> oConn.Execute sSQL >>>> oConn.Close >>>> Set oConn = Nothing >>>> End Sub >>>> >>>> Thanks in advance. >>>> >>>> >>> >>> >> >> > >  
Thread options
Other interesting topics
Setting permissions for a Asp.Net website using a Sql2005 database
Error Message 8152 SQL Server performance over internet connection SQL2K Restore 133GB database question Does Tlog actually contain data ? Cannot Defrag Index How to include more databases in DB Mainteance Plan for SQL Server 2005 BULK INSERT - error column too long Getting rid of unused indexes - sql server 2000 Error in Backup |
|||||||||||||||||||||||
