sequential update of table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

sequential update of table

Author

Topic

pfaisalbe
Starting Member

12 Posts

Posted - 2009-06-10 : 10:42:24

Hi

I am passing text file to sql server 2005 using sql bulk copy class via vb.net. Text file has 1 column and 1 million rows. These are actually samples taken from a device. I store the data in the table successfully.

but the problem is data is stored in random. My requirement is records in table should follow the same order as in text file.

Order by can't work here since the values in the text file are random values taken at a particular time.

Any idea

Cheers

Faisal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 10:59:21

As a basic principle you should never assume that you can get data out of a table in the order it was stored.
Because of this you should arrange a numbering in your textfile for example.

No, you're never too old to Yak'n'Roll if you're too young to die.

pfaisalbe
Starting Member

12 Posts

Posted - 2009-06-10 : 11:11:39

Thanks you are right.

If I add an additional column in the text file , the problem will be sorted out.

It will slow down the system ,Which means additional i million entry is added.

Cheers

Faisal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-10 : 13:50:39

quote:
Originally posted by pfaisalbe

Hi

I am passing text file to sql server 2005 using sql bulk copy class via vb.net. Text file has 1 column and 1 million rows. These are actually samples taken from a device. I store the data in the table successfully.

but the problem is data is stored in random. My requirement is records in table should follow the same order as in text file.

Order by can't work here since the values in the text file are random values taken at a particular time.

Any idea

Cheers

Faisal

you shouldnt worried about order in which data is actually stored in table as long as you have a column by which you can retrive them in order you want in select

pfaisalbe
Starting Member

12 Posts

Posted - 2009-06-11 : 08:17:30

I AM JUST REPHRASING MY PROBLEM . HERE IS MY EXPLANATION. THE DATA FROM THE TEXT FILE FOR EXAMPLE IS IN THE ORDER
-0.589
1.2356
2.586
-0.456
1.23

Once you transfer the these values into database , they won't be in order. If I plot the data , It won't give the exact information. So order in the textfile should be maintained.

Unfortunately I have only one column.
Is there any sql query to retreive the order ?

Faisal

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 08:34:30

You need to add a column tot he original file to maintain the order. (is this graph data? if so you need to store the X coordinate as well as the y coordinate!)

SQL server isn't storing the data in a list. No way to get it in the order you need without that second column.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-11 : 09:42:41

Hm, I don't know if this will actually work because I don't know how BCP actually inserts the data but can you try to add an identity column to the table you're inserting in to?

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:10:17

quote:
Originally posted by pfaisalbe

I AM JUST REPHRASING MY PROBLEM . HERE IS MY EXPLANATION. THE DATA FROM THE TEXT FILE FOR EXAMPLE IS IN THE ORDER
-0.589
1.2356
2.586
-0.456
1.23

Once you transfer the these values into database , they won't be in order. If I plot the data , It won't give the exact information. So order in the textfile should be maintained.

Unfortunately I have only one column.
Is there any sql query to retreive the order ?

Faisal

you could add a audit column to your table like datecreated and default it to GETDATE() so that on insertion of each value to table, this column will automatically assume current datetime as its value. then just use it for ordering records in order of insertion and plot.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 11:34:53

Hi Visakh16,

If he's bulk imported this won't all the dates for those rows be the same?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pfaisalbe
Starting Member

12 Posts

Posted - 2009-06-11 : 13:21:11

Thanks for All

I have inserted an identity column. It works fine.
The drawback is , If I reuse the same table ,I mean clear all the data and reupload the data, Identity column doesnot start with 1 instead it starts with 6574859 , order also collapsed.

Cheers

Faisal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 13:23:40

quote:
Originally posted by pfaisalbe

Thanks for All

I have inserted an identity column. It works fine.
The drawback is , If I reuse the same table ,I mean clear all the data and reupload the data, Identity column doesnot start with 1 insted it starts with 6574859 , order also collapsed.

Cheers

Faisal

for clearing the identity before reuse, just run below

DBCC CHECKIDENT ("yourtable");

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-12 : 11:17:16

You can also TRUNCATE the table. That will reset the IDENTITY.

ankit_ankit
Starting Member

1 Post

Posted - 2009-06-12 : 11:57:03

hi,
i think you can take help time...just retrieve the time in which your data was fetched. next you can use order by time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-14 : 01:35:10

quote:
Originally posted by ankit_ankit

hi,
i think you can take help time...just retrieve the time in which your data was fetched. next you can use order by time

if its bulk import, the time will same for all of them. in that case i think identity column will be better

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-14 : 02:12:38

For BCP and BULK INSERT, you can add the ORDER hint.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources