Insert comma seperated float and varchar string into a temporary table

Question

I have a string with values:

 @value = 'e65.89,f34.yy,sw21.67,aqw21,g45.g4'

I need to insert this string as individual rows into a temporary table.

like:

     VALUES
     e65.89
     f34.yy
     sw21.67 
     aqw21
     g45.g4

I Tried this code,it works fine with integer values but when a float or varchar value is given it shows error:

code:

CREATE TABLE #Temp (SNo INT IDENTITY(1,1),Code VARCHAR(max))

     Declare @value varchar(max)
     set @value = 'e65.89,f34.yy,sw21.67,aqw21,g45.g4'
    DECLARE @InsertStatement varchar(max) = 'insert into #Temp values ('+REPLACE(@value ,',','),(')+');';
    EXEC (@InsertStatement);

    SELECT * FROM #Temp;

Error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'e'.

Starting with SQL Server 2016 there is `STRING_SPLIT`. With lower versions you will find tons of examples here... — Shnugo, Jan 23 '18 at 10:28
You need to quote strings. You can't insert the values `(e65.89,f34.yy,sw21.67,aqw21,g45.g4)`... Instead you need to insert the values `('e65.89','f34.yy','sw21.67','aqw21','g45.g4')` — MatBailie, Jan 23 '18 at 10:29
One hint about the linked question: This is rather old, so are the answers. It is a good idea to sort them and search for the newer ones... Your own code would work if you simply wrap your values with quotes `'e65.89'`. — Shnugo, Jan 23 '18 at 10:30
I feel like I saw this exact question yesterday or Sunday, but the post has since been deleted. — Thom A, Jan 23 '18 at 10:33
@MatBailie wrapping the values with quotes will work but data's in values variable is filled dynamically using an stored procedure with comma delimiter — ASHWANT, Jan 23 '18 at 10:35
@ASHWANT, [read this question](https://stackoverflow.com/q/37331465/5089204), it is almost exactly the same as yours... — Shnugo, Jan 23 '18 at 10:41
@ASHWANT - Your data is built using a call to `REPLACE()`, and can be quoted in there. If you must use this method, you really should understand how to use it. It's massively open to SQL Injection Attacks. Even if you're not open to attacks *(the data could be machine generated)*, if the data ever includes quotes, you need to escape them. Don't do this. Really, don't do this. — MatBailie, Jan 23 '18 at 10:41
Use `STRING_SPLIT` if on 2016 onwards, or the `UPDATE1` or `UPDATE2` from @Shnugo's answer *(linked in a comment above)*. Splitting the string isn't going to result in problems. Embedding data in Dynamic SQL in this way is what's dangerous *(`sp_exectesql` allows passing parameters for exactly that reason.)* — MatBailie, Jan 23 '18 at 10:58

score 0 · Accepted Answer · answered Jan 23 '18 at 10:37

0

Try wrapping the values you are inserting with '' (single quote marks)

CREATE TABLE #Temp (SNo INT IDENTITY(1,1),Code VARCHAR(max))

Declare @value varchar(max)
set @value = 'e65.89,f34.yy,sw21.67,aqw21,g45.g4'
DECLARE @InsertStatement varchar(max) = 'insert into #Temp values (''' + 
REPLACE(@value ,',','''),(''')+ '''' + ');';
EXEC (@InsertStatement);

SELECT * FROM #Temp;

answered Jan 23 '18 at 10:37

Andre Lombaard

6,985
13
55
96

I tried this code only table is created values is not been inserted – ASHWANT Jan 23 '18 at 10:43
When I run this code on my side, the table is created and all values are inserted. Try to do a PRINT @InsertStatement to see where your insert statement is breaking – Andre Lombaard Jan 23 '18 at 10:45
When using PRINT @InsertStatement, your insert should display something like insert into #Temp values ('e65.89'),('f34.yy'),('sw21.67'),('aqw21'),('g45.g4'); – Andre Lombaard Jan 23 '18 at 10:47
i tried it with print statement too. still same result while executing it shows 5 rows affected but It doesnt insert it in my table. It shows :(5 row(s) affected) insert into #Temp values ('e65.89'),('f34.yy'),('sw21.67'),('aqw21'),('g45.g4'); – ASHWANT Jan 23 '18 at 10:51
If it shows 5 rows affected then it inserted into your table. Do you get nothing back when selecting from the temp table? Also make sure that you drop the temp table by using drop table #Temp before you run the statement again – Andre Lombaard Jan 23 '18 at 10:55
i dropped the table before executing it shows 5 rows affected but selecting the table displays nothing except the column name. – ASHWANT Jan 23 '18 at 10:56
Silly question, you did remember to replace the PRINT statement with EXEC again? When I copy and paste the exact code in my answer I can definitely insert and select the values – Andre Lombaard Jan 23 '18 at 10:59
yep i got it @user65439 i didnt select the table in which i inserted – ASHWANT Jan 23 '18 at 11:05
Glad you got it sorted – Andre Lombaard Jan 23 '18 at 11:07

Insert comma seperated float and varchar string into a temporary table

1 Answers1