as soon as you put anything outside the Database itself, you loose all referencing capabilities.

SQL Server Agent jobs in my opinion the worst option, specially if they go with 1...

AI and promotion of company - should be removed

never seen it not work - but batch mode NOT always the best option.

Always test before and after with real production data volumes.

no way - while a clustered index on the desired columns will in many cases output the data on that order, there are cases where it won't.

if you have issues...

and working well with what solution?

using a hex editor see what is the true row delimiter.

\r\n is one, but it can also be \r or \n on their own. (you can also try each one...

there is a trick.

create a temp table #dummy - 1 column with the same datatype/size of one of the tables used on your main select.

create a clustered columnstore index on...

create a view over that table without the identity column - then insert into the view instead of the table and see if result is what you wish for.

Ken solution...

what Ant said - while you can go with multiple vm's with small footprint those will also reduce IOPS considerably - so a higher vm (Azure constrained cpu for example...

there is something you can try which makes a HUGE difference on such transfers.

don't transfer directly to the final table - transfer to a staging table (#temp or just normal...

• This reply was modified 4 months, 1 week ago by frederico_fonseca.

well. taking in consideration the OP mentioned that this is a tab delimited file, and is complaining about CR on middle of a text, then what I said applies -...

it is a sign that your process likely should be changed as you said - high volumes like this are in most cases best dealt by pushing the lookup data...

ensure the file is a correctly built CSV file - if it is then you can load it using the format=CSV above - if it not then get whoever generates...

if the file is a valid CSV file, then use the FORMAT=CSV and it will load that record correctly - example you have above is not using the format keyword...

I'm going to repeat what I said above.

using SSIS (or any other tool that deals with correctly built CSV files) having a cr, lf or field delimiter within the contents...