SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Copy delimitted file to another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kris22
Starting Member

35 Posts

Posted - 08/02/2008 :  00:06:28  Show Profile  Reply with Quote
see below

Edited by - kris22 on 08/02/2008 19:28:29

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 08/02/2008 :  01:13:35  Show Profile  Visit jezemine's Homepage  Reply with Quote
the order of rows in a table is a meaningless concept. the rows of a table are a set, and sets have no order.

only when you select the data and specify an ORDER BY clause does ordering make sense.


elsasoft.org
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/02/2008 :  06:01:05  Show Profile  Reply with Quote
quote:
Originally posted by kris22

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


do you have column headers in file for columns? You could try ordering on column you want using OPENROWSET

http://msdn.microsoft.com/en-us/library/ms190312.aspx
Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/02/2008 :  12:01:54  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by kris22

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


do you have column headers in file for columns? You could try ordering on column you want using OPENROWSET

http://msdn.microsoft.com/en-us/library/ms190312.aspx



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. Can you help me with the query? i never used OpenRowset. I appreciate your help
Thx








Edited by - kris22 on 08/02/2008 12:19:22
Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/02/2008 :  12:32:04  Show Profile  Reply with Quote
Hi Visakha,

Can you help me with the below
Thx

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. Can you help me with the query? i never used OpenRowset. I appreciate your help
Thx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/02/2008 :  12:35:21  Show Profile  Reply with Quote
quote:
Originally posted by kris22

quote:
Originally posted by visakh16

quote:
Originally posted by kris22

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


do you have column headers in file for columns? You could try ordering on column you want using OPENROWSET

http://msdn.microsoft.com/en-us/library/ms190312.aspx



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. Can you help me with the query? i never used OpenRowset. I appreciate your help
Thx











DO you have any unique valued columns in your table?
Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/02/2008 :  19:24:15  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by kris22

quote:
Originally posted by visakh16

quote:
Originally posted by kris22

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


do you have column headers in file for columns? You could try ordering on column you want using OPENROWSET

http://msdn.microsoft.com/en-us/library/ms190312.aspx



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. Can you help me with the query? i never used OpenRowset. I appreciate your help
Thx











DO you have any unique valued columns in your table?



Hi,
I don't have any uniue columns in that table, but i can add one identity column if want. can you give me any idea to figure this out?
Thx
Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/04/2008 :  12:32:58  Show Profile  Reply with Quote
Hi,
Can some body help me with the query? appreciate your help
Thx
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 08/04/2008 :  21:37:45  Show Profile  Reply with Quote
DECLARE @staging TABLE
(
	ID	int identity(1,1),
	C1	int,
	C2	varchar(2),
	C3	varchar(2)
)

-- CREATE sample data IN random order FOR testing
INSERT INTO @staging (C1, C2, C3)
SELECT	C1, C2, C3
FROM
(
	SELECT	C1 = 41, C2 = 'te', C3 = 'cc' UNION ALL
	SELECT	41, 'te', 'cc' UNION ALL
	SELECT	41, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' 
) d
ORDER BY NEWID()


--	Query

SELECT	C1, C2, C3
FROM
(
	SELECT	ID, C1, C2, C3, 
		row_no	= (SELECT COUNT(*) FROM @staging x WHERE x.C1 = s.C1 AND x.ID <= s.ID)
	FROM	@staging s
) s
ORDER BY row_no, C1

/*
C1          C2   C3   
----------- ---- ---- 
         41 te   cc
         42 te   cc
         51 te   cc
         41 te   cc
         42 te   cc
         51 te   cc
         41 te   cc
         42 te   cc
         51 te   cc

(9 row(s) affected)
*/



KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22742 Posts

Posted - 08/05/2008 :  06:05:23  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by kris22

see below


What to see below?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 08/05/2008 :  08:36:20  Show Profile  Reply with Quote
see above


KH
Time is always against us

Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/08/2008 :  18:48:46  Show Profile  Reply with Quote
quote:
Originally posted by khtan

DECLARE @staging TABLE
(
	ID	int identity(1,1),
	C1	int,
	C2	varchar(2),
	C3	varchar(2)
)

