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?

Lock · Accepted Answer · 2015-12-30 21:35:05Z

6

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.

edited Dec 30, 2015 at 21:35

answered Dec 30, 2015 at 5:52

Lock

5,53217 gold badges71 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vineeth Prabhakaran Over a year ago

Thanks @lock but how can i Execute two sql statements in a execute method or should I use two Execute methods like execute(@sql); and execute(@sql1)

Matthieu M. Over a year ago

Is recommending concatenation to form SQL queries really a good idea? I mean, it works in this simplistic example but it already requires quote juggling (10 quotes for 2 strings!) and is liable to break down on any value already including a quote should that s come from concatenation itself.

Lock Over a year ago

It's not how I would do it.. I would avoid dynamic sql however the question showed a variable already with the two rows as a string so my answer is based off an assumption that perhaps the data is getting passed in that way from another system possibly out of the ops control.

Mukund · Accepted Answer · 2015-12-30 05:55:13Z

4

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

Here because username is of type varchar so it's considering @s as single value and inserting it in one row.

answered Dec 30, 2015 at 5:55

Mukund

1,6871 gold badge12 silver badges20 bronze badges

2 Comments

Mukund Over a year ago

@RajaYuvi right, this is another way of doing it. there is no need to prepare two separate query string here and then execute.

Matthieu M. Over a year ago

@RajaYuvi: Please backup this statement. How is Lock's answer better when it's open to SQL Injection and requires fiddling with quotes?

Bala · Accepted Answer · 2015-12-30 06:00:06Z

2

The below logic makes use of substring feature:

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

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

answered Dec 30, 2015 at 6:00

Bala

7041 gold badge8 silver badges21 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows through a variable

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related