0

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.

Chad Echicken
  • 35
  • 1
  • 9
  • If it's a CSV, why not import it as CSV using `bcp` or `BULK INSERT` into a proper table? As for the errors, they are pretty clear - there's no column named `New DOS File` in that table. Changing the query won't make a non-existent column work. There's no need for complex queries either. What is the actual table schema? – Panagiotis Kanavos Jun 18 '20 at 06:33
  • No it's an Excel Import File and one of the Columns in the import file is a CSV file string, every other columns are normal Excel Data Format. – Chad Echicken Jun 18 '20 at 06:37
  • 1
    Add the relevant information to the question. You don't need a complex query, you need to add the missing column and use a simple UPDATE. Anything after the first two attempt isn't needed. If you do add that column, you may need nothing more than `update thatTable set someCol=value from thatTable cross apply string_split(otherCol,'|')`. Without the table schema and some sample data though, people can't help by testing alternatives – Panagiotis Kanavos Jun 18 '20 at 06:40
  • @PanagiotisKanavos Yes, I have tried that too in my Post.... `Update [Document Export Edit] set [New DOS File] = (select value from [Document Export Edit] cross apply string_split([DOS file], '|')) GO` I got 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.]` – Chad Echicken Jun 18 '20 at 06:42
  • Or not - UPDATE only updates existing columns, it won't generate new columns. You need an INSERT to create new columns which means that a) a view may be a *very* good idea or that b) the cleaned data should be stored in a separate table. You could use a `MERGE` but that would lead to duplicate columns each time you run that query. – Panagiotis Kanavos Jun 18 '20 at 06:42
  • `Yes, I have tried that too` tried what? What do you have now, what do you want to get? What you posted doesn't explain the problem at all. It looks like an UPDATE is completely inappropriate too – Panagiotis Kanavos Jun 18 '20 at 06:44
  • This looks like an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) - you have a problem X (how to convert non-normalised data to proper rows) and think Y is the answer(splitting and an UPDATE) but when that failed (UPDATE can't insert new rows) you ask about Y, not the actual problem X. UPDATE won't work by definition, so what's the actual problem? Why do you want to store the clean data in the dirty-data table? – Panagiotis Kanavos Jun 18 '20 at 06:46
  • Perhaps the simplest and fastest solution would be to store the clean data in a separate table. Another solution would be to create an [indexed view](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15), thus "materialising" the results and forcing the server to store them on disk. – Panagiotis Kanavos Jun 18 '20 at 06:48
  • @PanagiotisKanavos Yes I have also tried the insert queries `insert [Document Export Edit] ([New DOS file]) values (select value from [Document Export Edit] cross apply string_split([DOS File], '|'))` but it either appends the values and makes all my other columns `NULL` or it comes up with this error message `Incorrect syntax near the keyword 'select'. Incorrect syntax near ')'.` – Chad Echicken Jun 18 '20 at 06:50
  • You're still trying to make the wrong answer work - trying to modify the source table. ` it either appends the values` that's what INSERT does, that's what you actually asked for in the question! `and makes all my other columns NULL` because you didn't provide any values for those other columns – Panagiotis Kanavos Jun 18 '20 at 06:52
  • I have stated my problem quite clearly in my post. I have the exact view I want through my select query but I can't even create a new table from my query and for the life of me I can't figure out why.... I literally just said in my post "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, that would be grand!". This is not the final view hence the UPDATE is needed. – Chad Echicken Jun 18 '20 at 06:54
  • And those statements are self-contradictory. You can't use UPDATE when you have new rows. You need INSERT. When you INSERT you need to provide values for all fields. The simple answer to this is to write the output you want to a new table. – Panagiotis Kanavos Jun 18 '20 at 06:56
  • @PanagiotisKanavos Look, I don't think you understand what I am trying to ask. Thank you for your time but it's not very helpful (I'm sorry). I've tried MODIFYING the column and it doesn't work. I just need someone to show me what the right SYNTAX looks like because I don't think I'm doing it correctly. To clarify my issue more, I will add a sample table to my Post. If you could please stop telling me what I'm doing wrong and SHOW me how you do it, with screenshots, that would be more helpful, even step by step instructions would be more helpful. I KNOW I'm doing it wrong! – Chad Echicken Jun 18 '20 at 07:00

1 Answers1

0

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], '|')
Chad Echicken
  • 35
  • 1
  • 9