Inserting Into A Column From another table

[simon42]

Hi

I am trying to insert a column of data into a table from another table e.g

Table 1
Col_1 Col_2 Col_3 Col_4
1 1 1 Hello
1 2 1 Hello
1 3 1 Hello

Table 2
Col_1 Col_2 Col_3 Col_5
1 1 1 Yes
1 2 1 No

Table 3 (Combined)

Col_1 Col_2 Col_3 Col_4 Col_5
1 1 1 Hello yes
1 2 1 Hello No
1 3 1 Hello NULL

Can any one help as this is starting to drive me mad

Cheers

Si

[rmiao]

You can do it in two steps like:

1. insert table3 select * from table1 ...

2. update table3 set col_5 = table2.col_5 from table3, table2 where tabl3.col_1 = table2.col_1 and table3.col_2 = table2.col_2 and ...

[simon42]

Nice one M8 that woked a treat .. I'm very new at this!! Your help is very much apriciated!!

Cheers

Si

[nosepicker]

Or you can do it in one step:

INSERT INTO Table3
SELECT Table1.Col_1, Table1.Col_2, Table1.Col_3, Table1.Col_4, Table2.Col_5
FROM Table1 LEFT JOIN Table2 ON Table1.Col_2 = Table2.Col_2

[simon42]

Hi

Using the Update worked great for what I want to do, That is to create a table full of info then add more columns to it then add info to those columns.

The only problem I now have is that I cant summarise data to put into that column e.g.

I’ve created a product_info table and I want to add things like stock qty and order totals
I can add stock qty’s by using the update statement like this

Update Buying1
set Stock = LAGER.LAG_BESTAND
from Buying1, FuturERS.dbo.LAGER
where Branch_No = LAG_FILIALE and
Barcode = LAG_REFNUMMER

However when I come to do sales I need to summarise the OPOs.
The way I would like to do this is

update Buying1
set OPOs = Sum (BDT_ANZ_RESTMENGE)
from Buying1, FuturERS.dbo.BESTZEIL
WHERE Branch_No = BDT_ORIGNR and
Barcode = BDT_REFNUMMER
Group By BDT_ORIGNR, BDT_REFNUMMER

But it doesn’t work any suggestions??

Cheers

SI

[rmiao]

DId you get any error? Can you post sample schema and data?

[simon42]

Hi

An example of what I'm trying to do is I want to create a table called table1 with 2 colums barcode and stock. then insert into this the stock info from table 2, then add a colunm called Sales, then sumarise the info in table 3 and update it into table 1.

Table1 (my new table)
Barcode stock sales_pcs
10016 50 2
10017 80 3

table2 (existinmg stoc table)
Barcode Stock
10016 50
10017 80

Table3 (existing sales records)
Date Barcode Sales_val Sales_PCS
20060305 10016 50 1
20060304 10016 50 1
20060304 10017 20 2
20060302 10017 20 1


I can do the first and scend part fine its just sumarising the data to upload

hope this helps

Cheers

Si

[nosepicker]

Again, this can all be done in one step. I don't know why you insist on doing this in two or more steps:

INSERT INTO Table1
SELECT Stock.Barcode, Stock.Stock, SUM(Sales.Sales_PCS)
FROM Stock LEFT JOIN Sales
ON Stock.Barcode = Sales.Barcode
GROUP BY Stock.Barcode, Stock.Stock