insert values from one table into one other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

insert values from one table into one other

Author

Topic

picatshou
Starting Member

12 Posts

Posted - 2014-04-13 : 09:03:00

Hi all,
i am using sql server2008 R2
I have two tables
the first one contain
[table="width: 500, class: grid, align: left"]
[tr]
[td]product[/td]
[td]type[/td]
[td]quantity[/td]

[/tr]
[tr]
[td]pr1[/td]
[td][/td]
[td]2[/td]

[/tr]
[tr]
[td]pr1[/td]
[td][/td]
[td]3[/td]

[/tr]
[tr]
[td]pr2[/td]
[td][/td]
[td]10[/td]

[/tr]
[tr]
[td]pr2[/td]
[td][/td]
[td]20[/td]

[/tr]
[tr]
[td]pr3[/td]
[td][/td]
[td]1[/td]

[/tr]
[/table]
the second is like this:
[table="width: 500, class: grid, align: left"]
[tr]
[td]product[/td]
[td]type[/td]

[/tr]
[tr]
[td]pr1[/td]
[td]T2[/td]

[/tr]
[tr]
[td]pr1[/td]
[td]T2[/td]

[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]

[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]

[/tr]
[tr]
[td]pr3[/td]
[td]t3[/td]

[/tr]
[/table]
so i would like to use an sql request that fill in the first table with values from the second

in fact these tables are extracted the real tables are too much longuer then that
in fact the request should do the following thing if product in table 1=product in table2 then type in table 1 should be equal to the type in table 2
then table 1 will be like that :
[table="width: 500, class: grid, align: left"]
[tr]
[td]product[/td]
[td]type[/td]
[td]quantity[/td]

[/tr]
[tr]
[td]pr1[/td]
[td]T2[/td]
[td]2[/td]

[/tr]
[tr]
[td]pr1[/td]
[td]T2[/td]
[td]3[/td]

[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]
[td]10[/td]

[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]
[td]20[/td]

[/tr]
[tr]
[td]pr3[/td]
[td]T3[/td]
[td]1[/td]

[/tr]
[/table]
thanks a lot for any help in fact i didn't find any method to resolve this problem and i'm a recent user of sql server

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-13 : 20:33:32

If a product has only type in Table2, test following query.

BEGIN TRAN

UPDATE Table1 SET
[type] = Source.[type]
FROM (SELECT DISTINCT product, [type] FROM Table2) Source

SELECT * FROM Table1

ROLLBACK TRAN
--COMMIT TRAN

-------------------------------------
From Japan
Sorry, my English ability is limited.

picatshou
Starting Member

12 Posts

Posted - 2014-04-14 : 05:03:36

thanks nagino for the answer in fact product has a category and a type in the second table
and two products may have the same type :(

like that :

[table="width: 500, class: grid, align: left"]
[tr]
[td]product[/td]
[td]type[/td]
[td]categorie[/td]

[/tr]
[tr]
[td]pr1[/td]
[td]T2[/td]
[td]1[/td]


[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]
[td]2[/td]


[/tr]
[tr]
[td]pr2[/td]
[td]T1[/td]
[td]1[/td]


[/tr]
[tr]
[td]pr2[/td]
[td]T2[/td]
[td]2[/td]


[/tr]
[tr]
[td]pr3[/td]
[td]T3[/td]
[td]1[/td]


[/tr]
[/table]

What can i do i didn't found a solution :( thnx a lot for any help

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-14 : 21:11:44

Two products have same type, is no problem.
One product have two type, is the problem.
If product = pr2 have two type (T1/T2) in second table, what logic/rule decide type.
I think that the logic/rule is key of SQL.

-------------------------------------
From Japan
Sorry, my English ability is limited.

picatshou
Starting Member

12 Posts

Posted - 2014-04-15 : 04:30:03

quote:
Originally posted by nagino

Two products have same type, is no problem.
One product have two type, is the problem.
If product = pr2 have two type (T1/T2) in second table, what logic/rule decide type.
I think that the logic/rule is key of SQL.

thnx for the answer in my case i have Two products have same type sorry the last example contain mistakes
but when i executed your request just one type is associated to all products althoug they should have different types ? what should I do ? thnx a lot for any answer

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-04-15 : 05:09:06

Sorry, my English ability is limited, so I cannot understand long text exactly...
It will be helpful with examples and samples.

So, Let's tidy.

# Case 1 - Simple case

Condition:

Table1

product type quantity
    pr1             2
    pr1             3
    pr2            10
    pr2            20
    pr3             1

Table2

product type
    pr1   T2
    pr1   T2
    pr2   T2
    pr2   T2
    pr3   T3

In this case, pr1's type is T2, pr2's type is T2, pr3's type is T3.
So, use previous sql to get following result.

Result:

product type quantity
    pr1   T2        2
    pr1   T2        3
    pr2   T2       10
    pr2   T2       20
    pr3   T3        1

There is no problem.

# Case 2 - Puzzling case

Condition:

Table1

product type quantity
    pr1             2
    pr1             3
    pr2            10
    pr2            20
    pr3             1

Table2

product type
    pr1   T2
    pr1   T2
    pr2   T2 <- *
    pr2   T1 <- *
    pr3   T3

In this case, pr1's type is T2, pr3's type is T3.
However pr2's type is not deterministic, T1 or T2.
So pr2's type in result cannot determin.

Result:

product type quantity
    pr1   T2        2
    pr1   T2        3
    pr2   ??       10 <- * T1? T2? Which do you need?
    pr2   ??       20 <- * T1? T2? Which do you need?
    pr3   T2        1

In this case, rule is needed to decide type.
ex1. Lexicographic order, MIN(type), like following.

UPDATE Table1 SET
[type] = Source.[type]
FROM (SELECT product, MIN([type]) [type] FROM Table2 GROUP BY product) Source

ex2. Depende on other columns not included in the sample data.
If so, please show me other columns and some rule.

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources