Compare two table

Question

Compare two table

diboy79

Old Hand

Points: 308
More actions
February 14, 2011 at 12:54 am

#97646

Dear master,
I have to compare two table that have same amount (one to one record).
my first table is Orders:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblOrders','U') IS NOT NULL
DROP TABLE #tblOrders
--===== Create the Orders table
CREATE TABLE #tblOrders
(
IDInt IDENTITY(1,1) PRIMARY KEY,
Orders_NumberVarchar(3),
Orders_AmountNumeric(18,2)
)
--===== Insert the test data into the test table
INSERT INTO #tblOrders
(Orders_Number, Orders_Amount)
SELECT '1A',100 UNION ALL
SELECT '2B',200 UNION ALL
SELECT '3A',300 UNION ALL
SELECT '4B',300 UNION ALL
SELECT '5A',300 UNION ALL
SELECT '6A',300 UNION ALL
SELECT '7A',400 UNION ALL
SELECT '8B',400 UNION ALL
SELECT '9A',400 UNION ALL
SELECT '',400
my second table is Inventory:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblInventory','U') IS NOT NULL
DROP TABLE #tblInventory
--===== Create the Orders table
CREATE TABLE #tblInventory
(
IDInt IDENTITY(1,1) PRIMARY KEY,
Inventory_NumberVarchar(3),
Inventory_AmountNumeric(18,2)
)
--===== Insert the test data into the test table
INSERT INTO #tblInventory
(Inventory_Number, Inventory_Amount)
SELECT '',100 UNION ALL
SELECT '2A',200 UNION ALL
SELECT '3A',300 UNION ALL
SELECT '4A',300 UNION ALL
SELECT '',300 UNION ALL
SELECT '7A',400 UNION ALL
SELECT '8A',400 UNION ALL
SELECT '10A',400 UNION ALL
SELECT '11A',400 UNION ALL
SELECT '12A',400
after comparing/matching tblOrders.Amount=tblInventory.Amount, my goal is:
1. Update tblInventory set Inventory_Number=Orders_Number where isnull(Inventory_Number,0)='0'
2. Update tblOrders set Orders_Number=Inventory_Number where
isnull(Inventory_Number,0)<>'0'
I was very confused, there was a lot of record that have same amount when I joined those table.
could you help me how to compare and update those table, so finally I have a table that relate to each other as shown below::-D
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblOrders_Inventory','U') IS NOT NULL
DROP TABLE #tblOrders_Inventory
--===== Create the Orders table
CREATE TABLE #tblOrders_Inventory
(
IDInt IDENTITY(1,1) PRIMARY KEY,
Orders_NumberVarchar(3),
Inventory_NumberVarchar(3),
AmountNumeric(18,2)
)
--===== Insert the test data into the test table
INSERT INTO #tblOrders_Inventory
(Orders_Number,Inventory_Number, Amount)
SELECT '1A','1A',100 UNION ALL
SELECT '2A','2A',200 UNION ALL
SELECT '3A','3A',300 UNION ALL
SELECT '4A','4A',300 UNION ALL
SELECT '5A','5A',300 UNION ALL
SELECT '7A','7A',400 UNION ALL
SELECT '8A','8A',400 UNION ALL
SELECT '9A','9A',400 UNION ALL
SELECT '10A','10A',400

thanks for your help 🙂
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

Wow! First time user who obviously read the "Forum Etiquette" article. Very well done! I'm surprised people didn't jump all over this with solutions.

Lemme study it a bit and I'll be back...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Ah... first problem I see...

Why are the "empty string" inventory numbers in the Inventory table? Without a proper inventory number on those rows, the amounts mean nothing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

diboy79 Old Hand Points: 308 More actions · Answer 3

Jeff Moden (2/14/2011)
Ah... first problem I see...
Why are the "empty string" inventory numbers in the Inventory table? Without a proper inventory number on those rows, the amounts mean nothing.

thanks Jeff,

yes, you are correct. that is the problem for me. inventory_number column should not be any empty data therein. I have to rebuild the inventory table.

I usually do inventory_number updates manually, one by one, but at the amount of data that I've met more and more, the manual is not possible to do.

I hope the table inventory and orders tables can be linked one by one for the row that has the same amount.

inventory table should be the parent table, so that when inventory_number not empty then orders_number which must be changed.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

after comparing/matching tblOrders.Amount=tblInventory.Amount, my goal is:
1. Update tblInventory set Inventory_Number=Orders_Number where isnull(Inventory_Number,0)='0'
2. Update tblOrders set Orders_Number=Inventory_Number where
isnull(Inventory_Number,0)<>'0'
I was very confused, there was a lot of record that have same amount when I joined those table.

I am not surprised you are confused if that is the specification!

you want to join on tblOrders.Amount = tblInventory.Amount ???

That sounds wrong - can you please explain the business case behind what you are trying to achieve ?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);