3

I have the following string variable to split into temp table.

Example:

DECLARE @Str VARCHAR(MAX) = '10000,200000'

Now I want it to store in #Temp table.

Like this:

Table: #Temp

Cola     Colb
--------------
10000   200000
MAK
  • 6,824
  • 25
  • 74
  • 131
  • possible duplicate of [Split function equivalent in T-SQL?](http://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql) among many many others. – Daniel E. Dec 08 '14 at 12:01
  • possible duplicate of [Split multiple string's into multiple columns](http://stackoverflow.com/questions/27264985/split-multiple-strings-into-multiple-columns) your Question only – Rajesh Dec 08 '14 at 12:03
  • yes,split the string,then pivot it.Insert the pivoted resultset into #temp table – KumarHarsh Dec 08 '14 at 12:03

1 Answers1

6

Assuming that your columns is not varchar

CREATE TABLE #Temp
(
  Col1 int,
  Col2 int
)

DECLARE @Str VARCHAR(MAX) = '10000,200000'
DECLARE @SQLString VARCHAR(MAX) = 'INSERT #Temp Select ' + @Str

EXEC (@SQLString)
hon2a
  • 7,006
  • 5
  • 41
  • 55
Jaques
  • 2,215
  • 1
  • 18
  • 35
  • 2
    `DECLARE @SQLString VARCHAR(MAX)` – chridam Dec 08 '14 at 12:07
  • 2
    except the declaration isn't valid and it doesn't execute as posted – Tanner Dec 08 '14 at 12:07
  • `Msg 208, Level 16, State 0, Line 1 Invalid object name '#Temp'.` in SQL Server 2008 R2 environment I am getting this error – Rajesh Dec 09 '14 at 05:43
  • @Rajesh, I thought that was the error that you got. Have you ever sat down for a second a thought why you would received such an error? Maybe because the temp table does not exist? MAK asked how he could get values into table #Temp, and I gave him the answer. If you haven't created the temp table in your session, how do you expect the values to be inserted into that table? Create a #Temp table for yourself in your current session with 2 columns, and then execute it. And then you might have better success – Jaques Dec 09 '14 at 05:48
  • @Jaques I had created the table but with only one column so its thrwing the error after your comment I created the table with two columns now its working fine Edited your Answer in order to cancel my down vote – Rajesh Dec 09 '14 at 06:46
  • @Rajesh, MAK never said that he wants to create a temp table from the string result. I quote "Now I want it to store in #Temp table.". If he wanted the temp table to be created he would've said so, so I'm not sure why you are so anal about this if the question was answered according to what MAK wanted. – Jaques Dec 09 '14 at 07:23
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66445/discussion-between-rajesh-and-jaques). – Rajesh Dec 09 '14 at 07:40