There's no doubt OPENSTRING is really cool... but how do I preserve the input row/line number ordering?

With LOAD TABLE, this technique works:

DECLARE LOCAL TEMPORARY TABLE raw_text (
   line_number          BIGINT NOT NULL 
                           DEFAULT AUTOINCREMENT
                           PRIMARY KEY CLUSTERED,
   line_text            LONG VARCHAR NOT NULL DEFAULT '' )
   NOT TRANSACTIONAL;

LOAD TABLE raw_text ( line_text )
   USING VALUE @whatever
   DEFAULTS ON 
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF 
   STRIP OFF;

asked 14 Aug '10, 18:08

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%


select rowid( tab ), * from openstring( VALUE @whatever ) WITH( ...schema... ) tab

permanent link

answered 15 Aug '10, 13:47

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90

question asked: 14 Aug '10, 18:08

question was seen: 764 times

last updated: 15 Aug '10, 13:47