Duplicating a record within a table

Question

Duplicating a record within a table

patrick 98158

SSC Rookie

Points: 31
More actions
March 11, 2010 at 8:09 pm

#96634

Hello All,
I have a Table1 with 10 fields currently.
Field#1 is a unique key.
I have a UI that prompts the user for
a. an existing Field#1 (from unique key)
b. duplicate to new value of Field#1 (to unique key)
Is there a way to write a stored procedure that receives the 2 values above to perform the duplication?
The stored procedure need to be 'dynamic' in the sense that when Table1 grows to 15 fields, the stored procedure can still work without modifications.
Any help will be appreciated.
py

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

patrick 98158 (3/11/2010)
Hello All,
I have a Table1 with 10 fields currently.
Field#1 is a unique key.
I have a UI that prompts the user for
a. an existing Field#1 (from unique key)
b. duplicate to new value of Field#1 (to unique key)
Is there a way to write a stored procedure that receives the 2 values above to perform the duplication?
The stored procedure need to be 'dynamic' in the sense that when Table1 grows to 15 fields, the stored procedure can still work without modifications.
Any help will be appreciated.
py

I don't believe that, taking new columns being added into consideration, that this can be done without the use of dynamic SQL.

My question would be, why will columns be added to the table? I ask because there may be a workaround but I need to know the reason for the addition of columns and what the columns would be used for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

patrick 98158 SSC Rookie Points: 31 More actions · Answer 2

Thanks Jeff.

Assuming Table1 is an Item Master with 10 fields of which Item Code is a unique key.

Right now, the UI allowed the user to enter all 10 fields.

There is now a need to introduce a new field Item Category.

This new field is now included into Table1 and the UI.

On the UI, I want to develop a function that duplicates an existing Item Code to a new Item Code that is entered by the user.

I will call a stored procedure that INSERT INTO Table1 by selecting an existing record.

My intention is not to change the stored procedure, by having to explicitly code Item Category into it.

I new to this, so any advice will be much appreciated.

-

Paul White SSC Guru Points: 150468 More actions · Answer 3

Demo setup:

-- For demonstration purposes

USE tempdb;

GO

-- Drop test table and procedure if it exists

IF OBJECT_ID(N'dbo.DuplicateItem', N'P')

IS NOT NULL

DROP PROCEDURE dbo.DuplicateItem;

IF OBJECT_ID(N'dbo.ItemMaster', N'U')

IS NOT NULL

DROP TABLE dbo.ItemMaster;

GO

-- Create test table

CREATE TABLE dbo.ItemMaster

(

item_code BIGINT PRIMARY KEY,

field_01 INTEGER NOT NULL,

field_02 INTEGER NOT NULL,

field_03 INTEGER NOT NULL,

field_04 INTEGER NOT NULL,

field_05 INTEGER NOT NULL,

field_06 INTEGER NOT NULL,

field_07 INTEGER NOT NULL,

field_08 INTEGER NOT NULL,

field_09 INTEGER NOT NULL,

field_10 INTEGER NOT NULL,

);

GO

-- Add 5000 rows with item_code 1...5000

-- and random values in the remaining fields

INSERT dbo.ItemMaster

SELECT TOP (5000)

ROW_NUMBER() OVER (ORDER BY (SELECT 0)),

CHECKSUM(NEWID(), C1.name),

CHECKSUM(NEWID(), C1.name)

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3

Solution:

-- Procedure to copy rows using dynamic SQL

CREATE PROCEDURE dbo.DuplicateItem

@CopyFrom BIGINT,

@CopyTo BIGINT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @FieldList NVARCHAR(MAX),

@Command NVARCHAR(MAX);

-- Construct a comma-separated list of column names

-- Note this includes a comma at the start of the list

SELECT @FieldList =

SQ.field_names.value(N'/text()[1]', N'NVARCHAR(MAX)')

FROM (

-- Columns in creation order

-- Excludes identity and computed columns

-- Also excludes the primary key column

SELECT ',' + QUOTENAME(name)

FROM sys.columns

WHERE object_id = OBJECT_ID(N'dbo.ItemMaster', N'U')

AND name <> N'item_code'

AND is_computed = 0

AND is_identity = 0

ORDER BY

column_id ASC

FOR XML PATH(''), TYPE

) SQ (field_names);

