Duplicate row excluding primary key

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

General SQL Server Forums

New to SQL Server Programming

Duplicate row excluding primary key

Author

Topic

m_weltm
Starting Member

8 Posts

Posted - 2007-07-06 : 11:37:05

I am trying to duplicate a row in a table and this is the solution I came up with:

CREATE PROC duplicate_row(@my_primary_key)
AS

DECLARE @sql_stmt varchar(MAX)
SET @sql_stmt = 'INSERT INTO My_Table('

SELECT @sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'My_Table'
AND COLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt + 'title) SELECT '

SELECT @sql_stmt = @sql_stmt + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'My_Table'
AND COLUMN_NAME NOT IN ('my_primary_key','title')

SET @sql_stmt = @sql_stmt +' title+''_copy'' AS title FROM My_Table WHERE my_primary_key = '+LTRIM(STR(@my_primary_key))

EXEC(@sql_stmt)

However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.
-thanks

pootle_flump

1064 Posts

Posted - 2007-07-06 : 11:57:19

That won't even compile.

Out of curiosity - why on earth would you want to create an omnitable stored procedure to duplicate records? I presume the PK is an identity field and, as such, there must be no alternate keys in your entire database(?).

EDIT - sorry - it is not for all tables. Which then in turn begs the question why dynamic SQL?

m_weltm
Starting Member

8 Posts

Posted - 2007-07-06 : 12:22:12

I am trying to write it in such a way that if we decide to add columns to the table later, the proc will still function.

pootle_flump

1064 Posts

Posted - 2007-07-06 : 13:46:55

quote:
Originally posted by m_weltm

I am trying to write it in such a way that if we decide to add columns to the table later, the proc will still function.

That will slow it down. Better to code it properly and efficiently and handle changes through QA testing.
Please can you post the real procedure (no problem changing the table name if you like)? This procedure cannot even be created as it has at least one sytax error. There are possible improvements but there is no point improving this if the real one is totally different.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-06 : 13:52:32

quote:
Originally posted by m_weltm

However this stored proc is horrifically slow. I was wondering if anyone had any suggestions on a better way to accomplish this.

quote:
Originally posted by m_weltm

I am trying to write it in such a way that if we decide to add columns to the table later, the proc will still function.

Hmmm... Seems that if performance is the issue, I'd do it without the dynamic SQL and would update the code later when new fields are added.

pootle_flump

1064 Posts

Posted - 2007-07-06 : 13:58:48

I'm shooting off soon so thought I would quickly tidy it up.
1) Not tested (not got SS on this PC)
2) It should actually compile this time
3) Only one scan of information_schema view
4) Execution plan is cached until table's schema is changed.
5) Also the whole premise is flawed since this can only be possible if your only key constraint is on the identity which is a design problem.

CREATE PROC duplicate_row
(
	@my_primary_key AS INT
)
AS

DECLARE @sql_stmt 	AS nvarchar(MAX)
	, @param_def 	AS nvarchar(20)

SELECT 	@sql_stmt = COALESCE(@sql_stmt + ', ', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'My_Table'
AND COLUMN_NAME NOT IN ('my_primary_key','title')

SELECT 	@sql_stmt = N'INSERT INTO Mydb.dbo.My_Table (' + @sql_stmt + ', title) 
		SELECT ' + @sql_stmt + ', title + ''_copy'' AS title 
		FROM Mydb.dbo.My_Table 
		WHERE my_primary_key = @my_primary_key'
	, @param_def = N'@my_primary_key INT'

EXECUTE dbo.sp_executesql @sql_stmt, @param_def, @my_primary_key = @my_primary_key
GO

Some reading:
http://www.sommarskog.se/dynamic_sql.html

EDIT - formatting & point 5.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources