Copy rows in the same table based on a number

Transact-SQL
1

hello,

i have a tabel in which i need to insert the same row based on the number column.
bellow is my example:
image

do you have any suggestion?
thank you

2

something like:

insert into mytable (amid, oppid, number)
select amid, oppid, number from mytable where number = 2 union all
select amid, oppid, number from mytable where number = 2 union all
select amid, oppid, number from mytable where number = 3 union all
select amid, oppid, number from mytable where number = 3 union all
select amid, oppid, number from mytable where number = 3 union all

This assumes that none of these columns is a key, otherwise it won't work, since you can't have dup keys.

May I ask, what is the reason for duplicating rows like this? (It feels wrong to me)

3

thank you for your replay but it doesnt help me much. i just gave an example. my table has thousands of rows, and in the number column i have numbers like 62 or 37.
i know it seems wrong but my stakeholders will use this final table with all those duplicates for another project.

4

ok, how do you receive the numbers to copy? Can you put them in a separate table? And how do you know how many copies of each row you need? Or, is that what the number is for?

Maybe you could post the DDL for the tables involved?

5

the numbers in the last column (number column) i managed to populate based on another table.
i didnt have any other idea except this. thsi is waht i had initially:

image
image.png249×560 4.16 KB

6

so i thought that first i should make another column in my first table and bring the number of times that AMID showed up in the second table and after that to multiply by that number

7

oh, well a simple join on the AMID between table 1 and two should do it then

8

it doesnt work with just a simple join , i tried that

9

Post your code

10

I have a brilliant solution to this problem (brilliant in my opinion, of course, and no dissenting opinions allowed). But I am not able to post it, I get a 503 error. I think that means "James, you have been declared persona-non-grata on this site").

Will try again a little later.

11

You need a numbers table which I called #N, with a single integer column n that starts at 1 and goes up to some value less than the max number in your table. I am not able to post the code for that table - the site crashes on me.

The numbers table starts at 1 and has at least as many rows as maximum value of "number" in your table.
Then,
;WITH cte AS
(
SELECT
AMID,
[OPP id],
number,
COUNT(*) CurrentN
FROM
YourTable
GROUP BY
AMID,
[OPP id],
number
)
INSERT INTO YourTable
SELECT
AMID,
[OPP id],
number
FROM
cte c
CROSS JOIN N a
WHERE
a.n < CurrentN;

12

This is a prime example of why you have to have either a permanent "Tally Table" or "Tally Function" in all your databases.

Here's code for the tally function I'm currently using...

CREATE FUNCTION dbo.tfn_Tally
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows 
				ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
(
	@NumOfRows BIGINT,
	@StartWith BIGINT 
)
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN
	WITH 
		cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),	-- 10 rows
		cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),								-- 100 rows
		cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),								-- 10,000 rows
		cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b),								-- 100,000,000 rows
		cte_Tally (n) AS (
			SELECT TOP (@NumOfRows)
				(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
			FROM 
				cte_n4 a CROSS JOIN cte_n4 b													-- 10,000,000,000,000,000 rows
			)
	SELECT 
		t.n
	FROM 
		cte_Tally t;
GO

Now, with a tally function in place this becomes a trivial exercise...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
AMID CHAR(11) NOT NULL,
OPPid CHAR(14) NOT NULL,
Number INT NOT NULL
);
INSERT #TestData (AMID, OPPid, Number) VALUES
('IEEU9999650', 'OPP-0000660453', 2),
('IEEU9999876', 'OPP-0007731579', 3);

--===============================================

-- The solution query...
SELECT 
	td.AMID,
	td.OPPid,
	td.Number
FROM 
	#TestData td
	CROSS APPLY dbo.tfn_Tally(td.Number, 1) t;

Results...

AMID        OPPid          Number
----------- -------------- -----------
IEEU9999650 OPP-0000660453 2
IEEU9999650 OPP-0000660453 2
IEEU9999876 OPP-0007731579 3
IEEU9999876 OPP-0007731579 3
IEEU9999876 OPP-0007731579 3
13

thank you for you answer but i came up with an ideea yesterday. i admit that i had to improvise a lot but at the end the result was what i wanted.
i have created an additional column , named it [Vlookup] in table 1 which shows what amids are common for both table 1 and 2 and what si not common. i made a left join because i needed to keep also the amids that are not common in table1.

update table 1
set [Vlookup]=[AMID]
from table 1 left join table 2
on table1.[AMID]=table2.[AMID]

After, i created a temporary table in which i multiply the number of rows in table 1 based in the number of rows in table 2 but only for those common amids:

select t1.[AMID], t 1.[MDCP Organization ID], t1.[Opportunity Id]
into #TempTable
from table1 as t1 inner join table2 as t2

and after i created the final table in which i bring the data from #TempTable (has only the common Amids) union the data from table 1 which has in column [Vlookup] is null (those Amids that are not common).