Insert duplicate rows based on number in the column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Insert duplicate rows based on number in the...

Insert duplicate rows based on number in the column

Rate Topic

Display Mode

Topic Options

Author

Message

knakka 14475

Posted Thursday, March 21, 2013 9:42 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

First table (@OrigTab) has Parts and quantity, now we need copy each part record basing on quanity show in @DesireTable

Declare @OrigTab Table(Part varchar(30), qty int)
insert into @OrigTab
select 'X51','3' union
select 'A5','1' union
select '123','2' union
select '054','5'
select * from @OrigTab ORDER BY 1 DESC

/*Basing on the number of quantity duplicate rows need to be created with a new id column(tag)in the below format */
Declare @DesireTable TABLE(TAG VARCHAR(13),Part varchar(30), qty int)
insert into @DesireTable
select 'PNM-01','X51','3' union
select 'PNM-02','X51','3' union
select 'PNM-03','X51','3' union
select 'PNM-04','A5','1' union
select 'PNM-05','123','2' union
select 'PNM-06','123','2' union
select 'PNM-07','054','4'union
select 'PNM-08','054','4'union
select 'PNM-09','054','4'union
select 'PNM-10','054','4'union
select 'PNM-11','054','4'
select * from @DesireTable

Post #1433874

ChrisM@Work

Posted Thursday, March 21, 2013 10:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

Declare @OrigTab Table(Part varchar(30), qty int)
insert into @OrigTab
select 'X51','3' union
select 'A5','1' union
select '123','2' union
select '054','5'
select * from @OrigTab ORDER BY 1 DESC

;WITH
-- Make an inline tally table (CTE) 
	E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    Tally(N) AS (SELECT 1 FROM E2 a, E2 b)
    
-- use the tally table as a source of rows 
SELECT
	TAG = 'PNM-'+RIGHT('0'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),2),
	o.Part, 
	o.qty
FROM @OrigTab o
CROSS APPLY (SELECT TOP(o.qty) N FROM Tally) x
ORDER BY Part DESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1433886

knakka 14475

Posted Thursday, March 21, 2013 10:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 13, 2013 1:35 PM
Points: 38, Visits: 83

Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

Post #1433897

ChrisM@Work

Posted Thursday, March 21, 2013 10:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

knakka 14475 (3/21/2013)

Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

;WITH
-- Make an inline tally table (CTE) 
	E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    Tally(N) AS (SELECT 1 FROM E2 a, E2 b)
SELECT COUNT(*) FROM Tally

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1433898

Lynn Pettis

Posted Thursday, March 21, 2013 10:40 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 21,609, Visits: 27,439

knakka 14475 (3/21/2013)

Hi ChrisM@Work thank you for the reply.. I have one question in case they are more than 100 quanties what do I need to do.

Adjust this piece of code to append more zeros.

TAG = 'PNM-'+RIGHT('0'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),2)

For instance:

TAG = 'PNM-'+RIGHT('00000000'+CAST(ROW_NUMBER() OVER(ORDER BY Part DESC) AS VARCHAR(2)),3)

will provide '001' through '999'.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1433905

« Prev Topic | Next Topic »

Permissions