CODEWITHSUNDEEP
sql-serversql-server-2012
AskMe
AskMe

Reputation: 2561

Insert into table with where condition

I have two tables

Table1

            OfficeID  OfficeName
            -------------------
            1          UK
            2          JP
            3          US1
            4          US2
            5          US3
            6          US4

OfficeID is an identity auto increment column.

I need to add couple of more offices (e.g. US5,US6) into table1:

insert into  Table1 (OfficeName) 
values ('US5'), ('US6')

I have another table2

            OrgID   OfficeID
            ---------------- 
            1            1
            2            2 
            3            3
            3            4
            3            5
            3            6

After inserting the US5 and US6 the new data in table 1 will be

            OfficeID  OfficeName
            -------------------
            1          UK
            2          JP
            3          US1
            4          US2
            5          US3
            6          US4
            7          US5
            8          US6

After this, I would like to insert officeID into table 2 so that my table 2 would look like this:

            OrgID   OfficeID
            ---------------- 
            1            1
            2            2 
            3            3
            3            4
            3            5
            3            6 
            3            7
            3            8

Here is how I'm trying to do this

insert into Table2 (OfficeID) 
    select OfficeID 
    from table1 
    where OfficeID in ((7), (8))
      and table2.OrgID = 3

How to achieve this? Thanks

Upvotes: 0

Views: 74

Answers (3)

Irvin Escalante
Irvin Escalante

Reputation: 31

try to make a inner join to the table 2 because you are trying to filter by column on table 2 and are inaccesable in the select statement, try this

 INSERT INTO Table2  (OfficeID) 
 SELECT OfficeID FROM table1 INNER JOIN table2 ON (CLAUSE) WHERE table1.OfficeID in ((7),(8))
 AND   table2.OrgID=3

Upvotes: 0

Shekar Kola
Shekar Kola

Reputation: 1297

If you want identity column to be inserted into Table2 try OUTPUT clause

Insert into Table1 (OfficeName)
    OUTPUT inserted.OfficeID, 3 INTO Table2  (OfficeID, OrgID) 

values
('US5'),
('UK6')
go

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3159

You should define all columns you want to insert:

         insert into Table2  (OfficeID, OrgID) 
         select OfficeID, 3 from table1 where OfficeID in ((7),(8))

Upvotes: 2

Related Questions