insert data from 3 tables with the same fields into 1 table

115 views Asked by At

I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one query. how do I do this?

i have used 3 separate insert into statements that look like this: but i need it in one statement:

INSERT INTO dbo.dummy
SELECT
dbo.jobcost.code,
dbo.jobcost.ven,
dbo.jobcost.date,
dbo.jobcost.jno,
FROM 
dbo.jobcost
WHERE
dbo.jobcost.jno > '40000'

INSERT INTO dbo.dummy
SELECT
dbo.servic15.code,
dbo.servic15.ven,
dbo.servic15.date,
dbo.servic15.jno
FROM 
dbo.servic15
WHERE

dbo.servic15.jno > '40000'

INSERT INTO dbo.dummy
SELECT
dbo.accnt.code,
dbo.accnt.ven,
dbo.accnt.date,
dbo.accnt.jno
FROM 
dbo.accnt
WHERE
dbo.jobcost.jno > '40000'
2

There are 2 answers

3
54l3d On BEST ANSWER

You need INSERT ... SELECT statement

INSERT INTO dummy  (code, jno, ven, date)
  SELECT code, jno, ven, date FROM accnt jno > '40000'
  UNION ALL
  SELECT code, jno, ven, date FROM jobcost jno > '40000'
  UNION ALL
  SELECT code, jno, ven, date FROM servic15 jno > '40000';

If the table is not created yet, use CREATE TABLE ... SELECT.

0
Vbasic4now On
INSERT INTO dummy  (code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno)
  SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno FROM accnt 
  WHERE jno > '40000'
  UNION ALL
  SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno FROM jobcost 
  WHERE jno > '40000'
  UNION ALL
  SELECT code, invno, ven, invdate, amon, accno, amnt, jno, saccno, ckno FROM servic15 
  WHERE jno > '40000';

this worked