CODEWITHSUNDEEP
sqlsql-server-2008
Mike
Mike

Reputation: 1938

How to insert multiple rows with one insert statement

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?

Upvotes: 0

Views: 96

Answers (5)

Goose
Goose

Reputation: 3289

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'

Upvotes: 1

Justin
Justin

Reputation: 9724

Query:

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

Upvotes: 1

torun
torun

Reputation: 480

You can only insert from another table, like this:

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

Upvotes: 0

user2989408
user2989408

Reputation: 3137

Try this...

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

Upvotes: 1

Milen
Milen

Reputation: 8877

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

Upvotes: 1

Related Questions