• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Help with Insert Query

Hi Experts

I need to insert a new row to a table which contains 99% of the information from an existing row but with a few minor changes. I pass the existing unique ID of the row I wish to copy and the new info I need to insert. I'm struggling a little with the syntax and I'm not sure if I'm approaching it wrong.
A summary is below:
Create Procedure AddAnExtraLine()
	@Unique_ID [uniqueidentifier],
	@MyNewBatch [nvarchar](20)
BEGIN

	DECLARE @NEWUnique_ID [uniqueidentifier]
	SELECT @NEWUnique_ID = NewId()

	INSERT INTO [Mytable]
		([Unique_ID]
		,[UniqueID2]
		,[MyDate]
		,[Pmt_Type]
		,[Pmt_Ref]
		,[Description]
		,[Quantity]
		,[UnitPrice]
		,[NettAmt]
		,[VatAmt]
		,[Batch_ID])
	 VALUES
		(SELECT (@NEWUnique_ID 
		,[UniqueID2]
		,[MyDate]
		,[Pmt_Type]
		,[Pmt_Ref]
		,[Description]
		,0
		,0
		,0
		,0
		,@MyNewBatch)
	FROM [Mytable] 
	WHERE [Unique_ID] = @Unique_ID)
END

Open in new window

0
alexking
Asked:
alexking
1 Solution
 
Pratima PharandeCommented:
try this

Create Procedure AddAnExtraLine()
      @Unique_ID [uniqueidentifier],
      @MyNewBatch [nvarchar](20)
BEGIN

      DECLARE @NEWUnique_ID [uniqueidentifier]
      SELECT @NEWUnique_ID = NewId()

      INSERT INTO [Mytable]
            ([Unique_ID]
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,[Quantity]
            ,[UnitPrice]
            ,[NettAmt]
            ,[VatAmt]
            ,[Batch_ID])
       VALUES
            Select (@NEWUnique_ID
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,0
            ,0
            ,0
            ,0
            ,@MyNewBatch)
      FROM [Mytable]
      WHERE [Unique_ID] = @Unique_ID)
END
0
 
Pratima PharandeCommented:
sorry try thsi

Create Procedure AddAnExtraLine()
      @Unique_ID [uniqueidentifier],
      @MyNewBatch [nvarchar](20)
BEGIN

      DECLARE @NEWUnique_ID [uniqueidentifier]
      SELECT @NEWUnique_ID = NewId()

      INSERT INTO [Mytable]
            ([Unique_ID]
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,[Quantity]
            ,[UnitPrice]
            ,[NettAmt]
            ,[VatAmt]
            ,[Batch_ID])
       VALUES
            (Select @NEWUnique_ID
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,0
            ,0
            ,0
            ,0
            ,@MyNewBatch
      FROM [Mytable]
      WHERE [Unique_ID] = @Unique_ID)
END
0
 
jimyXCommented:
Create Procedure AddAnExtraLine()
      @Unique_ID [uniqueidentifier],
      @MyNewBatch [nvarchar](20)
BEGIN

      DECLARE @NEWUnique_ID [uniqueidentifier]
      SELECT @NEWUnique_ID = NewId()

      INSERT INTO [Mytable]
            ([Unique_ID]
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,[Quantity]
            ,[UnitPrice]
            ,[NettAmt]
            ,[VatAmt]
            ,[Batch_ID])
       VALUES
            (SELECT @NEWUnique_ID
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,0
            ,0
            ,0
            ,0
            ,@MyNewBatch
      FROM [Mytable]
      WHERE [Unique_ID] = @Unique_ID)
END
0
 
HainKurtSr. System AnalystCommented:
I guess we dont need "VALUES"
try this:
Create Procedure AddAnExtraLine()
      @Unique_ID [uniqueidentifier],
      @MyNewBatch [nvarchar](20)
BEGIN
      DECLARE @NEWUnique_ID [uniqueidentifier]
      SELECT @NEWUnique_ID = NewId()

      INSERT INTO [Mytable]
            ([Unique_ID]
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,[Quantity]
            ,[UnitPrice]
            ,[NettAmt]
            ,[VatAmt]
            ,[Batch_ID])
            (SELECT @NEWUnique_ID 
            ,[UniqueID2]
            ,[MyDate]
            ,[Pmt_Type]
            ,[Pmt_Ref]
            ,[Description]
            ,0
            ,0
            ,0
            ,0
            ,@MyNewBatch
      FROM [Mytable] 
      WHERE [Unique_ID] = @Unique_ID)
END

Open in new window

0
 
alexkingAuthor Commented:
Aaah Thankyou!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now