how to set insert string values in sql server?

Question

I have this query in sql server

 DECLARE @SQL NVARCHAR(MAX),@X NVARCHAR(MAX)

 SET @X = 'val1,val2'

 SET @SQL = 'INSERT INTO [Persons] VALUES (' + @X + ')' 

 print (@SQL)
 exec (@SQL)

But i don't know how to set @x value by the right way ?

Answer 1

You have to DECLARE every parameter.

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @X NVARCHAR(MAX)

     SET @X = '''val1'',''val2'''

     SET @SQL = 'INSERT INTO [Persons] VALUES (' + @X + ')' 

     print (@SQL)
     exec (@SQL)

Answer 2

You need to add escaped quotes:

SET @X = '''val1'',''val2'''

To make @X contain 'val1','val2'

Answer 3

Here's a SQL Fiddle.

If Val1 and Val2 are string fields, you have to escape single quotes around string values as follows.

 DECLARE @SQL NVARCHAR(MAX)
 DECLARE @X NVARCHAR(MAX)

 SET @X = '''val1'',''val2'''

 SET @SQL = 'INSERT INTO [Persons] VALUES (' + @X + ')' 

 print (@SQL)
 exec (@SQL)

Answer 4

see from this link use split function, How do I split a string so I can access item x?

then

Declare @ColNames VARCHAR(1000)='val1,val2'

INSERT INTO [Persons]
select item from dbo.SplitString(@ColNames,',')