使用TSQL，如何从Table1中的现有记录插入Table2中的NEW记录并生成NEWID

Table1 contains Work Orders and Table2 contains Installers. I would like to insert a record into Table2 for Work Order 3906 in Table1. Tables are indexed such that Table1GUID = Table2GUID. Currently Table1GUID is NULL for 3906 because there is no corresponding record in Table2 so I'm guessing that I need to generate NEWID() for Table1GUID and then insert new record in Table2 such that records from both tables have same GUID?

Table1包含工作订单,Table2包含安装程序。我想在Table1中为表1中的工作订单3906插入记录。对表进行索引,使得Table1GUID = Table2GUID。目前Table1GUID对于3906是NULL,因为Table2中没有相应的记录所以我猜我需要为Table1GUID生成NEWID()然后在Table2中插入新记录,以便两个表中的记录具有相同的GUID?

Below is throwing error Msg 547 UPDATE statement conflicted with the FOREIGN KEY constraint. How do I create NEWID in Table1 and Insert into Table2?

下面是抛出错误消息Msg 547 UPDATE语句与FOREIGN KEY约束冲突。如何在Table1中创建NEWID并插入Table2?

UPDATE Document SET Table1GUID = NEWID() WHERE WONumber = '3906'

Which of the 2 below is best for creating record in Table2?

以下2中哪一个最适合在Table2中创建记录?

INSERT INTO Table2 (Table2GUID)
SELECT Table1GUID FROM Table1
WHERE Table1.WOnumber = '3906'

INSERT INTO Table2 (Table2GUID)
SELECT Table1GUID FROM Table1
INNER JOIN Table2 ON Table2GUID = Table1GUID
WHERE Table1.WOnumber = '3906'

1 个解决方案

#1

Since column stores NULL and you don't have corresponding row in Table2 joining has no sense. Try add a filter predicate you already mentioned and replace NULL value with newid():

由于列存储NULL并且在Table2中没有相应的行,因此加入没有任何意义。尝试添加您已经提到的过滤谓词,并用newid()替换NULL值:

set @new_id = newid()

INSERT INTO Table2 (Table2GUID)
SELECT @new_id
FROM Table1 t1
WHERE t1.WOnumber = '3906'
  and t1.Table1GUID is NULL

UPDATE t1 SET
  Table1GUID = @new_id
FROM Table1 t1
WHERE t1.WOnumber = '3906'
  and t1.Table1GUID is NULL

if these changes haveto be multirecord you will need some other approach

如果这些变化必须是多记录,你将需要一些其他的方法

#1