Update: The comments and suggestions in this Q&A conversation led directly to a blog post about OPENSTRING and CROSS APPLY.
This was originally a "How do I ... ?" question until John Smirnios provided a gentle "You can't" answer, so now it is a "Product Suggestion".
Here is the text of the original question...
Here is a working FOR loop that invokes OPENSTRING to repeatedly analyze a tab-delimited string stored in the column raw_text.line_text, in 188.8.131.5289.
Is there a way to eliminate the FOR loop and use a single set-oriented INSERT statement that invokes OPENSTRING ( VALUE raw_text.line_text ) instead of using a local string variable OPENSTRING ( VALUE @line_text )?
If LATERAL is the answer, the syntax escapes me :)
CREATE TABLE raw_text ( line_number BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY CLUSTERED, line_text LONG VARCHAR NOT NULL DEFAULT '' ); /* sample input... line_number,line_text 128,Play - not available now WAAV Mon-Fri 10am 2 hours Leland NC 980 AM */ CREATE TABLE radio_programs ( line_number BIGINT NOT NULL PRIMARY KEY CLUSTERED, station VARCHAR ( 100 ) NULL, dates VARCHAR ( 100 ) NULL, times VARCHAR ( 100 ) NULL, duration VARCHAR ( 100 ) NULL, city VARCHAR ( 100 ) NULL, state VARCHAR ( 100 ) NULL, frequency VARCHAR ( 100 ) NULL ); FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR SELECT raw_text.line_number AS @line_number, raw_text.line_text AS @line_text FROM raw_text ORDER BY raw_text.line_number FOR READ ONLY DO INSERT radio_programs SELECT @line_number, programs.* FROM OPENSTRING ( VALUE @line_text ) WITH ( TABLE radio_programs ( filler(), filler(), station, dates, times, duration, city, state, frequency, filler() ) ) OPTION ( DELIMITED BY ' ' ) AS programs; END FOR; /* sample output... line_number,station,dates,times,duration,city,state,frequency 128,'WAAV','Mon-Fri','10am','2 hours','Leland','NC','980 AM' */
You should be able to combine the statements using something like the following:
Unfortunately, there is a bug^H^H^H unintended limitation that prevents the above from working correctly: instead, the server returns an error "invalid parameter".
There is a work-around for the limitation, you can use
Look forward to this limitation being corrected in a future version of the server.
answered 17 Nov '10, 16:58
Ivan T. Bowman
I think I can give you an almost but not quite approach that may be of use to you. There is a form of LOAD TABLE that is, strictly speaking, undocumented though you will see it if you translate a log that contains a LOAD TABLE ... WITH CONTENT LOGGING. It looks like the following:
column_list must be a verifiably unique set of values (ie, there is a primary key or unique index on non-nullable columns included within column_list) and string_column must be a string type (char, nchar, or binary).
Effectively, the values of string_column are concatenated to produce the data stream that will be processed by LOAD TABLE. In your case, your input doesn't appear to have row delimiters at the end of raw_text.line_text; however, if it did you could possibly get away with the syntax above. If there is an ENCODING clause and string_column is CHAR or NCHAR, the encoding must match the encoding of string_column. If string_column is BINARY, any encoding is permitted.
As I said, you won't find it documented in the LOAD TABLE docs; however, since our tools generate that syntax it's not exactly a secret. I'll add a standard disclaimer that undocumented behaviour is subject to change without notice though.
answered 17 Nov '10, 04:34