SqlHelp45 Posted July 6, 2016 Author Report Posted July 6, 2016 On 6/30/2016 at 10:27 AM, SqlHelp45 said: Thanks guys for all the help. ee SP create chesyina and it works fine when DrugChain table and Test have same number of records for DonorName. 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 Ippudey call lo they asked me to tweak the code a bit. Change endi antha lets say DrugChain table has 10,000 rows and Test table has only 1000 rows. With that 1000 rows DonorName column in Test table should populate values in DrugChain table and the names can repeat. 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 @MINROWID1 <=@MAXROWID1 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 IF @MINROWID = @MAXROWID SET @MINROWID = 0 SET @MINROWID = @MINROWID +1 SET @MINROWID1 = @MINROWID1 + 1 END END Bold lo unna statments add chesthe it's working. Thanks everyone for your help. Quote
mtkr Posted July 6, 2016 Report Posted July 6, 2016 2 minutes ago, SqlHelp45 said: 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 @MINROWID1 <=@MAXROWID1 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 IF @MINROWID = @MAXROWID SET @MINROWID = 0 SET @MINROWID = @MINROWID +1 SET @MINROWID1 = @MINROWID1 + 1 END END Bold lo unna statments add chesthe it's working. Thanks everyone for your help. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.