Help needed to INSERT using a column values variable as a result of a SELECT

Question

Help needed to INSERT using a column values variable as a result of a SELECT

pwallis

Mr or Mrs. 500

Points: 596
More actions
June 18, 2012 at 6:55 am

#257704

I have a large table A (5M records) and I need to insert into table B a series of columns from table A where the values of some of those columns are taken from actual columns of A and others are extracted from a single column on A (a delimited list).
All my solutions end result in 'column name or number of supplied values does not match the table definition' as I can't get the delimited list recognised as a list of values and not a single value. To try and illustrate what I am doing:
Table A is: [COL_1]=A, [COL_2]=B, [COL_3]=C, [OTHER_COLS]=D,E,F
Table B is: [COL_1],[COL_2],[COL_3],[COL_4],[COL_5],[COL_6]
So [COL_1],[COL_2],[COL_3] all map into the equivalent columns but the values of [COL_3],[COL_4],[COL_5] need to be extracted from the delimited list of [OTHER_COLS].
DECLARE @eof as nchar(1)
DECLARE @replace as nvarchar(max)
DECLARE @exec as nvarchar(max)
SELECT @eof = CONVERT(NCHAR(1), 0xFDF8)
SELECT @replace = char(39) + char(39) + char(39) + char(39) + '+ REPLACE(OTHER_COLS, N' + char(39) + @eof + char(39) + ', ' + char(39) + char(39) + char(39) + ',' + char(39) + char(39) + char(39) + ') + ' + char(39) + char(39) + char(39) + char(39)
SELECT @exec = N'SELECT [COL_1],[COL_2],[COL_3],' + @replace + ' FROM
'
EXEC (@exec)
INSERT INTO
exec(@exec)
The result of the @exec is something like:
[COL_1],[COL_2],[COL_3],(No column name)
A B C 'D','E','F','G'
I can run an inline function to extract each column which works but takes a loong time to execute as there are 120 of these delimited columns to be processed.
Thanks for reading this far! 🙂 Hope you can understand the issue I'm trying to overcome.
June 18, 2012 at 7:57 am

This was removed by the editor as SPAM
June 19, 2012 at 2:52 am

This was removed by the editor as SPAM
June 19, 2012 at 9:35 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Wow this is confusing. It would help your case a LOT if you read the first article in my signature. I can't tell if you need the tally table, a string splitter, or both. Your comment about an inline string splitter tells me you should read the article in my signature about splitting strings anyway. In there you will find a great article that explains how to split strings in a set based manner which means it is FAST FAST FAST.

If you want some real help, again read the first article in my signature and post ddl, sample data and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pwallis Mr or Mrs. 500 Points: 596 More actions · Answer 2

Thought it would be confusing to ppl. I am trying to achieve is a piece of code that generates results like

INSERT INTO TableB ([COL_1],[COL_2],[COL_3],[COL_4],[COL_5]) SELECT [COL_1],[COL_2],[COL_3], ufnsplit([OTHER_COLS], 1), ufnsplit([OTHER_COLS], 2) FROM TableA

The values contained in [OTHER_COLS] splits out so position 1 goes into [COL_4], position 2 into [COL_5] etc. except there are 120 of them per record and with 5M records it takes a while to run a inline split function.

So what I'm after is a way to flatten a delimited list of values such that it can be used in an INSERT statement that performs. Something equivalent to:

INSERT INTO TableB ([COL_1],[COL_2],[COL_3],[COL_4],[COL_5]) SELECT [COL_1],[COL_2],[COL_3], REPLACE(OTHER_COLS], @eof, ',') FROM TableA

where the REPLACE(OTHER_COLS], @eof, ',') is evaluated as a list of values and recognised by the INSERT as a continuation of the column list.

( think I just made it more confusing :crazy: )

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

If you want some real help, again read the first article in my signature and post ddl, sample data and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 4

From what I can tell by the referenced string splitter routine presented by Jeff Moden, when fed a delimitted string, it returns a table like:

return_table(

itemnumber int,

item varchar(whatever)

)

so to use this like the poster prefers, we'd have to select individual values by itemnumber, like

(select item from <stringsplitterinvocation> where itemnumber = 1),

(select item from <stringsplitterinvocation> where itemnumber = 2),

(select item from <stringsplitterinvocation> where itemnumber = 3),

etc...

unfortunately, this means that the string splitter is invoked once for each column, and I don't know how optimal that is. Has anybody used it that way?

pwallis Mr or Mrs. 500 Points: 596 More actions · Answer 5

