Getting ID from reference table before executing Bulk insert

[yangski]

Hi guys, I'm new here. I have a problem though. I can perform a bulk insert from .csv file to MS SQL Server 2005. My problem here is that there are foreign tables in the .csv file. What I want to happen is that these data should be replaced with their IDs.

Here is an example:

-sample.csv-
Id    Name    Type
==  ====    ====
1    Name1   Type1
2    Name2   Type2
3    Name3   Type1


-referencetb-

Id    Type
==  ====
1    Type1
2    Type2
3    Type3

-Maintb (After bulk inserting)-

Id    Name    Type
==  ====    ====
1    Name1   1
2    Name2   2
3    Name3   1

[cgyrob]

You should be able to clean it up during import with a SSIS package if you know how or else you can do a batch load into a temp table and then clean it up when inserting into maintb.

Since you have a reference table you can use s select statement to insert the correct values but this would be a two step process. First load file then run the insert script.

I would invest some time in learning how to create SSIS packages if you are going to be doing alot of data importing or manipulation.

This insert statement should work if you just want to load and then run the script afterwards.

Insert into maintb (id ,name, type)
Select a.id, a.name, b.id 
From temp a
Inner join  referencetb b on a.type = b.type

You can also use a case statement in the insert but you have would to manually enter every type and change the script if the types ever change.

[yangski]

Is the ForEach Loop possible for this task? So that I don't have to create a temp table? :) Thanks for the help... :P

[cgyrob]

There is no For or foreach loop for sqlserver unless you are talking about using the foreach container in SSIS, which I did mention would be the best way to go about bulk inserts.

[yangski]

There is no For or foreach loop for sqlserver unless you are talking about using the foreach container in SSIS, which I did mention would be the best way to go about bulk inserts.

I had another idea. I'd read the text file first then insert it into a dataset, have it referenced there as well before inserting it to the database... I just don't know how to work around it.. any ideas?

[cgyrob]

What are you going to use to read the file. If you are going to program something to read the file you can simply clean the data in your code and insert into the desired table once you have cleansed the data.

[yangski]

What are you going to use to read the file. If you are going to program something to read the file you can simply clean the data in your code and insert into the desired table once you have cleansed the data.

This is the code that I've done so far...

Private Sub btnSClass_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSClass.Click
        ofdItemClass.ShowDialog()
        'Dim fs As System.IO.FileStream
        'Dim FileInUse As Boolean = True
        'Try
        '    fs = System.IO.File.Open(ofdItemClass.FileName, FileMode.Open, FileAccess.Read, FileShare.None)
        '    FileInUse = False
        'Catch ex As Exception
        '    Do While FileInUse = True
        '        MessageBox.Show("The File " & ofdItemClass.FileName & " is currently used by another program.  Please close it to continue.")
        '    Loop
        'End Try

        'Dim fs As Object
        'fs = CreateObject("Scripting.FileSystemObject")
        'Do While fs.Open(ofdItemClass.FileName)
        '    MessageBox.Show("The file is still open." & ofdItemClass.FileName & " Please close it to continue.")

        'Loop

        Using sr As New StreamReader(ofdItemClass.FileName)
            Dim dt As New DataTable
            Dim row() As String
            Dim colPN As New DataColumn("Parent Name")
            Dim colCD As New DataColumn("Class Description")
            Dim colR As New DataColumn("Remarks")
            Dim colIS As New DataColumn("Is Sync")
            Dim colDU As New DataColumn("Date Updated")
            Dim i As Integer
            dt.Columns.Add(colPN)
            dt.Columns.Add(colCD)
            dt.Columns.Add(colR)
            dt.Columns.Add(colIS)
            dt.Columns.Add(colDU)
            i = 0

            Do While Not sr.EndOfStream
                row = sr.ReadLine.Split(Char.Parse(","))
                dt.Rows.InsertAt(dt.NewRow, i)
                dt.Rows(dt.Rows.Count - 1).Item("Parent Name") = row(0).ToString
                dt.Rows(dt.Rows.Count - 1).Item("Class Description") = row(1).ToString
                dt.Rows(dt.Rows.Count - 1).Item("Remarks") = row(2).ToString
                dt.Rows(dt.Rows.Count - 1).Item("Is Sync") = row(3).ToString
                dt.Rows(dt.Rows.Count - 1).Item("Date Updated") = row(4).ToString
                i = dt.Rows.Count + 1
            Loop
            dgvClass.DataSource = dt
        End Using

    End Sub

That's just about getting the file. I haven't tried the code that you gave me... the database server's having some difficulties or something... hehehhe... :P