hello,

i have a tabel in which i need to insert the same row based on the number column.

bellow is my example:

do you have any suggestion?

thank you

hello,

i have a tabel in which i need to insert the same row based on the number column.

bellow is my example:

do you have any suggestion?

thank you

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)

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.

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?

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:

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

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

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

Post your code

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.

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;

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
```

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).