Inserting multiple rows through a variable

Question

Actually I want to insert multiple rows to a table. The Structure of the table is

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

and I tried to insert multiple rows 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'')

Actually the required output 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 would avoid dynamic SQL where possible. The standard way to do it if your values are not in a variable is:

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, opt for one of the above instead of the dynamic option originally mentioned.