Stored Procedure to duplicate record(s)

[WaveOut]

I would like to duplicate some records in a table. The table name is Craft and it has the following fields:

id - integer (Identity = Yes)
week_UID - uniqueidentifier
craft_ID - integer
craft_group - integer
craft_name - nvarchar
craft_category - nvarchar
craft_count - integer
craft_hours - float


when I duplicate the records I want to duplicate all but the last two fields (craft_count and craft_hours).

I would like to execute this from a stored procedure. I will provide the week_UID(@wuid_copy) of the week I want to copy and also the week_UID(@wuid) of the week I want to copy to.

I need the sql statement for the procedure below.

CREATE PROCEDURE spDupeWeek
-- @wuid = current week we are working with
-- @wuid_copy = week we want to copy data from

        @wuid uniqueidentifier,
        @wuid_copy uniqueidentifier
AS
BEGIN
I need the sql code that will go here
END
GO

Thanks,
Dave

 

[SQLSister]

And the reason why we should write your sp from scratch would be?

What have you tried?

"NOTHING is more important in a database than integrity." ESquared

 

[WaveOut]

I have tried the following but it did not work:

CREATE PROCEDURE spDupeWeek
-- @wuid = current week we are working with
-- @wuid_copy = week we want to copy data from

        @wuid uniqueidentifier,
        @wuid_copy uniqueidentifier
AS
BEGIN
Insert Into Craft(week_Uid,craft_ID,craft_group, craft_name,craft_category)
Select  @wuid,craft_ID,craft_group, craft_name,craft_category
From Craft
where week_UID = @wuid_copy
END
GO

Dave

 

[bborissov]

Dave, this is something fairly easy.
What you have till know?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[WaveOut]

I think I figured it out. The craft_count was a required field. I removed that constrant and it now works.

Dave

 

[bborissov]

No, don't remove the constraint. I have one rule NEVER allow NULLs for any data type different than Datetime

CREATE PROCEDURE spDupeWeek
-- @wuid = current week we are working with
-- @wuid_copy = week we want to copy data from

        @wuid uniqueidentifier,
        @wuid_copy uniqueidentifier
AS
BEGIN
Insert Into Craft(week_Uid,
                  craft_ID,
                  craft_group,
                  craft_name,
                  craft_category,
                  craft_count,
                  craft_hours)
Select  @wuid_copy,
        craft_ID,
        craft_group,
        craft_name,
        craft_category,
        0,
        0
From Craft
where week_UID = @wuid_copy
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Thargy]

bborisov,

on what basis did you say

I have one rule NEVER allow NULLs for any data type different than Datetime

I know there was a smiley after that statement, were you serious? I couldn't figure out the humour there, Boris me ol' fruit (to use and Anglicism)

T

Grinding away at things Oracular