Copy file to table with ascending order

Last Post 01 Aug 2008 10:59 PM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
vkk18
New Member
New Member

--
01 Aug 2008 08:09 PM
Hi Guys,
I have a file with comma delimitted. I want to insert into table with ascending order. Please see the file below

41;1;M ;;;;400000;1;080707;080825;;;
51;Y;080707;1;2010;30;BEBR00760000;400000;NM;;;;;;;;;;;;;;1;;;;
42;DODGERS, VARIOUS;
41;1; T ;;;;400000;1;080707;080825;;;
51;Y;080722;2;1836;30;BEBR00760000;400000;NM;;;;;;;;;;;;;;2;;;;
42;DODGERS, VARIOUS;
41;2; T ;;;;0;1;080701;080701;;;
51;Y;080701;2;1927;30;BEBR00760000;0;NM;;;;;;;;;;;;;;1;;;;
42;DODGERS, VARIOUS;
41;3;MTWTFSS;;;;0;10;080630;080706;;;
51;Y;080630;1;0102;30;BEBR00760000;0;NM;;;;;;;;;;;;;;9;;;;
51;Y;080630;1;2234;30;BEBR00780000;0;NM;;;;;;;;;;;;;;4;;;;
51;Y;080630;1;0600;30;BEBR00840000;0;NM;;;;;;;;;;;;;;1;;;;
51;Y;080630;1;1658;30;BEBR00760000;0;NM;;;;;;;;;;;;;;10;;;;
51;Y;080630;1;2356;30;BEBR00760000;0;NM;;;;;;;;;;;;;;8;;;;
42;ROS, 6A-6A;

I was able to insert into table as it is.right now it is inserting like rows 41,51,42. But i want to insert like
rows 41,42,51.
I tried ascending order but it is inserting all in the ascending order. there are 3 groups in this file which starts with 41,51,42 and again 41,51,42 and so on. i want in the same order but i want 42nd row to be inserted after 41. Can you guys give me some idea?
Thanks
SQLUSA
New Member
New Member

--
01 Aug 2008 10:59 PM
You upload it into a staging table (SSIS, BULK INSERT, bcp).

You copy (INSERT...SELECT...) it to final destination with appropriate sort.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/highperformance2005/ The Best SQL Server 2005 Training in the World!
vkk18
New Member
New Member

--
02 Aug 2008 10:04 AM
Hi,

I am inserting this file into a staging table called Stage1 and it is in the format. C1,C2 and C3 are column Headers.

C1 C2 C3
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc
41 te cc
51 te cc
42 te cc

it is inserting in the staging table like this. Now from this staging table i want to insert into another temp table in the same order but i want to insert row42 after row41. Ex: 41,42,51 again 41,42,51 and so on. Can you help me with the query?
Thx
vkk18
New Member
New Member

--
02 Aug 2008 10:09 PM
Hi,

It is a parent child relation ship that's why i want the set to be together. some body said i can use OPENROWSET. do you guys have any idea about this?
Thx


Acceptable Use Policy
---