How Can SQL Create Duplicate Records

How can SQL create duplicate records?

You can use a "numbers" table (it's handy for various operations):

CREATE TABLE num
( i UNSIGNED INT NOT NULL
, PRIMARY KEY (i)
) ;

INSERT INTO num (i)
VALUES
  (1), (2), ..., (1000000) ;

Then:

SELECT 
    t.id, t.name
FROM 
        tableX AS t
    JOIN
        num
            ON num.i <= t."count"

Warning: There is of course a limitation on this approach. The query will not produce all the wanted rows, as soon as you have a value in the count column that exceeds the maximum value stored in the Numbers table. If the values in the count column are unbounded, then only an iterative or recursive solution (like the other two answers) can work.

Creating duplicate rows in the same table TSQL

I am not sure what you mean by "can't specify columns". What prevents you from doing so? If it is just the sheer number of columns to type, and iff you are required to list all columns but one, and don't want to type it out, you can use a little known feature in SSMS. Click on the columns folder of the table in question, and drag into a new window. It will give you a comma delimited list of all columns. Remove the ID column, then select that remaining column list into a #temp table.

How To Create Duplicate Records depending on Column which indicates on Repetition

You would need to first generate_rows with increasing row_number and then perform a cross join with your table.

Eg:

create table t(rowid int, name varchar(100),shares_bought int, date_val date)

insert into t
select *
from (values (1,'Dan',2,'2018-08-23')
            ,(2,'Mirko',1,'2018-08-25')
            ,(3,'Shuli',3,'2018-05-14')
            ,(4,'Regina',1,'2018-01-19')
            )t(x,y,z,a)

with generate_data
  as (select top (select max(shares_bought) from t)
             row_number() over(order by (select null)) as rnk /* This would generate rows starting from 1,2,3 etc*/
       from sys.objects a
       cross join sys.objects b
   )
select row_number() over(order by t.rowid) as rowid,t.name,1 as shares_bought,t.date_val
  from t 
  join generate_data gd
    on gd.rnk <=t.shares_bought /* generate rows up and until the number of shares bought*/
 order by 1

Here is a db fiddle link

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=5736255585c3ab2c2964c655bec9e08b

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 to duplicate records, modify and add them to same table

I would suggest just using a view instead of trying to create and maintain two copies of the same data. Then you just select from the view instead of the base table.

create view MyReversedDataView as

select ID
    , Col1
    , Col2
from MyTable

UNION ALL

select ID
    , Col2
    , Col1
from MyTable

How can I create duplicate rows for each row in a table that does not contain a specific value?

you can use cursor for retrieve each row data in select query.

If I have understood correctly your question you can use this script for insert new row for specific condition:

insert into Vendors
SELECT 'new val' col1,'new val' col2, VendorState FROM Vendors
where VendorState <> 'ca'

------------Edit---------------------

if you want to create new table (copy of vendor) you can use this script:

SELECT * into Vendors_Copy FROM Vendors
WHERE VendorState <> 'ca'

How to create duplicate rows based on value in a cell

You can try to use CTE Recursion

WITH CTE(name, num_rows,startnum) AS(
  SELECT name,num_rows,1 startnum
  FROM test_create_rows
  UNION ALL
  SELECT name,num_rows,(startnum + 1) as startnum
  FROM CTE
  WHERE (startnum + 1) <= num_rows
)
SELECT name ,num_rows
FROM CTE
order by name

Results:

|  NAME | NUM_ROWS |
|-------|----------|
| Name1 |        3 |
| Name1 |        3 |
| Name1 |        3 |
| Name2 |        4 |
| Name2 |        4 |
| Name2 |        4 |
| Name2 |        4 |
| Name3 |        1 |

Create duplicate records based on field value Access

Following is query that works for me. It uses hidden table MSysObjects which is virtually certain to have enough records.

SELECT tReOrder.*
FROM tReOrder, 
   (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones 
    FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
WHERE F.Factor<[ReOrder Qty]
ORDER BY Barcode;

---

Related Topics