Insert Select into with same ID

Insert Split Delimited Strings in a Column with same ID.

Lets say I have demo setup as follows:

USE DEMODATABASE 
GO 

CREATE TABLE EMPLOYEEID_Address 
  ( 
     ID            INT , 
     Address VARCHAR(MAX) 
  )

I declare some variables

DECLARE @id INT
DECLARE @Addresses VARCHAR(MAX) 
DECLARE @SEPARATOR CHAR(1)
SET @id = 1 
SET @SEPARATOR='|'
SET @Addresses='159 North Manchester Rd.
Edison, NJ 08817|209 West Fulton Lane
Braintree, MA 02184|...'

Note: the @addresses sting is dynamic and not fixed

Now my aim is to insert into table EMPLOYEEID_Address multiple address with same the id using the STRING_SPLIT function.

/*TARGET TABLE*/
Id  Address
1   159 North Manchester Rd. Edison, NJ 08817
1   209 West Fulton LaneBraintree, MA 02184
1   ...

So I tried:

INSERT INTO EMPLOYEEID_Address 
            (ID,Address)
Select @id as ID, 
SELECT * 
FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

But this didn’t work, while

INSERT INTO EMPLOYEEID_Address 
            (Address)
SELECT * 
FROM   STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR)

works with result as:

Id   Address
NULL 159 North Manchester Rd. Edison, NJ 08817
NULL 209 West Fulton LaneBraintree, MA 02184

Is there any way I can achieve this or a better way to achieve my target table with id and string passed as parameters?

Answer

I think you want:

INSERT INTO EMPLOYEEID_Address (ID, Address)
    Select @id as ID, s.value
    from STRING_SPLIT(@EMPLOYEENAME, @SEPARATOR) s;

The column returned by string_split() is called value.

Advertisement

Answer