[RESOLVED] Create new record with values from existing record

[aconybeare]

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

        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

[aconybeare]

Hi,

Think I'm sorted have done the following

    -- Create the new version with selected values from the last version	
	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	
		)
		SELECT
			@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
		FROM MS_Version
		WHERE Jnl_Code=@Jnl_Code
		AND MS_No=@MS_No
		AND Version_No=@Version_No -1
	
	SET @Result = @@Error