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

  • 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.

  • 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/

  • 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: )

  • 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/

  • 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?

  • 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?

  • 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/

  • 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.

  • 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/

  • 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...

  • 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/

  • 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

  • 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.

    I actually have a similar issue on and off, so far I've used a fairly inefficient solution (scalar functions). In the interest of the subject, I'm posting my interpretation of the issue:

    CREATE TABLE TABLE_A (

    [COL_1] VARCHAR(50),

    [COL_2] VARCHAR(50),

    [COL_3] VARCHAR(50),

    [OTHER_COLS] VARCHAR(50)

    )

    CREATE TABLE TABLE_B (

    [COL_1] VARCHAR(50),

    [COL_2] VARCHAR(50),

    [COL_3] VARCHAR(50),

    [COL_4] VARCHAR(50),

    [COL_5] VARCHAR(50),

    [COL_6] VARCHAR(50)

    )

    INSERT INTO TABLE_A VALUES('A','B','C','D,E,F')

    -- WE WANT A STATEMENT THAT INSERTS INTO TABLE_B VALUES FROM FROM TABLE_A THE VALUES IN SUCH A WAY THAT TABLE_B

    -- WILL LIST LIKE THIS, IE., SPLITTING TABLE_A COLUMN 'OTHER_COLS' INTO SEPARATED VALUES:

    --SELECT * FROM TABLE_B

    --COL_1 COL_2 COL_3 COL_4 COL_5 COL_6

    --A B C D E F

    In Sean's defense, making ddl scripts available is the custom here, this way people can jump right into twiddling with queries without having to set up a table and example data. Even a degenerate example of the problem (like I posted) would probably be good enough. If what I posted isn't representative of your issue, its probably even more illustrative of the need to post correct ddl.

  • patrickmcginnis59 (6/19/2012)


    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.

    I actually have a similar issue on and off, so far I've used a fairly inefficient solution (scalar functions). In the interest of the subject, I'm posting my interpretation of the issue:

    CREATE TABLE TABLE_A (

    [COL_1] VARCHAR(50),

    [COL_2] VARCHAR(50),

    [COL_3] VARCHAR(50),

    [OTHER_COLS] VARCHAR(50)

    )

    CREATE TABLE TABLE_B (

    [COL_1] VARCHAR(50),

    [COL_2] VARCHAR(50),

    [COL_3] VARCHAR(50),

    [COL_4] VARCHAR(50),

    [COL_5] VARCHAR(50),

    [COL_6] VARCHAR(50)

    )

    INSERT INTO TABLE_A VALUES('A','B','C','D,E,F')

    -- WE WANT A STATEMENT THAT INSERTS INTO TABLE_B VALUES FROM FROM TABLE_A THE VALUES IN SUCH A WAY THAT TABLE_B

    -- WILL LIST LIKE THIS, IE., SPLITTING TABLE_A COLUMN 'OTHER_COLS' INTO SEPARATED VALUES:

    --SELECT * FROM TABLE_B

    --COL_1 COL_2 COL_3 COL_4 COL_5 COL_6

    --A B C D E F

    In Sean's defense, making ddl scripts available is the custom here, this way people can jump right into twiddling with queries without having to set up a table and example data. Even a degenerate example of the problem (like I posted) would probably be good enough. If what I posted isn't representative of your issue, its probably even more illustrative of the need to post correct ddl.

    Thanks Patrick, this was my interpretation of the issue at hand too. This is actually fairly straight forward combining Jeff Moden's splitter and cross tabs.

    In the interest of at least 2 rows to confirm that this works I first added a second row to table_A. Then an insert.

    INSERT INTO TABLE_A VALUES('g','h','i','x,y,z')

    insert Table_B

    select col_1, col_2, col_3,

    max(Case when d.ItemNumber = 1 then d.Item end) as Col_4,

    max(Case when d.ItemNumber = 2 then d.Item end) as Col_5,

    max(Case when d.ItemNumber = 3 then d.Item end) as Col_6

    from TABLE_A

    cross apply dbo.DelimitedSplit8K(OTHER_COLS, ',') d

    group by col_1, col_2, col_3

    select * from table_b

    For anybody else just joining us, please see the link in my signature about splitting strings. It contains the code to generate this function.

    _______________________________________________________________

    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/

  • Thank you all for your assistance.

    I am going to tryout the method that Sean has highlighted first as the code needs to be called via dynamicSQL (due to determining the actual columns names etc.). Hopefully it will perform a lot better than the slow function method currently in use.

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

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