SQL Server split string values into rows and columns and insert into table

In SQL Server 2016+ you can use string_split().

In SQL Server pre-2016, using a CSV Splitter table valued function by Jeff Moden along with left() and stuff() (or right()) with charindex():

declare @status nvarchar(max) = '123456|House,123567|Flat,789463|Car,635491|Lorry,'

select 
    nvalue = left(s.Item,charindex('|',s.Item)-1)
  , [Type] = stuff(s.Item,1,charindex('|',s.Item),'')
from dbo.DelimitedSplitN4K(@status,',') s
where s.Item <>''

rextester demo: http://rextester.com/QQZUC78477

returns:

+--------+-------+
| nvalue | Type  |
+--------+-------+
| 123456 | House |
| 123567 | Flat  |
| 789463 | Car   |
| 635491 | Lorry |
+--------+-------+

splitting strings reference:

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Split strings the right way – or the next best way - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand
• Ordinal workaround for **string_split()** - Solomon Rutzky

You can either use split() from the web or in SQL Server 2016. Then:

with lines(line) as (
      select l.*
      from dbo.split(@str, '|') l
     )
insert into resultsTable (nvalue, type)
    select left(line, charindex(',', line) - 1),
           stuff(line, charindex(',', line), len(line), '')
    from lines;

Using SUBSTRING(), Xml and Cross apply we can get desired result

DECLARE @Value NVARCHAR(max) = ',123456|House,123567|Flat,789463|Car,635491|Lorry,'
DECLARE @DYVALUE TABLE (VALUE NVARCHAR(MAX))

INSERT INTO @DYVALUE (VALUE)
SELECT @VALUE

;WITH cte
AS (
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS Value
    FROM (
        SELECT CAST('<S>' + REPLACE(Value, ',', '</S><S>') + '</S>' AS XML) AS Value
        FROM @DyValue
        ) AS A
    CROSS APPLY Value.nodes('/S') AS Split(a)
    )
SELECT *
FROM (
    SELECT SUBSTRING(Value, 0, CHARINDEX('|', Value)) AS nvalue
        ,SUBSTRING(Value, CHARINDEX('|', Value) + 1, LEN(Value)) AS [Type]
    FROM Cte
    ) DT
WHERE nvalue <> ''
    AND [Type] <> ''

Result:

+--------+-------+
| nvalue | Type  |
+--------+-------+
| 123456 | House |
| 123567 | Flat  |
| 789463 | Car   |
| 635491 | Lorry |
+--------+-------+