Populate Data -- Loop

Question

Post reply

Populate Data -- Loop

Cawie

SSC-Addicted

Points: 445
More actions
June 30, 2016 at 10:51 am

#327498

z

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63594 More actions · Answer 1

I have a task where we need to populate data from one table to another one.

What's wrong with the INSERT INTO without all the variables?

INSERT INTO DestTable(col1, col2, col3)

SELECT [DISTINCT] colA, colB, colC

FROM SrcTable

WHERE... [NOT EXISTS....?]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

This is equivalent to your code. Both are not guaranteed to get the same results each time as there's nothing to order. Pietlinden gave you the other part of the solution, but you still need to find out how to fix it to make the query deterministic.

WITH cteDrugChain AS(

SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM DrugChain_1

),cteTEST AS(

SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM TEST

)

UPDATE dc SET

DonorName = t.DonorName

FROM cteDrugChain dc

JOIN cteTEST t ON dc.rn = t.rn;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

durga.palepu Hall of Fame Points: 3530 More actions · Answer 3

I hope you can use tally/number table to load the data into TableA from TableB.

CREATE TABLE #tblNums(n INT PRIMARY KEY)

DECLARE @cnt =1;

WHILE (@cnt <=100)

BEGIN

INSERT INTO #tblNums(n)

VALUES(@cnt)

SET @cnt = @cnt+1

END

;with CTE

AS

(

SELECT DonorName, ROW_NUMBER() OVER(ORDER BY ID) AS RowNum

FROM TableB b

CROSS JOIN #tblNums t1

WHERE t1.n <=10

)

UPDATE a

SET a.DonorName = cte.DonorName

WHERE a.ID = cte.RowNum

Hope this helps.

Cawie SSC-Addicted Points: 445 More actions · Answer 4

Luis Cazares (6/30/2016)
This is equivalent to your code. Both are not guaranteed to get the same results each time as there's nothing to order. Pietlinden gave you the other part of the solution, but you still need to find out how to fix it to make the query deterministic.
WITH cteDrugChain AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM DrugChain_1
),cteTEST AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM TEST
)
UPDATE dc SET
DonorName = t.DonorName
FROM cteDrugChain dc
JOIN cteTEST t ON dc.rn = t.rn;

Is there a way to tweak this query and repeat values for DonorName. I mean in Test we have 10,000 rows and in DrugChain_ we have 100k records. I am okay if the rows get repeat any no of times.

My Code: DECLARE @TEMP TABLE( rowId INT IDENTITY(1, 1) , DonorName VARCHAR(40))

DECLARE @TEMP1 TABLE ( rowId INT IDENTITY(1, 1) , ChainID INT)

DECLARE @MINROWID INT

DECLARE @MAXROWID INT

DECLARE @MINROWID1 INT

DECLARE @MAXROWID1 INT

DECLARE @DonorName VARCHAR(40)

DECLARE @ChainID INT

INSERT @TEMP( DonorName)

SELECT DonorName FROM TEST

INSERT INTO @TEMP1 (ChainID)

SELECT ChainID FROM DrugChain_1

SELECT @MINROWID = MIN(ROWID), @MAXROWID = MAX(ROWID) FROM @TEMP

SELECT @MINROWID1 = MIN(ROWID), @MAXROWID1 = MAX(ROWID) FROM @TEMP1

SELECT * FROM @TEMP

SELECT * FROM @TEMP1

WHILE @MINROWID <=@MAXROWID

BEGIN

SELECT @DonorName = DonorName FROM @TEMP WHERE ROWID = @MINROWID

SELECT @ChainID = ChainID FROM @TEMP1 WHERE ROWID = @MINROWID1

UPDATE DrugChain_1 SET DonorName = @DonorName WHERE ChainID = @ChainID

SET @MINROWID = @MINROWID +1

SET @MINROWID1 = @MINROWID1 + 1

END

Again thanks for all the help.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

Cawie (7/5/2016)
Luis Cazares (6/30/2016)
This is equivalent to your code. Both are not guaranteed to get the same results each time as there's nothing to order. Pietlinden gave you the other part of the solution, but you still need to find out how to fix it to make the query deterministic.
WITH cteDrugChain AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM DrugChain_1
),cteTEST AS(
SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn FROM TEST
)
UPDATE dc SET
DonorName = t.DonorName
FROM cteDrugChain dc
JOIN cteTEST t ON dc.rn = t.rn;
Is there a way to tweak this query and repeat values for DonorName. I mean in Test we have 10,000 rows and in DrugChain_ we have 100k records. I am okay if the rows get repeat any no of times.
My Code: DECLARE @TEMP TABLE( rowId INT IDENTITY(1, 1) , DonorName VARCHAR(40))
DECLARE @TEMP1 TABLE ( rowId INT IDENTITY(1, 1) , ChainID INT)
DECLARE @MINROWID INT
DECLARE @MAXROWID INT
DECLARE @MINROWID1 INT
DECLARE @MAXROWID1 INT
DECLARE @DonorName VARCHAR(40)
DECLARE @ChainID INT
INSERT @TEMP( DonorName)
SELECT DonorName FROM TEST
INSERT INTO @TEMP1 (ChainID)
SELECT ChainID FROM DrugChain_1
SELECT @MINROWID = MIN(ROWID), @MAXROWID = MAX(ROWID) FROM @TEMP
SELECT @MINROWID1 = MIN(ROWID), @MAXROWID1 = MAX(ROWID) FROM @TEMP1
SELECT * FROM @TEMP
SELECT * FROM @TEMP1
WHILE @MINROWID <=@MAXROWID
BEGIN
SELECT @DonorName = DonorName FROM @TEMP WHERE ROWID = @MINROWID
SELECT @ChainID = ChainID FROM @TEMP1 WHERE ROWID = @MINROWID1
UPDATE DrugChain_1 SET DonorName = @DonorName WHERE ChainID = @ChainID
SET @MINROWID = @MINROWID +1
SET @MINROWID1 = @MINROWID1 + 1
END
END
Again thanks for all the help.

You erased your original post (incredibly rude, by the way). As a result, I have no idea what your question actually is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)