Copy entire row except ID column

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

Transact-SQL (2000)

Copy entire row except ID column

Author

Topic

mzanaty
Starting Member

8 Posts

Posted - 2003-06-11 : 17:55:46

Any clever ways to duplicate an entire row of a generic table except the identity column (obviously can't be duped since it will auto-inc)?

insert t select * from t where id=@id

...gives an error because it also tries to update the id col. Too bad T-SQL has no "except" clause to exclude fields, because it would be nice to just say "*-id". Any ideas on how to accomplish something like this? (Explicit field list is not an option.)

Thanks in advance...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-11 : 17:58:03

Just don't use *.

INSERT INTO Table1 (Column2, Column3, Colum4)
SELECT Col2, Col3, Col4
FROM Table2
WHERE Col1 = @id

* shouldn't be used in production code anyway, so start explicitly selecting the columns.

Tara

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-06-11 : 18:30:01

quote:

Explicit field list is not an option

Why can't you explicitly list the fields? that is really a best practice (as Tara has pointed out).

To answer your question though, there is no way to have an exclude like you suggested. The only thing that comes to mind is to programatically perform that task using a stored procedure.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-12 : 07:22:36

I take it you don't have a primary key, other wise you couldn't insert t select * from t ... if you are not going to follow the rules, all bets are off.

If you mistyped and you meant insert someothert select * from t, then:


alter table t drop constraint [name of primary key constraint on id]
alter table t drop column id
alter table t add constraint pk primary key ([natural key column(s)])
insert someothert select * from t where naturalkey = @naturalkey

Jay White
{0}

mzanaty
Starting Member

8 Posts

Posted - 2003-06-13 : 12:33:35

Let me clarify the requirement. I need a generic stored proc that clones a specified row in an arbitrary table. It only knows the table name, identity column name (<tablename>Id), and identity value to clone, but has no knowledge of other columns. It clones the specified row and returns the newly inserted identity value.

I have solutions using sysdb or temp tables, but hoped for a more elegant approach. For example, the temp table hack:

select * into #t from t where tid=@tid
alter table #t drop column tid
insert t select * from #t
select @@identity

Note: The real stored proc uses dynamic sql due to the unknown table name, but I ommitted this for clarity. It also requires using a second temp table because the drop column on the first temp table never fully drops it. No clue why. Again, omitted for clarity. So the real thing goes more like:

CREATE PROCEDURE dbo.sp_CloneRow
(
@TableName varchar(100),
@IdColumnName varchar(100),
@IdColumnValue int
)

AS

set nocount on
exec
(
' select * into #t from ' + @TableName + ' where ' + @IdColumnName + '=' + @IdColumnValue +
' alter table #t drop column ' + @IdColumnName +
' select * into #tt from #t ' +
' insert ' + @TableName + ' select * from #tt'
)
select @@identity as Id
GO

There must be a cleaner way, right?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-13 : 19:49:53

quote:
There must be a cleaner way, right?

No, there MUSTN'T be a cleaner way, and with the "requirements" you've posted you're not likely to do better than what you have now.

Why does it need to be so generic? Even if you had 20 tables, you'd be better off writing explicit code to alter the right table based on the value being passed, instead of constructing dynamic SQL to do it. You'll get better performance in all likelihood, certainly not worse (you can do the INSERT in one operation without needing the temp table) If you are going to say "That won't work, my table design is constantly changing" then either nail down your table designs first and write the code properly, or don't expect a cleaner way to do a permanently dirty job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources