Import must be read as-is into a table.

[Guus2005]

I allways thought that when i import a table in SQL Server the file was imported as i was presented.

File:

This
is
a
file
in
which
the
sequence
is
very
important.

But i am having doubts as to the truth of that statement.
And if it is not, how can i make sure that it is?

I am using this command:

BCP SomeDatabase.dbo.tmpAnswerRaw in 'c:\temp\Filename.csv' -T -c

Thanks!

 

[CJ_London]

Think you will find imports are always random (not just BCP) - if they happen to be in order that is a happy coincidence. After all, data in tables is stored randomly, to get an order you need to provide it. Things I am aware of that can affect the order is the size and nature of the file (small files tend to be in order, large files not), and perhaps whatever 'minimize bloat' functionality the importing mechanism has.

I'm not familiar with BCP but a quick google suggests you can import in batches - perhaps smaller batches will retain some semblance of order?

If you require an order, usual way would be to include an 'orderby' column in the file to be imported

see this link

Can you BCP import records in the same order as a flat file?

I am trying to BCP IN a flat file that must maintain the sequence that is within the file. For an example, row 1 in the flat file must be in row 1 of the table. I will then assign identity to t...

stackoverflow.com

 

[GPGeorge]

I allways thought that when i import a table in SQL Server the file was imported as i was presented.

File:

This
is
a
file
in
which
the
sequence
is
very
important.

But i am having doubts as to the truth of that statement.
And if it is not, how can i make sure that it is?

I am using this command:

BCP SomeDatabase.dbo.tmpAnswerRaw in 'c:\temp\Filename.csv' -T -c

Thanks!

How about a real sample of real data. Sometimes, trying to "simplify" by using made up examples actually obscures the problem.

 

[Isaac]

I would use one of the bulk insert features instead, it is a bit easier to write code that specifies the formats.

But you probably have a problem with line breaks

 

[Galaxiom]

Import the whole csv into a table as a single value. Then apply STRING_SPLIT with the ordinal argument set. (You will need SQL Server 2022 or Azure.)

Then split the multiple records on the commas.

 

[Isaac]

And wouldn't you need some follow up querying? To transpose to columns based on the number in the Ordinal 2nd column?

 

[SQL_Hell]

SQL Server data in a table will be stored in the order of the clustered index (usually the primary key), if there is no clustered index defined then the data will be stored in a heap (random order).

 

[Isaac]

When reading a "flat" file into a table and appending it, the flat file will be read in physical record order

If you happen to be using some line-by-line method, yes