Copy delimitted file to another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Copy delimitted file to another table

Author

Topic

kris22
Starting Member

35 Posts

Posted - 2008-08-02 : 00:06:28

see below

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-02 : 01:13:35

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 06:01:05

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

kris22
Starting Member

35 Posts

Posted - 2008-08-02 : 12:01:54

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

kris22
Starting Member

35 Posts

Posted - 2008-08-02 : 12:32:04

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-02 : 12:35:21

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?

kris22
Starting Member

35 Posts

Posted - 2008-08-02 : 19:24:15

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

kris22
Starting Member

35 Posts

Posted - 2008-08-04 : 12:32:58

Hi,
Can some body help me with the query? appreciate your help
Thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 21:37:45

[code]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)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 06:05:23

quote:
Originally posted by kris22

see below

What to see below?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-05 : 08:36:20

see above

KH
[spoiler]Time is always against us[/spoiler]

kris22
Starting Member

35 Posts

Posted - 2008-08-08 : 18:48:46

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
[spoiler]Time is always against us[/spoiler]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-09 : 02:08:57

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

kris22
Starting Member

35 Posts

Posted - 2008-08-12 : 15:07:02

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-12 : 23:38:11

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

KH
[spoiler]Time is always against us[/spoiler]

kris22
Starting Member

35 Posts

Posted - 2008-08-13 : 16:49:07

quote:
Originally posted by khtan

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

KH
[spoiler]Time is always against us[/spoiler]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 23:21:56

What is this ? Looks like it's a change of what you required initially !

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources