question

tdmtek avatar image
tdmtek asked

Insert rows with max(value)+1

I need to update a table by inserting a new row for a number of customers. I need to structure the update like the existing data, but find the max value of 1 field and increment it by 1. This isn't our db to modify, I need to do this with the current structure. DDL:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PATIENT_COMMENT](
	[Com_Pra_Id] [varchar](3) NOT NULL,
	[Com_Pat_Id] [varchar](15) NOT NULL,
	[Com_Seq] [int] NOT NULL,
	[Com_Usr_Id_Added] [varchar](3) NULL,
	[Com_Usr_Id_Modified] [varchar](3) NULL,
	[Com_Date_Added] [datetime] NULL,
	[Com_Date_Modified] [datetime] NOT NULL,
	[Com_Tag] [datetime] NULL,
	[Com_Comments] [text] NULL,
 CONSTRAINT [PK_PATIENT_COMMENT2] PRIMARY KEY CLUSTERED 
(
	[Com_Pra_Id] ASC,
	[Com_Pat_Id] ASC,
	[Com_Seq] ASC,
	[Com_Date_Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
I need to increment the com_seq by 1 based on the max com_seq for each customer

Sample data:
Com_Pra_Id	Com_Pat_Id	Com_Seq	Com_Usr_Id_Added	Com_Usr_Id_Modified	Com_Date_Added	         Com_Date_Modified	      Com_Tag	Com_Comments
ARR	      11111111	     23	   AMC	               AMC	            2009-03-24 15:02:46.000	2009-03-24 15:02:46.000	NULL	   Blah blah

t-sqlsql-server-2008-r2insert
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moved out of meta space on site.
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.