Insert multiple lines through a variable

Question

Actually I want to insert some rows into the table. Table structure

Create Table tbl_username
(id int  autoincrement,
username varchar(100),
Primary key(id))

and I tried to insert some lines like

Declare @s as varchar(100)
set @s='(''name1''),(''name2'')'
insert into tbl_username(username)values @s;

but I get the output as

id        username
1         (''name1''),(''name2'')

The actual required conclusion for me is

id          username
1           name1
2           name2

How can i achieve this?

Answer 1

Use dynamic SQL

Declare @s as varchar(100)
Declare @sql as varchar(max)
set @s='(''name1''),(''name2'')'
set @sql = 'insert into tbl_username(username) values ' + @s;

execute(@sql);

However, I could avoid dynamic SQL where possible. The standard way to do this is if your values ​​are not in a variable:

INSERT INTO tbl_username(username) values ('name1'),('name2')

or

INSERT INTO tbl_username(username) values ('name1')
INSERT INTO tbl_username(username) values ('name2')

If possible, select one of the above instead of the previously mentioned dynamic parameter.

Answer 2

insert into tbl_username(username)values ('name1'),('name2'),.....;

, username varchar, @s .

Answer 3

The logic below uses the function substring:

DECLARE @s as varchar(100), @Delimiter VARCHAR(1)

SET @s = 'name1,name2'
SET @Delimiter = ','

DECLARE @Position INT, @ListItem VARCHAR(MAX)

WHILE CHARINDEX(@Delimiter, @s) > 0
    BEGIN
        SELECT @Position  = CHARINDEX(@Delimiter, @s)  
        SELECT @ListItem = SUBSTRING(@s, 1, @Position-1)

        INSERT INTO tbl_username 
            SELECT @ListItem

        SELECT @s = SUBSTRING(@s, @Position+1, LEN(@s)-@Position)
    END

INSERT INTO tbl_username 
    Select @s

SELECT * FROM tbl_username

Try: http://sqlfiddle.com/#!6/d0f76/1/0