duplicating a row with a new id

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

duplicating a row with a new id

Author

Topic

sky
Starting Member

10 Posts

Posted - 2002-04-15 : 10:10:57

Hi

A few months ago I asked how to duplicate a row in a table while changing the ID.
We worked out a solution... which worked great until now.
Theoreticaly the solution should not be effected by columns being added or removed from the table, however I just tried removing a column and it is falling over. It is as if it is still trying to copy the data into the old table...Perhaps somthing needs refreshing?
Here is the stored procedure that does the work. Anyone any ideas?

CREATE PROCEDURE spInsertNewBasket
@thisid nvarchar (50)
AS

DECLARE @intCounter int
DECLARE @colCount int
DECLARE @sqlText nvarchar(4000)
DECLARE @columnText nvarchar(4000)
DECLARE @columnName nvarchar(4000)

--this presumes atuo incrementing primary key is first column in the table
SET @intCounter = 2

Select @colCount = count(COLUMN_NAME) From INFORMATION_SCHEMA.Columns Where TABLE_NAME = 'tBaskets'

SET @columnText = ''

WHILE @intCounter <= @colCount

BEGIN

Select @columnName = COLUMN_NAME From INFORMATION_SCHEMA.Columns Where ORDINAL_POSITION = @intCounter AND TABLE_NAME = 'tBaskets'

SET @columnText = @columnText + @columnName + ','

SET @intCounter = @intCounter + 1

END

Set @columnText=left(@columnText,len(@columnText)-1)

SET @sqlText = 'INSERT INTO tBaskets SELECT ' + @columnText + ' FROM tBaskets WHERE id =' + @thisid

EXEC (@sqlText)

update tBaskets set datecreated=GETDATE() where id=@@identity

select id from tBaskets where id=@@identity

GO

--------------------------------
If your swimming upstream, your going the wrong way.

motokevin
Starting Member

36 Posts

Posted - 2002-04-15 : 19:46:57

I changed a few things:

-I changed the format so that sp_UpdateProcedure_InsertNewBasket creates the procedure spInsertNewBasket.
-I got rid of the line where you remove the trailing comma (you'll see why).
-I added code in the loop to skip over the column 'datecreated' and still increment the counter.
-I added code to put the column names after the Insert Into (col1,col2,etc).
-I changed the select @@Identity statement.

I think you'll like this a little better. All you have to do is run the outer procedure (sp_UpdateProcedure_InsertNewBasket) any time the tBaskets table changes (adding or removing columns). This will drop and recreate an updated version of spInsertNewBasket.

CREATE PROCEDURE sp_UpdateProcedure_InsertNewBasket

AS

DECLARE @intCounter int
DECLARE @colCount int
DECLARE @sqlText nvarchar(4000)
DECLARE @columnText nvarchar(4000)
DECLARE @columnName nvarchar(4000)

--this presumes atuo incrementing primary key is first column in the table
SET @intCounter = 2

Select @colCount = count(COLUMN_NAME) From INFORMATION_SCHEMA.Columns Where TABLE_NAME = 'tBaskets'

SET @columnText = ''

WHILE @intCounter <= @colCount

BEGIN

Select @columnName = COLUMN_NAME From INFORMATION_SCHEMA.Columns Where ORDINAL_POSITION = @intCounter AND TABLE_NAME = 'tBaskets'

IF Not @columnName Like 'datecreated'
SET @columnText = @columnText + @columnName + ','

SET @intCounter = @intCounter + 1

END

-- Unnecessary
-- Set @columnText=left(@columnText,len(@columnText)-1)

SET @sqlText = '
CREATE PROCEDURE spInsertNewBasket
@thisid nvarchar (50)
AS
INSERT INTO tBaskets (' + @columnText + 'datecreated)
SELECT ' + @columnText + 'GETDATE()
FROM tBaskets
WHERE id = @thisid

Select @@identity
Go
'

Drop Procedure spInsertNewBasket
EXEC (@sqlText)

-- Unnecessary
-- update tBaskets set datecreated=GETDATE() where id=@@identity

--select id from tBaskets where id=@@identity

GO

sky
Starting Member

10 Posts

Posted - 2002-04-16 : 04:46:49

Thanks

That works, although I had to put the

select id from tBaskets where id=@@identity

back into it, becuase the asp needs it as 'id' and using AS id seemed to cause problems.

I stll don't understand why the procedure needs recreating, it should be taking the column information from INFORMATION_SCHEMA as soon as the table is updated.
I can only asume that the schema is cached.

Cheers

--------------------------------
If your swimming upstream, your going the wrong way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources