sql statement

I am checking a csv file that will give me about 300 entries that I need to match up in an sql database.  Is there a way to put this in one statement?  Or better to run 300 statements in a loop?  If one statement works, how do I write that?

I would have 300 serial numbers and need to make sure a column on each one of these entries had a "Y" in it.  Most of these will have a "Y" in it.  If the serial number does not exist I need to create a new row for this.
chadmanvbAsked:
Who is Participating?
 
sybeCommented:
If it is a one time thing, I would not bother very much about writing the best code for this purpose. Just use code that you are best familiar with and get it done.
0
 
nepaluzCommented:
do you need to check the csv file or the database?
If itis the csv file, give canwe have a coupleof lines' sample posted?
0
 
chadmanvbAuthor Commented:
This will run every every hour or as needed to keep the SQL database updated with this csv file.

So far I have just have a loop to read the csv file and get all the lines I need to find, but I'm not sure how to make the update to SQL.  This is what I have so far.

 '*******************READ CSV***********************************
        Dim workStationsRead As New StreamReader(workstationstext)
        Dim workStationsList As String = workStationsRead.ReadToEnd()
        workStationsRead.Close()

        'find available workstations
        Dim workStationListSplit() As String = Regex.Split(workStationsList, "\r\n")

        For Each FileLine As String In workStationListSplit

            If FileLine.StartsWith(";") Then

                'skip all comment lines
            ElseIf FileLine.Trim = "" Then
                'skip blank lines

            Else
                Try
                   
                    Dim cols
                    cols = Split(FileLine, ",")
                    Dim WorkstationExclude As String = (cols(12)) 'this determines if the workstation will be checked.
                    If WorkstationExclude = "Y" Or WorkstationExclude = "y" Then
                        'these should all have remote access enabled.  Check the database to make sure

'***********need help here building the large sql statement****************
'***or I could just use a single sql statement here*******************


                    Else
                        'skip other workstations
                    End If
                Catch ex As Exception
                   
                End Try

            End If

        Next

0
 
CodeCruiserCommented:
I would insert the CSV to a temp table on the server and then do table comparisons in a stored procedure which would be way faster

http://stackoverflow.com/questions/96448/sql-bulk-import-from-csv

0
 
chadmanvbAuthor Commented:
I just ran individual queries for each iteration of the loop.  It took less than 1 second.  Code, I'm sure that would be faster, but this seemed to be faster than I thought
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.

All Courses

From novice to tech pro — start learning today.