July 7, 2005 at 6:54 am
I am using BCP to copy data from a text file to a table. The text file looks like this:
BKNL05-334C-GMRS
00011120582311+47.5674-052.7017 0.0360.0
00011120582615+47.5674-052.7017 0.1360.0
00011120582927+47.5674-052.7017 0.2360.0
00011120583431+47.5674-052.7017 0.1360.0
00011120583903+47.5674-052.7017 0.0360.0ATLANTIC KINGFISHER CFH8948
00011120584108+47.5674-052.7017 0.1360.0ATLANTIC KINGFISHER CFH8948
00041120582343+47.5679-052.7011 0.0319.0
00041120582559+47.5679-052.7011 0.0 18.0MAERSK NASCOPIE VCNB
00041120582643+47.5679-052.7011 0.0 22.0MAERSK NASCOPIE VCNB
00041120583034+47.5679-052.7011 0.0150.0MAERSK NASCOPIE VCNB
00041120583343+47.5679-052.7011 0.0357.0MAERSK NASCOPIE VCNB
00041120584055+47.5679-052.7011 0.0224.0MAERSK NASCOPIE VCNB
00051120582347+47.5555-052.7110 0.1182.9
00051120582670+47.5555-052.7110 0.0183.0
etc....
The order of the records above is important as the first row (bolded above) is used in a subsequent stored procedure to extract the string BKNL05-334. However, when the data is BCP'ed to the table, it does not preserve the order and the subsequent script fails to execute properly. Is there a way to preserve the order of the data between the text file and table when using the BCP? Any help would be greatly appreciated.
Note: This is my BCP Code to move the data from the text file to the table
for /r G:\FTPServer\ADAM6Upload\Realtime\ %%i in (*.ais) do bcp sis2_dfo..rt_aisstaging in %%i -c -t"\|" -r"\n" /U"uname" /P"pword" /S"sname" which is contained within a batch file.
July 7, 2005 at 8:56 am
I am not sure whether it would be possible to retain the order with bcp itself.
The workaround is edit the data file by appending row number with the help of an external program. I use VB 6.0. Then in the staging table add an integer field rowID. bcp in the data and sort by rowID.
Regards,
gova
July 7, 2005 at 10:33 am
Thanks Govinn. I am doing the same thing on two other files and they seem to be retaining their order when BCP'ed to the tables. The 'tables' are staging tables where I store the information to be processed by other stored procedures and are then deleted after the processing has taken place. I find it very strange that it is retaining order in some of these tables but not others. Do you know why this could be the case?
July 7, 2005 at 10:54 am
If the table has a clusterd index the order will change as expected. If there is no index then we can never predict the order of storage.
Regards,
gova
July 7, 2005 at 11:03 am
How would I determine if the table has a clustered index or not? Is there a procedure for determining this?
July 7, 2005 at 11:13 am
sp_help tablename
will list all table properties including index details. Creating an index will not help you in this problem unless you have a data in datafile which can be in indexed column.
If you know such column you can use it to determine first row. bcp in performance will be hit if there are indexes in staging tables.
Regards,
gova
July 7, 2005 at 3:46 pm
Can you alter the table into which you are bcp'ing the file to add an identity column on which a clustered pk index has been established?
CREATE TABLE YerTable (
Field1 char(10),
Field2 char(100),
..
..
RowID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED
)
You would then have to use a bcp format file (search this site for lots of good examples) to copy the data in that ignores the PK in your staging table. The data will be loaded in the same order it is in the file.
HTH, Dave
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply