How to insert multiple rows with one insert statement

Question

I'm trying to insert new rows into my DB. I have 155 rows that I need to insert. What is happening is that I am adding new users based on an existing account table. I have a query that lists the new users that I need to add to the users table, but I don't want to have to type out the insert 155 times. Is there an easier way of doing this? I've seen where you can have multiple sets of 'Values' in the insert, but I'm not sure how I would implement this. For example:

insert into tblUsers (State,City,Code)
Values ('IN','Indy',(select UserCode from tblAccounts where UserCode in(Select UserCode from tblAccounts where State = 'IN')))

I know that my sub-query will return 155 values and so won't actually work here, is there a way to modify this so that it will work?

Goose · Accepted Answer · 2014-03-20 20:47:34Z

1

Try this:

INSERT INTO tblUsers (State,City,Code)
SELECT 'IN','Indy', UserCode
FROM tblAccounts
WHERE UserCode IN
    (SELECT UserCode
     FROM tblAccounts
     WHERE State = 'IN')

or better simplified (a subquery is not needed):

INSERT INTO tblUsers (State,City,Code)
SELECT 'IN','Indy', UserCode
FROM tblAccounts
WHERE State = 'IN'

edited Mar 20, 2014 at 20:47

answered Mar 20, 2014 at 20:40

Goose

3,2894 gold badges30 silver badges37 bronze badges

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

Comments

Milen · Accepted Answer · 2014-03-20 20:38:48Z

1

insert into tblUsers (State,City,Code)
   Select 'IN','Indy',  UserCode 
   from tblAccounts 
   where UserCode in (Select UserCode 
                     from tblAccounts 
                     where State = 'IN')

answered Mar 20, 2014 at 20:38

Milen

8,8977 gold badges47 silver badges59 bronze badges

Comments

user2989408 · Accepted Answer · 2014-03-20 20:40:16Z

1

Try this...

INSERT INTO tblUsers (State,City,Code)
SELECT 'IN','Indy', UserCode 
FROM tblAccounts 
WHERE UserCode IN (SELECT UserCode FROM tblAccounts WHERE State = 'IN')

answered Mar 20, 2014 at 20:40

user2989408

3,1371 gold badge20 silver badges15 bronze badges

Comments

Justin · Accepted Answer · 2014-03-20 20:43:53Z

1

Query:

INSERT INTO tblUsers (State,City,Code)
SELECT 'IN','Indy', UserCode
FROM tblAccounts
WHERE State = 'IN'

answered Mar 20, 2014 at 20:43

Justin

9,7336 gold badges38 silver badges49 bronze badges

Comments

torun · Accepted Answer · 2014-03-20 20:40:16Z

0

You can only insert from another table, like this:

insert into tblUsers (State,City,Code)
SELECT * FROM table1

answered Mar 20, 2014 at 20:40

torun

4901 gold badge5 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to insert multiple rows with one insert statement

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related