使用 SQL 插入重复记录 [英] inserting duplicate records with SQL

问题描述

我通过搜索 UniqueColID 并将结果插入回表中来复制记录....这样做我有一个重复的条目，这是我想要的，除了我希望一列不重复并且该列是 UniqueColID..

I am duplicating a record by searching UniqueColID and inserting the results back into the table....Doing so I have a duplicate entry which is what I want EXCEPT I want ONE column to not be duplicate and that column is UniqueColID..

下面是我的代码:

CREATE PROCEDURE [dbo].[InsertDuplicateFields] 
    (@UniqueColID varchar(50), @IndividualID varchar(50) = null)
AS
Begin           
    INSERT INTO TableCollisionBegin 
        SELECT * FROM TableCollisionBegin 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableCollisionDetails 
        SELECT * FROM TableCollisionDetails 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableCollisionLocation 
        SELECT * FROM TableCollisionLocation 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableDriver 
        SELECT * FROM TableDriver 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableFollowUp 
        SELECT * FROM TableFollowUp 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableOfficerLogs 
        SELECT * FROM TableOfficerLogs 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TablePolice 
        SELECT * FROM TablePolice 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableRecVerified 
        SELECT * FROM TableRecVerified 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableSignature 
        SELECT * FROM TableSignature 
        WHERE [IndividualID] = @IndividualID;

    INSERT INTO TableTrailer 
        SELECT * FROM TableTrailer 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableValidateLog 
        SELECT * FROM TableValidateLog 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableVehicle 
        SELECT * FROM TableVehicle 
        WHERE [UniqueColID] = @UniqueColID;

    INSERT INTO TableWitness 
        SELECT * FROM TableWitness 
        WHERE [UniqueColID] = @UniqueColID;


End

每个表都有 NUMEROUS 个列，但它们都共享 UniqueColID.

Each table has NUMEROUS columns but they all share UniqueColID.

当我插入副本时，我希望所有详细信息都是重复的，但我想插入一个新的 UniqueColID..我该怎么做?

When I insert the duplicate I want all the details to be duplicate but I want to insert a NEW UniqueColID..How can I do this?

新的 UniqueColID 是由 ASP.NET 代码中的特殊方法生成的.我想要 2 或 3 或 4 个不同的 UniqueColID，其余的列是重复的.

我尝试了您所说的第二个代码块，但出现错误:

I tried the 2nd block of code that you said but got the error:

"Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement."





CREATE PROCEDURE [dbo].[AmendInsertDuplicateFields] (@UniqueColID varchar(50), @NewUniqueColID varchar(50)) 

AS 
Begin CREATE TEMPORARY TABLE Temp AS SELECT * FROM [MVCNew].[dbo].[CollisionBegin] WHERE [UniqueColID] = @UniqueColID; 

UPDATE Temp SET UniqueColID = @NewUniqueColID; 
INSERT INTO [MVCNew].[dbo].[CollisionBegin] SELECT * FROM Temp; 
DROP TEMPORARY TABLE IF EXISTS Temp; 
End

我只是在想......我是否可以插入一个带有一些随机 UniqueColID 值的新记录，但所有其他列都插入了值o"或u"或一些随机文本"然后我将 UniqueColID 的所有列插入到这个新记录中，除了 UniqueColID?这样我就会有一个新的 UniqueColID，其余的列都具有相同的值.

I was just thinking....Is it possible for me to insert a new record with SOME Random UniqueColID value but all other columns are inserted with value "o" or "u" or "some random text" AND THEN I INSERT into this new record all of the columns of UniqueColID except UniqueColID? That way I would have a new UniqueColID with the rest of the columns having the same value.

