Post reply

Insert as multiple rows from column

  • January 6, 2017 at 6:47 am

    #325042

    Hi,

    I need the ChildID column values to be inserted as a separate row .

    CREATE TABLE #MyTestTable

    (

    Title NVARCHAR(200),

    ID NVARCHAR(100),

    ChildID NVARCHAR(650)

    )

    INSERT INTO #MyTestTable ( Title,ID,ChildID)

    SELECT 'Root','IEABEL6II7777777','["IEABEL6II4DVRKIE"]'

    UNION

    SELECT 'Training','IEABEL6II4DVRMDR','["IEABEL6II4DVRNFZ","IEABEL6II4DVRNEJ","IEABEL6II4DVRNEU","IEABEL6II4DVRNFF","IEABEL6II4DVRNEG"]'

    SELECT * FROM #MyTestTable

    --Desired results

    SELECT 'Root' AS Title,'IEABEL6II7777777' AS ID,'IEABEL6II4DVRKIE' AS ChildID

    UNION

    SELECT 'Training' AS Title,'IEABEL6II4DVRMDR' AS ID,'IEABEL6II4DVRMDR' AS ChildID

    UNION

    SELECT 'Training' AS Title,'IEABEL6II4DVRMDR' AS ID,'IEABEL6II4DVRNFZ' AS ChildID

    UNION

    SELECT 'Training' AS Title ,'IEABEL6II4DVRMDR' AS ID,'IEABEL6II4DVRNEJ' AS ChildID

    UNION

    SELECT 'Training' AS Title,'IEABEL6II4DVRMDR' AS ID,'IEABEL6II4DVRNEU' AS ChildID

    UNION

    SELECT 'Training' AS Title,'IEABEL6II4DVRMDR' AS ID,'IEABEL6II4DVRNFF' AS ChildID

    UNION

    SELECT 'Training' AS Title,'IEABEL6II4DVRMDR' AS ID, 'IEABEL6II4DVRNEG' AS ChildID

    DROP TABLE #MyTestTable

    Thanks,

    PSB

  • January 6, 2017 at 8:14 am

    #1921124

    Here's an option that you might want to consider. First, it removes unnecessary characters and changes the delimiters into something that shouldn't be part of your string. Then it uses a splitter function which is very fast and you can find its code and explanation in here:http://www.sqlservercentral.com/articles/Tally+Table/72993/

    SELECT t.Title, t.ID, REPLACE(s.Item, '"','') AS ChildID

    FROM #MyTestTable t

    CROSS APPLY (SELECT REPLACE( REPLACE(REPLACE(ChildID, '["', ''), '"]',''), '","', CHAR(7)))r(NewChildID)

    CROSS APPLY dbo.DelimitedSplit8K(r.NewChildID, CHAR(7))s;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 6, 2017 at 9:00 am

    #1921145

    Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

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