Setting a Primary key to subsequential Data (Header

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Setting a Primary key to subsequential Data...

20 posts, Page 1 of 2

1 2 »»

Setting a Primary key to subsequential Data (Header - Line)

Rate Topic

Display Mode

Topic Options

Author

Message

rena24

Posted Friday, September 14, 2012 10:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

Hello everyone!!!
I have a file with these Data -->

H;2334;8989;90;000
L;XXX;YYY;90;000
L;UUU;PPP;78;000
H;4445;855989;90;000
L;AAA;BBB;90;000
L;YYY;TTT;78;000

I want to set a primary Key (gid) to each row in that way -->

1;H;2334;8989;90;000
1;L;XXX;YYY;90;000
1;L;UUU;PPP;78;000
2;H;4445;855989;90;000
2;L;AAA;BBB;90;000
2;L;YYY;TTT;78;000

Is there any way to do this with row number or with an other newer sql command? I dont want to use a cursor (I hate it).

Thanks in advance!!!!

Greetings
Rena

Post #1359470

Sean Lange

Posted Friday, September 14, 2012 1:02 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

rena24 (9/14/2012)

Glad to hear you don't want to use a cursor for a simple update. However, what is your primary key? In your example it looks like you added a column to the beginning with a value that repeats itself 3 times. If you are asking if you can have a primary key column with multiple rows containing the same value I suggest you read about what a primary key is...

If you can explain clearly what you are trying to do we can help. It would probably be best if you post ddl(create table scripts).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1359585

Tom Brown

Posted Friday, September 14, 2012 1:11 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:16 AM
Points: 1,196, Visits: 1,319

There is no default sort order, so SELECT * FROM Table is not guaranteed to give the same sequence you put the records into the table.

The column you added cannot be a primary key because it is not unique.

The ROW_NUMBER() function needs an ORDER BY clause - which column is used to order the data? - there does not appear to be one from the data you gave.

From the data given, I don't think it can be done. I would need more information, like the full table definition, and where the data comes from (an Excel file? it appears to be CSV)

Post #1359594

rena24

Posted Friday, September 14, 2012 1:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

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:

inv_number;vat;amout;deliveryPlace
H;123442;23%;78.99;52000990889
itemDescription;color;qty
L;socks;blue;100
L;socks;red;200

So my raw data comes in that way -->
H;123442;23%;78.99;52000990889
L;socks;blue;100
L;socks;L;red;200
H;123443;23%;500.99;52000990111
L;skirt;blue;100
L;pants;red;200

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.
Example:

222;H;123442;23%;78.99;52000990889
222;L;socks;blue;100
222;L;socks;red;200
223;H;123443;23%;500.99;52000990111
223,L;skirt;blue;100
223,L;pants;red;200

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

                    End If
                    tempStr &= num.ToString.PadLeft((idLen), "0"c) & myLine.ToString & Environment.NewLine    

                End If
            Next

---------------------------------------
Thank you!!!! Smile

Post #1359625

Sean Lange

Posted Friday, September 14, 2012 1:59 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

Sure I can help. But first you need to help me. See the first link in my signature about best practices when posting questions. Until you post stuff we can work we are at best shooting in the dark. Post up some ddl and sample according to that article and we can help.

The real challenge is that the way your data is it is going to be very difficult to get this right because you have nothing that ties the details to the header at the moment. Once we have full ddl it will be easier to determine how to proceed.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1359631

rena24

Posted Friday, September 14, 2012 2:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

Ok, and thanks again for the answer. Its my third post and I was really in a hurry.. Sorry!! I will tomorrow rewrite the post in the right format.
I know that the Data is dificcult and the only solution that I had was the vb code above. It works fine but I want and I know that there is a really better way to do that.
Goodnight everyone
and thank you!! Smile

Post #1359640

Sean Lange

Posted Friday, September 14, 2012 2:32 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

Are you reading this data in from a file? If so, then this is not a big deal at all. The biggest challenge is that if this data is already in SQL there is nothing use as an order by. If it is however in a file then the file IS the order. Will be interested to see what you post. I won't be on much, if at all, over the weekend.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1359654

Evil Kraig F

Posted Friday, September 14, 2012 2:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

Ah, the old mainframe data sort issue. I HATE THEM. However, the solution isn't rediculously hard... as long as you're not loaded to SQL yet.

This takes a bit of chicanery mostly because the system styles are so different. As you've already mentioned, you'd like to get this into a table hierarchy. The problem is the pre association.

The solution to this is during the load sequence, as mentioned above. This is a place where SSIS can shine compared to some of the more traditional T-SQL methods. Using a synchronous transformation script component, you can read the records through in the order from the file and assign a new column an identifier (GUIDs in this case are probably easiest for first pass, though an int can be easily used to staging tables as well) . What you'll do is detect each row as it comes through, and when the row type is Header you'll change the identifier and assign it to each row that comes through until you hit the next header. This sounds more complicated than it really is, but you need a little (very little, I can do it) familiarity with VB or C# Coding.

If the data is already table loaded, this gets uglier, mostly because you've probably lost any chance of knowing what data belongs where. Hopefully you still have the original load files. If not, you're in for a headache. Script the data out during table scripting (it's an option in SSMS) and dump that to a new query page. Now, manually (because you have to check the actual data against the original ANYWAY) add in your identifier column and corresponding values to the 'everything in one' table. This will give you a new staging structure that will allow you to feed your two proper tables. Once that's done you should be able to modify your import process to never have to do that again.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1359659

rena24

Posted Friday, September 14, 2012 2:43 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 15, 2013 1:34 PM
Points: 27, Visits: 182

Yes, I read the Data from a file!
So, before I insert my Data in a Table I run the vb.code above and I recreate the file with the generated ref_number.
The raw Data in the file are (semilicon (;) delimited)-->

H;123442;23%;78.99;52000990889
L;socks;blue;100
L;socks;L;red;200
H;123443;23%;500.99;52000990111
L;skirt;blue;100
L;pants;red;200

Thank you!!

Post #1359663

Evil Kraig F

Posted Friday, September 14, 2012 2:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

rena24 (9/14/2012)

With that data, you have another reason you're going to need a transformation script component, you'll need to split the streams and manually break out the data. OW. Diff # of columns really can mess you up.

Alright, here's the short form. Create a flat file source that has *1* column, and ends on CRLF (or whatever ends the tail of each row). This feeds into your transformation script component. That script component will have two outputs, one for headers and one for line items. These you'll configure as expected for the correct columns, data types, etc.

In your code, you'll have to detect which output that row belongs to and 'ship it' to the correct output. This gets uglier and uglier, I'd hoped you'd be able to use generic column builds. The alternative is to use completely generic columns of varchar() with ragged right on the data source and feed them into staging tables, and then deal with all your validation and the like after they're in the SQL staging tables.

Post #1359666

« Prev Topic | Next Topic »

20 posts, Page 1 of 2

1 2 »»

Permissions