Looking at the original code, it appears fine. A test harness works fine too, showing that your theory is sound:
CREATE TABLE #tbl_directdebits (
ID INT IDENTITY(1,1),
ref VARCHAR(2),
recurperiod VARCHAR(2),
servicelevel VARCHAR(2),
duplicateof INT)
INSERT INTO #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) VALUES
('A', 'R', 'S', NULL),
('B', 'R', 'S', NULL),
('C', 'R', 'S', NULL),
('D', 'R', 'S', NULL)
SELECT * FROM #tbl_directdebits
DECLARE @id INT
SET @ID = 3
insert into #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from #tbl_directdebits where id= @id
insert into #tbl_directdebits (ref, recurperiod, servicelevel, duplicateof) select ref, recurperiod,servicelevel, @id from #tbl_directdebits where id= @id
SELECT * FROM #tbl_directdebits
Are you really using SQL Server 2000?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]