Hello and thanks for your fast Answers and that you try to Help me!!!
The thing is that I really dont have a primary key or refernece Number and that way I cant normalize my Data.
I give you a better example:
So my raw data comes in that way -->
I want to split my Data and \insert them in two tables. A header table and an item table. The Problem is that I dont have a reference key (not primary.. Sorry!!) and I must first generate one. The only thing that I have is,that the H letter in the First column shows that this raw is the header and the L means thats the Line. The Data comes subquential. That means that the inv_number 123442 has the two items that follow (socks) and the other one has the skirt and pants.
That means I must somehow generate the reference key that shows the relation between the invoice and the items. Its then the primary key for the Header Table and the Foreign Key for the item table.
I already have write a simple code in VB. It reads the file in a array string and I generate the ref number in that way -But I am sure there is a better way to do that only with sql. Please help!!! I hope my code here helps to explain the logik.->
Dim strFileName() As String
strFileName = IO.File.ReadAllLines(filename, Encoding.GetEncoding(1253))
Dim headerIdent as string = "H"
Dim Num as Integer = 222
For Each myLine In strFileName
If Not myLine = Chr(endCharacter) Then
If myLine.Substring(0, 2) = headerIdent Then
num = num + 1
tempStr &= num.ToString.PadLeft((idLen), "0"c) & myLine.ToString & Environment.NewLine