KCD 我已经尝试了你的代码块，但没有任何工作.. :(

KCD I have tried both your blocks of code and none work.. :(

我个人不明白 SELECT @NewUniqueColID .... 甚至是如何工作的，因为表中没有名为 @NewUniqueColID 的值的列

I personally dont see how SELECT @NewUniqueColID....even works because there is NO column in the table named the value of @NewUniqueColID

推荐答案

无论看起来多么可怕，我都建议使用指定完整列列表的查询.但您不需要自己输入所有字段:使用 SQL Server Management Studio，右键单击对象资源管理器中的表，然后选择菜单项脚本表为|插入到|新建查询窗口".

However horrible it seems, I would recommend using queries that specify the full column list. But you don't need to type in all the fields yourself: using SQL Server Management Studio, right click on the table in Object Explorer and choose the menu item "Script Table as | INSERT To | New Query Window".

这会给你这样的东西:

INSERT INTO [dbo].[TableOfficerLogs]
           ([UniqueColID]
           ,[OtherField1]
           ,[OtherField2] -- etc.
           ,[OtherFieldN]
     VALUES
           (<UniqueColID, int,>
           ,<OtherField1, nvarchar(200),>
           ,<OtherField2, nvarchar(200),> --etc.
           ,<OtherFieldN, nvarchar(200),>)

不要让这吓到您——您仍然不会手动输入所有 VALUES.现在离开该窗口一秒钟，然后返回对象资源管理器.使用同一个表上的上下文菜单，获取另​​一个脚本:脚本表为 | SELECT To | New Query Window".这将是一个完全标准的选择列表，其中列出了您的所有字段.复制整个查询并将其粘贴到第一个查询窗口中的 VALUES 子句上.

Don't let this scare you-- you still won't type all the VALUES manually. Now leave that window alone for a sec and go back to Object Explorer. Use the context menu on the same table, to get another script: "Script Table as | SELECT To | New Query Window". This will be a totally standard select list, with all your fields listed out. Copy the whole query and paste it in over the VALUES clause in your first query window.

这将为您提供一个完整的 INSERT ... SELECT 查询，其中列出了所有字段.现在，只需根据需要替换 SELECT 部分中的 UniqueColID.

This will give you a complete INSERT ... SELECT query with all the fields listed out. Now, just replace UniqueColID in the SELECT portion as appropriate.

此外，如果您事先知道新的 UniqueColID 值，请在 FROM 子句中加入它们(表示为表值参数，也许，如果您在 sproc 中执行所有这些操作):您最终会得到任意数量的副本，每个副本都有您指定的 ID.

In addition, if you know your new UniqueColID values in advance, join to them (represented as a table-valued parameter, perhaps, if you do all this in a sproc) in the FROM clause: you'll end up with as many duplicates as you like, each with the ID you specify.

更新:

这两个Script Table as"操作将为您提供如下所示的内容，并列出所有字段:

What the two "Script Table as" operations will get you is something like this, with all fields listed:

INSERT INTO [dbo].[TableOfficerLogs]
               ([UniqueColID]
               ,[OtherField1]
               ,[OtherField2] -- etc.
               ,[OtherFieldN]
SELECT [UniqueColID]
      ,[OtherField1]
      ,[OtherField2] -- etc.
      ,[OtherFieldN]

现在，要真正进行复制，您需要对其进行修改.您的代码最终可能如下所示:

Now, to actually do the copy, you tinker with it. Your code might end up looking like this:

CREATE PROCEDURE [dbo].[InsertDuplicateFields]        
    (@UniqueColID varchar(50), @NewUniqueColID varchar(50))         
AS    

INSERT INTO [dbo].[TableOfficerLogs]
               ([UniqueColID]
               ,[OtherField1]
               ,[OtherField2] -- etc.
               ,[OtherFieldN]
SELECT @NewUniqueColID
      ,[OtherField1]
      ,[OtherField2] -- etc.
      ,[OtherFieldN]
WHERE UniqueColID = @UniqueColID

这将复制指定的行并用新 ID 替换旧 ID.当然，您会为 8 个表中的每一个重复此过程.

This will duplicate the specified row and substitute the new ID for the old one. Of course, you would repeat this process for each of your 8 tables.

这篇关于使用 SQL 插入重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！