如何复制表SQL Sever 2008中的所有值，并使用唯一标识

I have a table called table123, with about 80 rows. It includes the columns 'ID', 'First_Name', 'Surname', 'Job_Title', 'Salary' and 'Address'.

我有一个名为table123的表，大约有80行。它包括列“ID”、“First_Name”、“姓”、“Job_Title”、“Salary”和“Address”。

I have created a new table called table987, which is an exact copy of table123. This is to not ruin table123 with any commands.

我已经创建了一个名为table987的新表，它是table123的精确副本。这不会破坏table123的任何命令。

Within table987, I want to duplicate all values, so that there will be 2 copies of each row. So, if I have a row with 'John' as 'First_Name', I want the duplicate copy of 'John' to come right after it in the table.

在table987中，我要复制所有的值，这样每一行就会有2个拷贝。所以，如果我有一个以“John”作为“First_Name”的行，我希望“John”的副本在该表的后面。

However, I don't want the duplicate copy to have the same 'ID'. Each row should have a unique 'ID'. Essentially, there should be 2 of each record, but each should have a unique 'ID'.

但是，我不希望复制的副本具有相同的“ID”。每一行都应该有唯一的“ID”。本质上，每个记录应该有2个，但是每个记录应该有一个唯一的“ID”。

I have tried using the following script to duplicate the values, but each duplicate has the same 'ID' as the original record:

我试过使用下面的脚本来复制这些值，但是每个副本都有与原始记录相同的“ID”:

INSERT INTO table987 (ID, First_Name, Surname, Job_Title, Salary, Address)
    SELECT ID, First_Name, Surname, Job_Title, Salary, Address
    FROM table123;

Is there a specific script I could use to sort out the duplicates in such a way?

是否有一个特定的脚本，我可以用它来整理这些重复的东西?

2 个解决方案

#1

Although the answer is already accepted, seeing the comments above ("but now it's just jumbled all over the place"), I want to add the ORDER BY clause to the query, which will give you the ordered data as you required in the mentioned comments above:

虽然已经接受了这个答案，但是看到上面的注释(“但是现在它只是混乱了”)，我想将ORDER BY子句添加到查询中，这将给您在上面提到的注释中所需要的有序数据:

DECLARE @table123 table(ID int, First_Name varchar(100),Surname varchar(100),Job_Title varchar(100),Address varchar(100))
DECLARE @table987 table(ID int, First_Name varchar(100),Surname varchar(100),Job_Title varchar(100),Address varchar(100))
INSERT INTO @table123 
VALUES
    (1,'FName1','Surname1','JobTitle1','Address1'),
    (2,'FName2','Surname2','JobTitle2','Address2'),
    (3,'FName3','Surname3','JobTitle3','Address3'),
    (4,'FName4','Surname4','JobTitle4','Address4'),
    (5,'FName5','Surname5','JobTitle5','Address5')

INSERT INTO @table987
SELECT * FROM (
SELECT * FROM @table123
UNION ALL
SELECT * FROM @table123)result ORDER BY ID

SELECT * FROM @table987;

Output:

输出:

--------------------------------------------------
|ID  First_Name   Surname    Job_Title   Address |
|------------------------------------------------|
|1    FName1      Surname1   JobTitle1   Address1|
|1    FName1      Surname1   JobTitle1   Address1|
|2    FName2      Surname2   JobTitle2   Address2|
|2    FName2      Surname2   JobTitle2   Address2|
|3    FName3      Surname3   JobTitle3   Address3|
|3    FName3      Surname3   JobTitle3   Address3|
|4    FName4      Surname4   JobTitle4   Address4|
|4    FName4      Surname4   JobTitle4   Address4|
|5    FName5      Surname5   JobTitle5   Address5|
|5    FName5      Surname5   JobTitle5   Address5|
-------------------------------------------------- 

#2

Use UNION ALL and set 'ID' as auto increment or set default value NEWID()

使用UNION ALL并将'ID'设置为自动增量或设置默认值NEWID()

INSERT  INTO dbo.table987 
    ( First_Name ,
      Surname ,
      Job_Title ,
      Salary ,
      Address
    )
    SELECT  First_Name ,
            Surname ,
            Job_Title ,
            Salary ,
            Address
    FROM    dbo.table123
    UNION ALL
    SELECT  First_Name ,
            Surname ,
            Job_Title ,
            Salary ,
            Address
    FROM    dbo.table123