-- CREATE sample data IN random order FOR testing
INSERT INTO @staging (C1, C2, C3)
SELECT	C1, C2, C3
FROM
(
	SELECT	C1 = 41, C2 = 'te', C3 = 'cc' UNION ALL
	SELECT	41, 'te', 'cc' UNION ALL
	SELECT	41, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	42, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' UNION ALL
	SELECT	51, 'te', 'cc' 
) d
ORDER BY NEWID()


--	Query

SELECT	C1, C2, C3
FROM
(
	SELECT	ID, C1, C2, C3, 
		row_no	= (SELECT COUNT(*) FROM @staging x WHERE x.C1 = s.C1 AND x.ID <= s.ID)
	FROM	@staging s
) s
ORDER BY row_no, C1

/*
C1          C2   C3   
----------- ---- ---- 
         41 te   cc
         42 te   cc
         51 te   cc
         41 te   cc
         42 te   cc
         51 te   cc
         41 te   cc
         42 te   cc
         51 te   cc

(9 row(s) affected)
*/



KH
Time is always against us





Hi Khtan,

Does row_no work in Sql2000?

Also i do have a table called Staging with ID,C1,C2,C3 and it has data already. can you help me with the query you gave me.


SELECT C1, C2, C3
FROM
(
SELECT ID, C1, C2, C3,
row_no = (SELECT COUNT(*) FROM @staging x WHERE x.C1 = s.C1 AND x.ID <= s.ID)
FROM @staging s
) s
ORDER BY row_no, C1



I don't understand @staging x and FROM @staging s
Instead of the Stored Procedure can you give me direct query?
Appreciate your help
Thanks

Edited by - kris22 on 08/08/2008 19:22:12
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/09/2008 :  02:08:57  Show Profile  Reply with Quote
1.row_no is not stndard row_number function in sql server. its just column alias khtan has created for holding the count value
2.The table staging was only for demonstration purpose. you can use your Staging table in place of that.Just make sure it has an identity column.If not, just use the table provided and dump results from your table to this.
3.x & s are just two aliases created for @Staging table as we are using it twice and want to give it different names to refer to
4.What Khtan has given you is a direct query not a stored procedure
Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/12/2008 :  15:07:02  Show Profile  Reply with Quote
Hi,
I tried this query, it is working if the data is small. but if the table has 2500 rows it is missing the order.is the query right that i am using?

I already have a staging table with the data



SELECT id,Col001, Col002, Col003
FROM
(SELECT id, Col001,Col002,Col003,row_no = (SELECT COUNT(*) FROM tblstaging x WHERE x.Col001 = tblstaging.Col001 AND x.id <= tblstaging.id)
FROM tblstaging) tblstaging
ORDER BY row_no,Col001

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 08/12/2008 :  23:38:11  Show Profile  Reply with Quote
" it is missing the order "
What do you mean by missing the order ?


KH
Time is always against us

Go to Top of Page

kris22
Starting Member

35 Posts

Posted - 08/13/2008 :  16:49:07  Show Profile  Reply with Quote
quote:
Originally posted by khtan

" it is missing the order "
What do you mean by missing the order ?


KH
Time is always against us




hi,
It is missing order because i want to sort by each group.
like

1st group

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

2nd group

41 te cc
51 te cc
51 te cc
42 te cc

3rd group

41 te cc
51 te cc
51 te cc
51 te cc
51 te cc
42 te cc

and so on


so what my assumption is when i insert this data into temp table from staging if i insert group number as another column then i would be able to sort by group and Col001. what do you think?

i think with this i can sort by group and C1.

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


I want to add this Grp column in temp table and want to increment if i get to column '41'.
Final out put will be

C1 C2 C3 Grp
41 te cc 1
51 te cc 1
51 te cc 1
51 te cc 1
42 te cc 1
41 te cc 2
51 te cc 2
42 te cc 2
41 te cc 3
51 te cc 3
51 te cc 3
51 te cc 3
42 te cc 3

can you give me some idea on how to insert above Grp number in temp table if possible query?
Thx


Edited by - kris22 on 08/13/2008 16:56:03
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 08/13/2008 :  23:21:56  Show Profile  Reply with Quote
What is this ? Looks like it's a change of what you required initially !


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000