I've plumbed the DelimitedSplitN4Kfunction in and can see how efficient it is but I don't understand how to use the output in an INSERT without calling it multiple times as above. So my sample is like:

SELECT [COL_1],[COL_2],[COL_3], split.ItemNumber, split.Item FROM TableA CROSS APPLY DelimitedSplitN4K(OTHER_COL, ',') split

How do I join the function output such that I can reference each element of the tableset as a columm value in the INSERT statement without multiple calls?

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

If you want some real help, again read the first article in my signature and post ddl, sample data and desired output.

Seriously if you want some help post some details. Post some ddl, sample data and desired output. It sounds like MAYBE you need to look at dynamic cross tabs but without any details it is shooting blind.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pwallis Mr or Mrs. 500 Points: 596 More actions · Answer 7

What I am trying achieve is the same functionality as the following statement without calling the SplitLocalRef() function, split out the [OTHER_COLS] delimited list from TableA into individual columns on TableB.

The statement is being generated as dynamic SQL looping through the 123 subfields listed as being inside the column [OTHER_COLS] and needing to be split out each into a separate column

INSERT INTO [database].dbo.[TableB] ([COL_1],[COL_2],[COL_3],[COL_4],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[117],[118],[119],[120],[653],[352],[380],[381],[100],[1327],[98],[99],[1332],[1337],[1338],[1339],[1340],[1341],[1342],[1343],[1344],[1345],[656],[657],[658],[659],[660],[5000],[5001],[1017],[5200],[5201],[5202],[5203],[5204],[5205],[5206],[5207],[5208],[5209],[5210],[5211],[5212],[5213],[5214],[5215],[5216],[5217],[5218],[5219],[5220],[5221],[5222],[5223],[5224],[5225],[5226],[5227],[716],[COL_5],[COL_6]) SELECT [COL_1],[COL_2],[COL_3],[COL_4],LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],1),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],2),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],3),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],4),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],5),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],6),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],7),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],8),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],9),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],10),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],11),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],12),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],13),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],14),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],15),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],16),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],17),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],18),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],19),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],20),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],21),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],22),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],23),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],24),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],25),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],26),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],27),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],28),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],29),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],30),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],31),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],32),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],33),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],34),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],35),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],36),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],37),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],38),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],39),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],40),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],41),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],42),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],43),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],44),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],45),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],46),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],47),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],48),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],49),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],50),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],51),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],52),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],53),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],54),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],55),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],56),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],57),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],58),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],59),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],60),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],61),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],62),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],63),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],64),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],65),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],66),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],67),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],68),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],69),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],70),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],71),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],72),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],73),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],74),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],75),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],76),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],77),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],78),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],79),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],80),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],81),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],82),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],83),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],84),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],85),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],86),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],87),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],88),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],89),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],90),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],91),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],92),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],93),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],94),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],95),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],96),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],97),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],98),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],99),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],100),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],101),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],102),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],103),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],104),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],105),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],106),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],107),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],108),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],109),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],110),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],111),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],112),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],113),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],114),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],115),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],116),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],117),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],118),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],119),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],120),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],121),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],122),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],123),300),LEFT([database].dbo.ufn_SplitLocalRef([OTHER_COLS],124),300),[COL_5],[COL_6] FROM [database].dbo.[TableA] WHERE [COL_6]=30 AND [OTHER_COLS] IS NOT NULL

TableB has columns:

[COL_1],[COL_2],[COL_3],[COL_4],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[117],[118],[119],[120],[653],[352],[380],[381],[100],[1327],[98],[99],[1332],[1337],[1338],[1339],[1340],[1341],[1342],[1343],[1344],[1345],[656],[657],[658],[659],[660],[5000],[5001],[1017],[5200],[5201],[5202],[5203],[5204],[5205],[5206],[5207],[5208],[5209],[5210],[5211],[5212],[5213],[5214],[5215],[5216],[5217],[5218],[5219],[5220],[5221],[5222],[5223],[5224],[5225],[5226],[5227],[716],[COL_5],[COL_6]

TableA has columns:

[COL_1],[COL_2],[COL_3],[COL_4],[OTHER_COLS],[COL_5],[COL_6]

The columns on TableB:

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[117],[118],[119],[120],[653],[352],[380],[381],[100],[1327],[98],[99],[1332],[1337],[1338],[1339],[1340],[1341],[1342],[1343],[1344],[1345],[656],[657],[658],[659],[660],[5000],[5001],[1017],[5200],[5201],[5202],[5203],[5204],[5205],[5206],[5207],[5208],[5209],[5210],[5211],[5212],[5213],[5214],[5215],[5216],[5217],[5218],[5219],[5220],[5221],[5222],[5223],[5224],[5225],[5226],[5227],[716]

