如何用一条插入语句插入多行

Question

我试图插入新的行到我的DB。我有155行需要插入。正在发生的事情是，我正在根据现有帐户表添加新用户。我有一个查询，它列出了我需要添加到users表中的新用户，但是我不想键入155次insert。有更简单的方法吗?我已经看到在哪里你可以有多组"值"在插入，但我不确定我将如何实现这一点。例如:

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

我知道我的子查询将返回155个值，因此实际上不会在这里工作，是否有一种方法来修改它，使其工作?

Answer 1

试试这个:

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

或更好地简化(不需要子查询):

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

Answer 2

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

Answer 3

试试这个…

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

Answer 4

查询:

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

Answer 5

只能从另一个表插入，如下所示:

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