How to Better Duplicate a Set of Data in SQL Server

How to better duplicate a set of data in SQL Server

Why dont you join on the FruitName to get a table with old and new FruitId's? Considering information would be added at the same time.... it may not be the best option but you wont be using any cycles.

INSERT INTO BASKET(BASKETNAME)
VALUES ('COPY BASKET')

DECLARE @iBasketId int
SET @iBasketId = @@SCOPE_IDENTITY;

insert into Fruit (BasketId, FruitName)
select @iBasketId, FruitName
from Fruit 
where BasketId = @originalBasket

declare @tabFruit table (originalFruitId int, newFruitId int)

insert into @tabFruit (originalFruitId, newFruitId)
select o.FruitId, n.FruitId
from (SELECT FruitId, FruitName from Fruit where BasketId = @originalBasket) as o
join (SELECT FruitId, FruitName from Fruit where BasketId = @newBasket) as n
    on o.FruitName = n.FruitName

insert into Property (FruitId, PropertyText)
select NewFruitId, PropertyText
from Fruit f join @tabFruit t on t.originalFruitId = f.FruitId

SQL Server Set with duplicate records

Use row_number with customized ordering. This query will return one row per unique combination of identifiers in the partition clause. When more than one row exists, it will choose the lowest BusinessType.

; with CTE as (
    select *
    , row_number() over (partition by ContractNumber, Person Type 
                         order by BusinessType) as RN
    from MyTable)

Select * from CTE where RN = 1

If you are not comfortable with CTEs, you can apply the same logic with a temp table or subquery instead.

How Do I Deep Copy a Set of Data, and Change FK References to Point to All the Copies?

Here is an example with three tables that can probably get you started.

DB schema

CREATE TABLE users
    (user_id int auto_increment PRIMARY KEY, 
     user_name varchar(32));
CREATE TABLE agenda
    (agenda_id int auto_increment PRIMARY KEY, 
     `user_id` int, `agenda_name` varchar(7));
CREATE TABLE events
    (event_id int auto_increment PRIMARY KEY, 
     `agenda_id` int, 
     `event_name` varchar(8));

An SP to clone a user with his agenda and events records

DELIMITER $$
CREATE PROCEDURE clone_user(IN uid INT)
BEGIN
    DECLARE last_user_id INT DEFAULT 0;

    INSERT INTO users (user_name)
    SELECT user_name
      FROM users
     WHERE user_id = uid;

    SET last_user_id = LAST_INSERT_ID();

    INSERT INTO agenda (user_id, agenda_name)
    SELECT last_user_id, agenda_name
      FROM agenda
     WHERE user_id = uid;

    INSERT INTO events (agenda_id, event_name)
    SELECT a3.agenda_id_new, e.event_name
      FROM events e JOIN
    (SELECT a1.agenda_id agenda_id_old, 
           a2.agenda_id agenda_id_new
      FROM
    (SELECT agenda_id, @n := @n + 1 n 
       FROM agenda, (SELECT @n := 0) n 
      WHERE user_id = uid 
      ORDER BY agenda_id) a1 JOIN
    (SELECT agenda_id, @m := @m + 1 m 
       FROM agenda, (SELECT @m := 0) m 
      WHERE user_id = last_user_id 
      ORDER BY agenda_id) a2 ON a1.n = a2.m) a3 
         ON e.agenda_id = a3.agenda_id_old;
END$$
DELIMITER ;

To clone a user

CALL clone_user(3);

Here is SQLFiddle demo.

Update one of 2 duplicates in an sql server database table

Try This with CTE and PARTITION BY

;WITH cte AS
(
  SELECT
      ROW_NUMBER() OVER(PARTITION BY Column1  ORDER BY Column1 ) AS rno,
      Column1 
  FROM Clients
)

UPDATE cte SET Column1 =Column1 +' 1 '
WHERE rno=2

Select subset of duplicate records in SQL Server

You could use ROW_NUMBER:

WITH cte AS (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY LName,Fname,DateOfBirth,StreetAddress 
                            ORDER BY ID DESC) rn
 FROM #Dataset
)
SELECT *
FROM cte 
WHERE rn > 1
ORDER BY ID;

db<>fiddle demo

EDIT:

WITH cte AS (
SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY LName, Fname, DateOfBirth, StreetAddress 
                    ORDER BY ID DESC) rn,
  SUM(CASE WHEN Source = 'Company XYZ' THEN 1 ELSE 0 END) 
               OVER(PARTITION BY LName, Fname, DateOfBirth, StreetAddress) AS cnt
FROM #Dataset
)
SELECT *
FROM cte 
WHERE rn > 1
  AND cnt > 0
  AND [Source] IS NULL
ORDER BY ID;

db<>fiddle demo2

EDIT 2:

WITH cte AS (
SELECT *, 
  SUM(CASE WHEN Source IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY LName, Fname, DateOfBirth, StreetAddress) c1,
  SUM(CASE WHEN Source = 'Company XYZ' THEN 1 ELSE 0 END) OVER(PARTITION BY LName, Fname, DateOfBirth, StreetAddress) AS c2,
  COUNT(*) OVER(PARTITION BY LName, Fname, DateOfBirth, StreetAddress) c3
FROM #Dataset
)
SELECT *
FROM cte 
WHERE c1 > 0
  AND c2 > 0
  AND c3 > 1
  AND Source IS NULL
ORDER BY ID;

db<>fiddle demo3

How to duplicate all records in a table SQL Server for n times?

jarlh and Larnu have given the solution in the request comments, but you are having difficulties understanding the concept.

The duplicates you are talking about are not real duplicates obviously. They differ in their IDs. This means you must list the columns and omit the ID: insert into t (col1, col2) select col1, col2 from t.

With an ad-hoc tally table:

insert into t (col1, col2)
  select col1, col2
  from t
  cross join (values (1),(2),(3),(4),(5)) tally(i);

How do you copy a record in a SQL table but swap out the unique id of the new row?

Try this:

insert into MyTable(field1, field2, id_backup)
    select field1, field2, uniqueId from MyTable where uniqueId = @Id;

Any fields not specified should receive their default value (which is usually NULL when not defined).

Avoid duplicates in INSERT INTO SELECT query in SQL Server

Using NOT EXISTS:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS(SELECT id
                    FROM TABLE_2 t2
                   WHERE t2.id = t1.id)

Using NOT IN:

INSERT INTO TABLE_2
  (id, name)
SELECT t1.id,
       t1.name
  FROM TABLE_1 t1
 WHERE t1.id NOT IN (SELECT id
                       FROM TABLE_2)

Using LEFT JOIN/IS NULL:

INSERT INTO TABLE_2
  (id, name)
   SELECT t1.id,
          t1.name
     FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.id = t1.id
    WHERE t2.id IS NULL

Of the three options, the LEFT JOIN/IS NULL is less efficient. See this link for more details.

Create Duplicate SQL Database for Testing

use copy database option in SQL server management studio

---

Related Topics