Insert into within temp named result set

Transact-SQL
1

Hi

I am trying to split up a table with multiple values in one row (seperated by "/") and match each of these against associated USERID, which I have achieved. However I want to put this data into a table that already exists and tried most of the usual insert into statements to do this but keeps failing giving object (table name) already exists. I tried dropping table before statement where I was running select....INTO and it still said object (table name) already exists.

The code I currently have (minus variable declarations showing) is:

`BEGIN
WITH [splits] AS (
SELECT
@UID [ID],
CHARINDEX(@sep, @ApplicationRequested) [pos],
0 [lastPos]
UNION ALL
SELECT
@UID [ID],
CHARINDEX(@sep, @ApplicationRequested, [pos] + 1),
[pos]
FROM [splits]
WHERE [pos] > 0)

Want to insert this data into table>>>>>
SELECT
ID,
SUBSTRING(@ApplicationRequested,[lastPos] + 1,CASE WHEN [pos] = 0 THEN 8000 ELSE [pos] - [lastPos] - 1 END) [value]
FROM [splits];
end`

Could anyone suggest the correct way to insert this data into a table? Data, after processing, is like this:

ID      Value
==      ====
1        BY
1        TY
2        HT
3        GT
3        JU
3        KI

Many thanks

G

2

I don't see the insert at the end of the cte, try this:

 SELECT 
      ID,
	  SUBSTRING(@ApplicationRequested,[lastPos] + 1,CASE WHEN [pos] 
	  = 0 THEN 8000 ELSE [pos] - [lastPos] - 1 END) [value] 
	  INTO MyInsertNewTable
	  	  FROM [splits];
3

Thanks I got it working using this

BEGIN
				WITH [splits] AS (
			SELECT
				@UID [ID],
				CHARINDEX(@sep, @ApplicationRequested) [pos],
				0 [lastPos]
			UNION ALL
			SELECT
				@UID [ID],
				CHARINDEX(@sep, @ApplicationRequested, [pos] + 1),
				[pos]
			FROM [splits]
			WHERE [pos] > 0)
			INSERT INTO AccessRequested (AccessID,AccessRequested)
			SELECT
			ID,
			SUBSTRING(@ApplicationRequested,[lastPos] + 1,CASE WHEN [pos] = 0 THEN 8000 ELSE [pos] - [lastPos] - 1 END) [value]
		    FROM [splits];

I understood that 'select....into' created a new table and at the time I was not dropping the table at that start of the statement hence the object exists error; I was then getting error cursor already exists because the statement was stopping before getting to the end and deallocating the cursor. I then decided to just include an insert into so do not need to worry about dropping and recreating table; will only need to truncate table at start every time I run it.