-- Construct the dynamic INSERT

SET @Command =

N'INSERT dbo.ItemMaster ([item_code]' + @FieldList + N')' + NCHAR(13) + NCHAR(10) +

N'SELECT @CopyTo' + @FieldList + NCHAR(13) + NCHAR(10) +

N'FROM dbo.ItemMaster' + NCHAR(13) + NCHAR(10) +

N'WHERE [item_code] = @CopyFrom;'

-- Execute the INSERT

-- TODO: Add error handling and RETURN code

EXECUTE sys.sp_executesql

@stmt = @Command,

@params = N'@CopyFrom BIGINT, @CopyTo BIGINT',

@CopyFrom = @CopyFrom,

@CopyTo = @CopyTo;

END;

GO

-- Copy item #1 to #5001

EXECUTE dbo.DuplicateItem

@CopyFrom = 1,

@CopyTo = 5001;

GO

-- Add a column to ItemMaster

ALTER TABLE dbo.ItemMaster

ADD field_11 INTEGER NULL;

GO

-- Copy item #2 to #5002

EXECUTE dbo.DuplicateItem

@CopyFrom = 1,

@CopyTo = 5002;

GO

-- Show the rows concerned

SELECT *

FROM dbo.ItemMaster

WHERE item_code IN (1, 5001, 5002);

GO

-- Tidy up

IF OBJECT_ID(N'dbo.DuplicateItem', N'P')

IS NOT NULL

DROP PROCEDURE dbo.DuplicateItem;

IF OBJECT_ID(N'dbo.ItemMaster', N'U')

IS NOT NULL

DROP TABLE dbo.ItemMaster;

Sample output:

item_code field_01 field_02 field_03 field_04 field_05 field_06 field_07 field_08 field_09 field_10 field_11

1 1268541582 1906997792 1191891520 43814621 1947280987 -1095377504 761630357 206292222 -173264769 1981989299 NULL

5001 1268541582 1906997792 1191891520 43814621 1947280987 -1095377504 761630357 206292222 -173264769 1981989299 NULL

5002 1268541582 1906997792 1191891520 43814621 1947280987 -1095377504 761630357 206292222 -173264769 1981989299 NULL

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

patrick 98158 (3/11/2010)
I will call a stored procedure that INSERT INTO Table1 by selecting an existing record.
My intention is not to change the stored procedure, by having to explicitly code Item Category into it.

How often will the users be adding new "fields"? Also, can you post the stored procedure? Last but not least, I know you don't want to change the stored procedure but that would be the way to do it. That can probably be automated but we'd need to see it.

The other thing is, allowing a user to add a column via a GUI is fraught with problems... no one has done an index analysis to figure out if an index will help. None of the existing stored procedures can use the column unless they are dynamic or have been physically changed. Paul's good code above has an inkling of all that must be done to successfully pull this off.

There's also another way (and people will bark at it but it can be very effective when done correctly) using a special table type called an EAV. That's why I asked how often you expect users to add new "fields". If it's only going to be the one, then I recommend changing the stored procedure(s) on a permanent basis. If it's going to be a whole lot of columns, then there might actually be a design problem that folks need to spend some time reconsidering.

Again... can you post the stored procedure you're talking about?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 5

EAV :sick:

You can use sparse columns when you get to SQL Server 2008. Or XML now. Probably other things too.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

Paul White (3/14/2010)
EAV :sick:
You can use sparse columns when you get to SQL Server 2008. Or XML now. Probably other things too.

Heh... user defined columns :sick:... that's the real problem. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 7

Jeff Moden (3/14/2010)
Heh... user defined columns :sick:... that's the real problem. 😉

True. I worked with an application once that shared a schema across all customers...and any customer could ask for a new field to be added to any of the tables. One table had seven hundred columns...mostly irrelevant for the majority of clients. Design? Who needs it?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Paul White (3/14/2010)
Jeff Moden (3/14/2010)
Heh... user defined columns :sick:... that's the real problem. 😉
True. I worked with an application once that shared a schema across all customers...and any customer could ask for a new field to be added to any of the tables. One table had seven hundred columns...mostly irrelevant for the majority of clients. Design? Who needs it?

Heh... agreed. That's why they "deserve" an EAV. It's called "DBA revenge". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)