时间戳

首页 > 解决方案 > SQL Server, updating item quantities of new items that are replacing old items

sql - SQL Server, updating item quantities of new items that are replacing old items

问题描述

I have a CSV with two columns OldItem and NewItem; each column holds a list of integers. Note - the CSV will hold around 1,000 rows.

OldItem | NewItem
-----------------
1021669 | 1167467
1021680 | 1167468
1021712 | 1167466
1049043 | 1000062

We have old items in the system that are being replaced by the new items and we would like to capture the current quantity of the first OldItem and assign it to the first NewItem, quantity of second OldItem assigned to quantity of third OldItem, etc.

The other fun part of the issue is that the Item Numbers that are in the spreadsheet don't match up to the item numbers associated with the quantities, there's a translation table in the system called Alias.

Here are the tables and columns we're interacting with: table Alias (essentially a translation table) column Alias (the numbers in the spreadsheet) column ItemID (the numbers in table "Items" that hold the quantities)

table Items (this holds all the items, new and old) column ItemID column Quantity

The only way I can think of doing this is doing a foreach on every OldItem like this, pseudo-code incoming: foreach OldItem (Select Alias.ItemID WHERE Alias.Alias = OldItem) then somehow, as I don't know how to return and use that result in SQL: Select Item.Quantity where Item.ItemID = Alias.ItemID. At this point I have the quantity that I want, now I have to reference back to the CSV, find the NewItem associated with the OldItem, and do this all over again with the NewItem and then update the NewItem Quantity to the one I found from the OldItem.

-dizzy-

Please help, I could solve this problem by wrapping SQL in PowerShell to handle the logical bits but it has severe performance consequences and I have to do this on MANY databases remotely with very bad network connections!

标签： sqlsql-server

解决方案

鉴于您有连接问题，我建议如下：

  1. 在数据库中创建工作表
  2. 将您的 CSV 导入工作表
  3. 运行将别名和数量复制到工作表中的脚本。不是必需的，但有助于审计
  4. 运行验证数据的脚本
  5. 运行将所需数据复制到的脚本Items

重要的是要注意，这假设旧项目是唯一的，并且只映射到一个新项目。在“测试部分”中有一个检查

创建工作表

打开 SQL Server Management Studio 并在您的数据库中运行此脚本（在下拉列表中选择它）

 -- Create a schema to hold working tables that aren't required by the application
 CREATE SCHEMA adm;

-- Now create a table in this schema
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ItemTransfer' 
           AND type = 'U' 
           AND schema_id = SCHEMA_ID('adm'))
    DROP TABLE adm.ItemTransfer;

CREATE TABLE adm.ItemTransfer (
    OldItem INT NOT NULL, 
    NewItem INT NOT NULL,
    OldAlias VARCHAR(50) NULL,
    NewAlias VARCHAR(50) NULL,
    OldQuantity NUMERIC(19,2) NULL
   );

导入 CSV 数据

有很多方法可以做到这一点。您的限制是您不可靠的网络，以及您对不熟悉的工具进行故障排除的舒适程度。这是一种可以重新运行而不会导致重复的方法：

在 excel 中打开您的 CSV 并将这个怪物粘贴到第 3 列第 2 行中：

="INSERT INTO adm.ItemTransfer (OldItem, NewItem) SELECT " & A2 & "," & B2 & " WHERE NOT EXISTS (SELECT * FROM adm.ItemTransfer WHERE OldItem=" & A2 & " AND NewItem=" & B2 & ");"

这将为该数据生成一个插入语句。将其向下拖动以生成所有插入语句。会有一堆看起来像这样的行：

INSERT INTO adm.ItemTransfer (OldItem, NewItem) SELECT 1,2 WHERE NOT EXISTS (SELECT * FROM adm.ItemTransfer WHERE OldItem=1 AND NewItem=2);

将此插入字符串复制/粘贴到 SQL Server Management Studio 中并运行它。它应该将所有数据插入到您的工作表中。

我还建议您将此文件保存为 .SQL 文件。此插入语句仅在记录不存在时插入，因此可以重新运行。

注意：有多种方法可以将数据导入 SQL Server。下一个最简单的方法是右键单击数据库/任务/导入平面文件，但停止重复/重新启动导入更复杂

现在您可以运行SELECT * FROM adm.ItemTransfer，您应该会看到所有记录

地图别名和数量

这一步实际上可以即时完成，但我们只需将它们写入工作表，因为它允许我们事后进行审计

这两个脚本将别名复制到工作表中：

UPDATE adm.ItemTransfer
SET OldAlias = SRC.Alias
FROM 
adm.ItemTransfer TGT
INNER JOIN
Alias SRC
ON TGT.OldItem = SRC.ItemID;


UPDATE adm.ItemTransfer
SET NewAlias = SRC.Alias
FROM 
adm.ItemTransfer TGT
INNER JOIN
Alias SRC
ON TGT.NewItem = SRC.ItemID;

这一件复制旧货数量

UPDATE adm.ItemTransfer
SET OldQuantity = SRC.Quantity
FROM 
adm.ItemTransfer TGT
INNER JOIN
Items SRC
ON TGT.OldAlias = SRC.ItemID;

在这些步骤之后，再次运行 select 语句进行检查。

更新前检查

在实际进行更新之前，您应该检查数据一致性

暂存表中的记录数：

SELECT 
COUNT(*) AS TableCount,
COUNT(DISTINCT OldAlias) UniqueOldAlias,
COUNT(DISTINCT NewAlias) UniqueNewAlias,
FROM adm.ItemTransfer

这些数字都应该相同，并且应该与 CSV 记录计数相匹配。如果不是，您会遇到问题，因为您缺少记录或者您没有一对一映射

此选择向您显示缺少别名的旧项目：

SELECT * FROM adm.ItemTransfer WHERE OldAlias IS NULL

此选择向您显示缺少别名的新项目：

SELECT * FROM adm.ItemTransfer WHERE NewAlias IS NULL

此选择显示项目表中缺少的旧项目

SELECT * 
FROM adm.ItemTransfer T
WHERE NOT EXISTS (
   SELECT * FROM Items I WHERE I.ItemID = T.OldItem)

此选择显示项目表中缺少的新项目

SELECT * 
FROM adm.ItemTransfer T
WHERE NOT EXISTS (
   SELECT * FROM Items I WHERE I.ItemID = T.NewItem)

备份表并进行更新

首先像这样备份数据库中的表：

SELECT *
INTO adm.Items_<dateandtime>
FROM Items

此脚本会在您更新 Items 表之前对其进行复制。如果你喜欢，你可以稍后删除它

实际的更新非常简单，因为我们事先在工作表中解决了所有问题：

UPDATE Items SET Quantity = SRC.OldQuantity FROM Items TGT INNER JOIN adm.ItemTransfer SRC ON SRC.NewAlias = TGT.ItemID;

概括

所有这些都可以捆绑到一个脚本中，并在需要时自动执行。照原样，您应该将所有工作文件以及 SELECT 测试语句的输出保存到 SQL 文件中

推荐阅读