CODEWITHSUNDEEP
sql-server
tmndungu
tmndungu

Reputation: 358

Duplicate Data from a column in one table to a column in another table multiple times SQL Server

I have data in 2 tables as follows:

Table1
ID Quality
1  Q1
2  Q2
3  Q3

Table2
ID Quality 
1   
2   
3
4
5
6
7
8
9

I would like to update Table2 to be as follows:

Table2
ID  Quality 
1  Q1
2  Q2
3  Q3
4  Q1
5  Q2
6  Q3
7  Q1
8  Q2
9  Q3

I have checked a few answers on SO but seems I'm not getting it right when trying to use Cross join.

Upvotes: 1

Views: 47

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Join using the modulus:

SELECT
    t2.ID,
    t1.Quality
FROM Table1 t1
INNER JOIN Table2 t2
    ON 1 + (t2.ID - 1) % 3 = t1.ID;

Note that we don't need to use NULLIF and ISNULL here.

If the second table might not have continuous IDs, but you still wanted to assign qualities using that general order, then we can try generating with ROW_NUMBER:

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY ID) ID
    FROM Table2
)

SELECT
    t2.ID,
    t1.Quality
FROM Table1 t1
INNER JOIN cte t2
    ON 1 + (t2.ID - 1) % 3 = t1.ID;

Upvotes: 3

Thom A
Thom A

Reputation: 95561

This get's you what you're after:

USE Sandbox;
GO

CREATE TABLE dbo.Table1 (ID int, Quality char(2))
INSERT INTO dbo.Table1 (ID,Quality)
VALUES(1,'Q1'),(2,'Q2'),(3,'Q3');
CREATE TABLE dbo.Table2 (ID int, Quality char(2));
INSERT INTO dbo.Table2 (ID)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9);
GO

UPDATE T2
SET T2.Quality = T1.Quality
FROM dbo.Table1 T1
     JOIN dbo.Table2 T2 ON T1.ID = ISNULL(NULLIF((T2.ID % 3),0),3);

SELECT *
FROM Table2;

GO
DROP TABLE dbo.Table2;
DROP TABLE dbo.Table1;

You need to use Modulus, but also cater for the 0's (as 3 % 3 = 0).

Upvotes: 2

Related Questions