duplicating rows with new key + twist

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 rows with new key + twist

Author

Topic

sky
Starting Member

10 Posts

Posted - 2002-01-30 : 09:06:03

Hi

I want to write a stored procedure that will duplicate a row in a table, only replacing the unique key.

Only the twist is that the table may gain extra columns over time and I don't want this to break the query.

so its got to be dynamic... any takers...

If your swimming upstream, your going the wrong way.

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-30 : 09:34:41

Well I guess you could pass the procedure an array with the column names and the column data in it, and then looop through that. Try this article -

http://www.sqlteam.com/item.asp?ItemID=2652

or

http://www.sqlteam.com/item.asp?ItemID=637

Also, why do you want to duplicate an entire row?

Jack

sky
Starting Member

10 Posts

Posted - 2002-01-30 : 10:01:16

I think there is an easier way...

I need to combine a query like this
INSERT INTO tTempTest
(col1, col2, col3)
SELECT col1, col2, col3
FROM tTempTest
WHERE id = 2

only I want to make the selection dynamic...

I could do this using a select like the following which returns column names

select * from INFORMATION_SCHEMA.tables where table_name = 'tTempTest'"

But how do I combine the two into one stored procedure, How do I use the results from the first select in the insert?
And how do I ignore the id column?

This is as far as I've got...

BTW. I want to duplicate the row, because it is stuff like referral details for a shopping basket, after someone has made a purchase I need a new basket id, which is the key on this table, but I want to keep the old row and keep referral details on the new basket... messy I know but I haven't got time to redesign an entire database...

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

Edited by - sky on 01/30/2002 10:03:43

sky
Starting Member

10 Posts

Posted - 2002-01-30 : 12:40:29

ok this is what i've got... anyone know how to make the bits in brackets work? perhaps by putting the select statement in a nested procedure?

CREATE PROCEDURE spTempTest AS

Select COLUMN_NAME
From INFORMATION_SCHEMA.Columns
Where TABLE_NAME = 'tTempTest'

Declare @columnText nvarchar(1000)
<above select stement.eof=false.movenext>
while <above select stement.eof=false>
Set @columnText=@columnText + <above select stement("COLUMN_NAME")> + ','
<above select stement.eof=false.movenext>
next
Set @columnText=left(@columnText,len(@columnText)-1)

Declare @sqlText nvarchar(2000)
Set @sqlText ='Insert into tTempTest ('
Set @sqlText=@sqlText + @columnText
Set @sqlText=@sqlText +') Select '
Set @sqlText=@sqlText + @columnText
Set @sqlText=@sqlText +' From tTempTest Where id=2'
exec(@sqlText)

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

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-31 : 05:22:01

OK, what about this;

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

--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 = 'tTempTest'

SET @columnText = ''

WHILE @intCounter <= @colCount

BEGIN

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

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

SET @intCounter = @intCounter + 1

END

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

SET @sqlText = 'INSERT INTO tTempTest SELECT ' + @columnText + ' FROM tTempTest WHERE id = 2'

EXEC (@sqlText)

Jack

..just found this article as well which might help -
http://www.sqlteam.com/item.asp?ItemID=2971

Edited by - jackstow on 01/31/2002 05:28:59

sky
Starting Member

10 Posts

Posted - 2002-02-04 : 04:46:59

Thanks Jack,

That works a treat... and I understand what its doing,

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