[RESOLVED] Create new record with values from existing record

Hi,

I'm trying to insert a new record into my table with some new values as well as using selected data from an existing record.

I'm creating a new version of a record and want to populate the row with some data from the last version

This is a snippet from my Stored proc, it bombs out because you can't use nText with local variables

Is there a better way of achieving this?

The values HiLighted in yellow are from the existing record

Code:

DECLARE @Title nvarchar(500) DECLARE @Abstract ntext DECLARE @Paper_Type_Code varchar(10) DECLARE @Keywords ntext DECLARE @Category varchar(100) DECLARE @Restricted_Reviewers ntext DECLARE @Deposit_Check bit DECLARE @Accession_Nos ntext DECLARE @Related_Articles ntext -- Get selected values from previous record SELECT @Title=Title ,@Abstract=Abstract ,@Paper_Type_Code=Paper_Type_Code ,@Keywords=Keywords ,@Category=Category ,@Restricted_Reviewers=Restricted_Reviewers ,@Deposit_Check=Deposit_Check ,@Accession_Nos=Accession_Nos ,@Related_Articles=Related_Articles FROM MS_Version WHERE Jnl_Code=@Jnl_Code AND MS_No=@MS_No AND Version_No=@Version_No -1 -- Create new version using selected fields from last version as defaults INSERT INTO MS_Version( Jnl_Code ,MS_NO ,Version_No ,Submit_Date ,Instructions_Check ,Sub_Status ,Submit_Auth_ID ,Permission_Check ,Permission_Check_US_Gov ,Title ,Abstract ,Paper_Type_Code ,Keywords ,Category ,Restricted_Reviewers ,Deposit_Check ,Accession_Nos ,Related_Articles ) VALUES ( @Jnl_Code ,@MS_No ,@Version_No ,GetDate() ,1 ,'N' ,@User_ID ,@PermToPub ,@PermToPubUSGov ,@Title ,@Abstract ,@Paper_Type_Code ,@Keywords ,@Category ,@Restricted_Reviewers ,@Deposit_Check ,@Accession_Nos ,@Related_Articles ) SET @Result = @@Error