How to control row insertion 1

[HoustonGuy]

I'm inserting rows into a table at specific spots. (A row of Data that is missing.)
The example below is silly, but this is what I need to do.

Example
-------
Row_id Column1

1 John Smith
2 Chicago
3 Illinois

I insert the address data. Now my table looks like this:

1 John Smith
1 123 Main St.
2 Chicago
3 Illinois

I need to be able to maintain the order correctly and reload into a new table. The issue is that sometimes SQL will insert the new address row above the name, (which technically came first.) I need the new table to populated correctly, like this:

1 John Smith
2 123 Main St.
3 Chicago
4 Illinois

I've tried adding a column with a counter and then repopulating the new table while sorting by the counter column, but it is still erratic. It boils down to the way SQL treats the simlar row_id's when processing. I also know that SQL inserts wherever it pleases, and we all must order by the row_id to maintain any type of structure - but I haven't found a fix for this yet.

Any insight is very much appreciated. Working with SQL 2008.
Thanks!

 

[genomon]

Your schema cries out for normalization. How about columns for last_name, first_name, address, city, state, zip_5 etc?

Problem solved?

Les temps sont durs pour les reveurs.

 

[gmmastros]

I agree with genomon. Normalization would be a good thing.

From the looks of it, your table is "almost" set up as an EAV table. EAV is [google]Entity Attribute Value[/google]

If you had another column in your table that represents the type of data you are storing, then you wouldn't need to rely on row ordering (which is always a horrible thing to do).

For example, your data could be:

[tt]
IdNumber Type Value
-------- -------- ------------
121 Name John Smith
121 Address 123 Main St.
121 City Chicago
121 State Illinois
[/tt]

By the way, I showed the data this way to illustrate the point. If you decide to change your table to include a "type" column, you should not use strings to store the type. Instead, you should use an integer column where 1=Name,2=Address,3=City,4-State, etc... This will decrease storage space and increase performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

I understand the point, but this isn't transactional data, or about normalization.

I'm loading a strictly structured text file into a table and searching for anomalies, (Missing data) and then exporting back out to text again - only corrected with the additional rows.

However many other options there are, this is the technique I've inherited and am stuck with.

 

[genomon]

a strictly structured text file

How is this file delimited? There are some great VBA ways to parse out text files.

Les temps sont durs pour les reveurs.

 

[HoustonGuy]

I agree genomon, but I've got to use SQL in an existant DTS package.

 

[genomon]

Oops - forgot what forum I was in

Les temps sont durs pour les reveurs.

 

[fredericofonseca]

how do you know which row is missing? and how do you know what order they should be output?

Once the full criteria for the above is made clear, there will be for sure a way putting it out on the order you desire.

Once the order is as you wish getting a sequential row id is easy using analytical functions

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[HoustonGuy]

Thanks fredericofonseca, but you've overlooked the question I asked.

I have other scripts that tell me which is missing, but that's not the issue I originally stated - nor is it part of the solution, and I know how to select the data in the order I need AFTER the insertion issue is corrected.

You stated that, "there will be for sure a way putting it out on the order you desire." touches closer to the issue. Based on the example I've given in my original post, what is the way of putting it out on the order I desire? (Again, how I found the rows needed to be inserted is not relevant to the answer.)

I DO appreciate the responses, but so far the question I've asked hasn't been the focus of any response, so far.

 

[fredericofonseca]

you are mistaken on thinking that what I asked is of no importance.

In any case.. when you add the new data you need to use a unique row id per group so that the id does not match any of the id's you already had on the source table, but that still has a value that when used on a order by gives you the desired output.
This could be your counter column, but as you have not explained how that counter was decided it is hard to say why you still got wrong values.

so your temporary table
Row_id      Column1      
1           John Smith
1           123 Main St.
2           Chicago
3           Illinois

could be instead

Row_id  sub_row_id    Column1      
1                     John Smith
1       1             123 Main St.
2                     Chicago
3                     Illinois

so your query when inserting into the final table would do a order by row_id, sub_row_id.

As long as you get the new row values correct for column row_id and sub_row_id you are sure to get the desired output.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[HoustonGuy]

Thanks fredericofonseca,

The Counter field I implement renumbers the table from top to bottom, and looks like this:

Row_id      Column1       Counter
1           John Smith       1
1           123 Main St.     2
2           Chicago          3
3           Illinois         4

I then reload to another table sorting by the counter field.
I would say 95% of the time the order is correct, but there are some incorrect insertions that don't keep the order true.

Your suggestion of ordering by row_id, sub_row_id isn't the same logic as mine, and may be the key to getting the correct order every time.

I will certainly try this, and I thank you very much for your help!