Search code examples
sqlsql-serversplitinsert-update

How to convert a Split String Query [Splitting in to multiple rows] in to an Update Table Query in SQL Server?

Very new at SQL. I have a Query that splits my column value in to Multiple Rows. Now I need to commit it to the actual Table and not just a view through a Select Query.

I've read so many solutions and none of them seem to work. The whole "create Split Function" write all these complicated variables, then create a "Temp Table" then "merge it back to the original table with the value column" and so forth is super confusing to me. Can someone please just put it in plan and simple format for me. There has to be a simpler way for Developer Noobs like myself. Please don't just tell me what I'm doing wrong and assume I know what you're talking about (because I know it's not working, I've identified I'm not a PRO at this), can someone please just show me what I need to do to make this work.

I have the output view which is EXACTLY what I want, but it's just a View, it doesn't update the table...I just want the VALUE output from my Split_String query to insert itself in to my Actual Table or even if I can just Update the Table to look like my view query table or even how I create a New table from my query so that an ACTUAL table looks like my query and is not just a view, that would be grand!

EXAMPLE TABLE of 1 row

please note this is only an example and there are over 120 columns in the actual table and splitting the [DOS File] string is not my issue (I have done that with my query which splits per delimiter and inserts into multiple rows and duplicates all the other columns around it), my issue is trying to commit the value column created by the split_string query to the actual table so that I can do other data transformations on it which requires the string to be split per delimiter

Column1 Name = DOS File

Column1 Value = example1.doc | example2.doc | example3.doc | example4.doc | example5.doc | example6.doc | example7.doc

Column2 Name = NAME

Column2 Value = Sally Andrews

Column3 Name = ADDRESS

Column3 Value = 42 Wallaby Way, Syndey

Column4 Name = PHONE NUMBER

Column4 Value = 123-123

My Split_String Query which has the [Output Column] of my Split_String Query, attached to all the other Columns in the referenced Table:

select value as [New DOS file], [Document Export Edit].*
from [Document Export Edit]
cross apply string_split([DOS file], '|')

The [DOS file] column is a MASSIVE .csv style string and thus the delimiter will be by '|'.

Methods I have tried to use:

Update [Document Export Edit]
 set [New DOS File] = (select value
from [Document Export Edit]
cross apply string_split([DOS file], '|'))
GO

With the following Error Message:

Invalid column name 'New DOS File'.

and this:

Update [Document Export Edit]
      set [New DOS File] = replace([New DOS File],[New DOS File],
             select value from [Document Export Edit]
      cross apply string_split([DOS file], '|'))

With this following Error Message:

Invalid column name 'New DOS File'.

and this:

Alter Table [Document Export Edit]
Add [New DOS File] NVARCHAR(max) null
Add [Test] NVARCHAR(max) null
GO

Update [Document Export Edit]
set [New DOS File] = id1,
    [Test] = id2
from (
  select [DOS File],
          (select top 1 value as val
              from string_split([DOS File], '|')
              order by (row_number() over(order by 1 asc)) asc
              ) as id1,
          (select top 1 value as val
              from string_split([DOS File], '|')
              order by (row_number() over(order by 1 asc)) desc
              ) as id2
              from [Document Export Edit]
      ) A inner join [Document Export Edit] B
      on A.[DOS File] = B.[DOS File]

with the Following Error Message:

Windowed functions, aggregates and NEXT VALUE FOR functions do not support integer indices as ORDER BY clause expressions.

So I tried this:

Update [Document Export Edit]
set [New DOS File] = id1
from (
  select [DOS File],
          (select top 1 value as val
              from string_split([DOS File], '|')
              ) as id1
              from [Document Export Edit]
      ) A inner join [Document Export Edit] B
      on A.[DOS File] = B.[DOS File]

Outcome:

It worked but it just split the first instance of the delimiter, duplicated the exact same thing in the other column and just ignored the rest of the string. SO CLOSE!!! I just want it to do what it did in the first bit and do it for the rest of the string but put it in different rows PER DELIMITER INSTANCE!!

so went forth to try this:

Update [Document Export Edit]
set [New DOS File] = id1
from (
  select [DOS File],
          (select top 1 value as val
              from [Document Export Edit]
              cross apply string_split([DOS File], '|')
              ) as id1
              from [Document Export Edit]
      ) A inner join [Document Export Edit] B
      on A.[DOS File] = B.[DOS File]

Outcome:

Then released that the entire [New DOS file] Column just replicated the first result. Went back and looked at my Syntax and then figured out that I had put a variable restriction only on the first instance ... my bad. So I went and changed it.

Then I tried this:

Update [Document Export Edit]
set [New DOS File] = id1
from (
  select [DOS File],
          (select value as val
              from [Document Export Edit]
              cross apply string_split([DOS File], '|')
              ) as id1
              from [Document Export Edit]
      ) A inner join [Document Export Edit] B
      on A.[DOS File] = B.[DOS File]

with the following Error Message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So it seems that this particular query can only do 1 variable at a time and not multiple rows at once.

Here are some of the links I've tried to follow which have either failed in my task or was WAY too complicated for me to follow:

Turning a Comma Separated string into individual rows

Splitting delimited values in a SQL column into multiple rows

Split column data into multiple rows

Split values over multiple rows

Update multiple rows in SQL Server with IN clause

Split string into multiple rows with multiple columns in paired

https://www.emoreau.com/Entries/Articles/2019/04/Splitting-a-value-into-multiple-rows-in-Microsoft-SQL-Server.aspx

I hope this is enough information for someone to please help me. Thank you.

Solution

  • After so many hours of trying and losing hope. My Beautiful Tech-Savy Friend finally pointed out that I just had the syntax in the wrong order.

    The answer to my question was:

    select value as [New DOS file], dbo.[Document Export Edit].*
into [Document Export String_Split]
from [Document Export Edit]
cross apply string_split([DOS file], '|')