are all the contained inside the single column [OTHER_COLS] exposed as separate columns.

So the issue is to derive the most efficient technique of decomposing the delimited list content of the column [OTHER_COLS] from TableA and insert all those as separate columns on TableB for a table of 5M source records.

I don't understand how to replace the use of the 123 x ufn_SplitLocalRef reach returning an individual element of the [OTHER_COLS] with a single call to DelimitedSplitN4K and achieve the same functionality.

The split function to extract is slow, converting to XML to extract is slow, using the DelimitedSplitN4K function to extract is fast but it will generate (123 x 5M) records so it may cause out of memory.

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

I will try this one last time. If you want some help read the article I suggested. You need to post ddl (create table statements), sample data (insert statements) and desired output.

We are all volunteers around here and I really don't want to spend my time building your tables and data so I can then spend more time to work on your solution.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

pwallis Mr or Mrs. 500 Points: 596 More actions · Answer 9

Don't worry if you don't understand Sean,patrickmcginnis59 highlighted the issue yesterday and

Stewart "Arturius" Campbell (6/19/2012) hinted that it may be posssible to get the information using a join...hopefully someone who does understand can assist me to get the code reworked.

Stewart "Arturius" Campbell (6/19/2012)
patrickmcginnis59 (6/18/2012)
From what I can tell by the referenced string splitter routine presented by Jeff Moden, when fed a delimitted string, it returns a table like:
return_table(
itemnumber int,
item varchar(whatever)
)
so to use this like the poster prefers, we'd have to select individual values by itemnumber, like
(select item from <stringsplitterinvocation> where itemnumber = 1),
(select item from <stringsplitterinvocation> where itemnumber = 2),
(select item from <stringsplitterinvocation> where itemnumber = 3),
etc...
unfortunately, this means that the string splitter is invoked once for each column, and I don't know how optimal that is. Has anybody used it that way?
The string splitter returns a table valued result set, therefore, one would join the table to the function.
This is actually very efficient, as it is not scalar...

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

pwallis (6/19/2012)
Don't worry if you don't understand Sean,patrickmcginnis59 highlighted the issue yesterday and
Stewart "Arturius" Campbell (6/19/2012) hinted that it may be posssible to get the information using a join...hopefully someone who does understand can assist me to get the code reworked.
Stewart "Arturius" Campbell (6/19/2012)
patrickmcginnis59 (6/18/2012)
From what I can tell by the referenced string splitter routine presented by Jeff Moden, when fed a delimitted string, it returns a table like:
return_table(
itemnumber int,
item varchar(whatever)
)
so to use this like the poster prefers, we'd have to select individual values by itemnumber, like
(select item from <stringsplitterinvocation> where itemnumber = 1),
(select item from <stringsplitterinvocation> where itemnumber = 2),
(select item from <stringsplitterinvocation> where itemnumber = 3),
etc...
unfortunately, this means that the string splitter is invoked once for each column, and I don't know how optimal that is. Has anybody used it that way?
The string splitter returns a table valued result set, therefore, one would join the table to the function.
This is actually very efficient, as it is not scalar...

Sorry if I don't measure up to your expectations. There is certainly no need to be condescending. They are both suggesting the same type of thing. The problem is you want us to help you code but we have nothing to code against. Best of luck, I hope you solve your problem.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Your first post has a code sample like so:

DECLARE @eof as nchar(1)

DECLARE @replace as nvarchar(max)

DECLARE @exec as nvarchar(max)

SELECT @eof = CONVERT(NCHAR(1), 0xFDF8)

SELECT @replace = char(39) + char(39) + char(39) + char(39) + '+ REPLACE(OTHER_COLS, N' + char(39) + @eof + char(39) + ', ' + char(39) + char(39) + char(39) + ',' + char(39) + char(39) + char(39) + ') + ' + char(39) + char(39) + char(39) + char(39)

SELECT @exec = N'SELECT [COL_1],[COL_2],[COL_3],' + @replace + ' FROM

'

EXEC (@exec)

INSERT INTO

exec(@exec)

If you put a PRINT statement before (or instead of) the EXEC

SELECT @exec = N'SELECT [COL_1],[COL_2],[COL_3],' + @replace + ' FROM

'

print (@exec)

- what does it generate? Can you post it here please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden