sql server - Fixed width import with field-widths depending on first char of line -

I need to import a file of this type of format:

  PPT line In some there is a fixed width of the parents with the CCHID line, the field is different width compared to the parent width, the static width consistent with the hair earrings, but parents do not have the parent of PACCend There are more fields nearby, like other parents like CThird Child Again the fixed width is related to this time 2 parent. The preceding p-line  

the width with which the columns are fixed depends on the letter P vs C. C. I did not come through this file format, but I need to deal with that sucker ... I am currently reading it as (simplified):

  create table #fixed ( Line varchar (max)) table #link (id int identity, parent int, lintype character, line correspondence (maximum)) with bulk author #fixed '\\ unc \ path \ to \ file.txt' (fieldfremater = '' ) Insert the #link (linetype, line) into the substring (line, 1, 1), set #fixed update c from c.parent = p.id #link c cross (select the top 1 id from # 1) link Linetype = 'P' and id & lt; C.id by order ID) P where c.linetype = 'C'  

this works, but I do not like it normally and I especially want to put SQL Server Concerned with the order that arbitrarily with #link , the correct parent-child relationship in this way updates , especially losing these big files in comparison to these 5 lines for.

But I do not see a way to apply a order here, or with a bulk insert with fixed-width-different- Imports a different-width format which is a format-file

Edit: In a way, I see that the openrowset (bulk '\\ unc \ file.txt', single_clob) and manually remove lines, my question is now mainly, should I be quite concerned about this order of insert in #link that warrants a single_clob < Switching to reading as / code>?

Your initial approach is likely to affect the problems because type (lintip, line) Select the alterator (line, 1, 1), from the #fixed

line to the ORDER BY section is not; There is no guarantee that the rows will be inserted in #link , their order will be shown in the source file.

(From the ID INT identity, line VARCHAR (MAX))

will add the BULK INSERT rows to the target table in the order that appears in the source table.

This means that you will need to use a format file to enable BULK INSERT to leave the Identity column.

Some such content will be required in the format file:

  9.0 1 1 SQLCHAR 0 99 999 "\ R \ n" 2 line SQL_Latin1_General_CP1_CI_AS  

It can then be used with a command such as

  bulk INSERT #fixed FROM '\\ unc \ path \' file.txt '(FIELDTERMINATOR =' ', FORMATFILE = 'c: \ temp \ test.fmt')  

(assuming you saved the file in C: \ Temporary \ Test.fmt)

You can then use the code already present to use the ID from #fixed :

  Enter the id #link (id int, parent int, linetype char), line varchar (max)) # links (id, lintip, line), line based on object (1, 1), # Fixed order