CODEWITHSUNDEEP
sqlinsert
Adam Halegua
Adam Halegua

Reputation: 185

Way to insert rows of output into a table?

I was just wondering if there was a way to Insert rows and columns into a table based on a previous query. I don't know if I'm explaining it right so let me try and show you

Below is a simple Outer Join which shows what is missing from the EMPHIREINFO table:

Select b.empno, b.empstate, c.startdt, c.enddt, c.cntrlgth
From EMPADDRESS b Left Outer Join EMPHIREINFO c
On b.empno = c.empno
Order By startdt

Output:

EMPNO   EMPSTATE    STARTDT     ENDDT       CNTRLGTH
9873    NY          20-MAY-11   20-NOV-11   6
7566    CA          15-OCT-11   15-OCT-12   12
7499    MN          31-OCT-11   30-APR-12   6
7369    NJ          15-JAN-12   15-JAN-13   12
5300    NY          11-AUG-12   11-AUG-13   12
7521    NJ          12-NOV-12   12-MAY-13   6
4600    NY          10-DEC-12   10-JUN-13   6
7902    CA          
7934    NY          
7900    MN

All of the empty spots are (null), which I assume you knew but wanted to mention none-the-less. Anyway, I was wondering if there was a way to Insert the EMPNO and EMPSTATE from EMPADDRESS into EMPHIREINFO based on this output. I know I can manually just do an Insert Statement, but I am thinking of a situation down the road where I am not dealing with a table that has 10 rows, but thousands.

I did do some research prior to coming here, but Google and even searching on this website left me empty handed. Any help, as usual, is welcomed.

Upvotes: 0

Views: 76

Answers (2)

Anon
Anon

Reputation: 10918

INSERT destination_table (column1, column2,column3)
SELECT a.column1, a.column2, b.column3
FROM source_table_a AS a
INNER JOIN source_table_b AS b ON (a.key1 = b.key1)

You can use this INSERT syntax with other commands that produce a result set:

INSERT destination_table (column1, column2,column3)
EXEC usp_produce_some_output

Upvotes: 1

Bohemian
Bohemian

Reputation: 425428

SQL allows you to insert into a table using the form

insert into table1
select ...

as long as the column number and types align. If they don't, you can specify the insert columns, eg

insert into table1 (col1, col5, col2)
select ...

So, for you (assuming the column types align):

insert into EMPHIREINFO 
Select b.empno, b.empstate, c.startdt, c.enddt, c.cntrlgth
From EMPADDRESS b Left Outer Join EMPHIREINFO c
On b.empno = c.empno
Order By startdt

You may need to specify columns though, for example if you have an autoincrementing id column (which is common)

Upvotes: 2

Related Questions