INSERT Multiple Rows and getting max(id)

We do not auto increment the PK field in tblA.  We look to see the Max(MyPK) and add 1.  This is not the most efficient but this is the way we do it.

I have to INSERT multiple rows at a time.  How can I keep getting the Max(MyPK) for the next row?  Will this work?

INSERT INTO tblA

(TEXT_ID, Source_uno,  TXT1, TXT2, LAST_MODIFIED)

SELECT  max(text_ID) + 1,
 hm.MATTER_UNO,   
SUBSTRING(mqa.QBILLINGNOTES, 1, 250), 
SUBSTRING(mqa.QBILLINGNOTES, 251, 250), GETDATE()

FROM tblB hm
LEFT JOIN tblC mqa ON hm._ProLaw_FK = mqa.Matters

Open in new window

huerita37Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It's not just an issue of efficiency.  You are going to have to be extremely careful with concurrency issues with this setup.  You are going to run into problems with this approach and I strongly recommend you convert to an IDENTITY column.

I don't mean to be preachy but this is simply put...a bad idea.
huerita37Author Commented:
I just found out some new information.  I am new to this company and I am not given the whole story until I dig real deep.

There is a table that store the LastKey for each table used.

Here is the query I would be using to get the last key used for tblA.

SELECT LastKey FROM [CMS_UNIQUE_KEYS] WHERE TBNAME = 'TBM_TEXT'

When one record is INSERTED INTO tblA then [CMS_UNIQUE_KEYS] table needs to be updated.

UPDATE [CMS_UNIQUE_KEYS]
SET LastKey = LastKey  + 1
WHERE TBNAME = 'TBM_TEXT'

How can I INSERT multiple records into tblA when I have to keep UPDATING  [CMS_UNIQUE_KEYS]?
Brian CroweDatabase AdministratorCommented:
I have seen this approach before as well.  There should be a stored procedure or function that you have to funnel ID requests through which handles the logic for distributing the value and incrementing the value in CMS_UNIQUE_KEYS.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
If they have set it up to use a user-defined function then you can just call it inline.

INSERT INTO tblA
SELECT dbo.GetNewKey('tblA'), <other values>
FROM tblB
WHERE ...
huerita37Author Commented:
There's not.  They use CURSORS to do all of this.  It's crazy how they complete it.
Brian CroweDatabase AdministratorCommented:
Ugh...run while you still can.

Seriously though I would try creating a function yourself that takes the tablename as a parameter and returns the next value and increments the current value.  Unfortunately you can't wrap this in a transaction as UDF's don't allow them.  It seems like a lot of work to get around functionality that is already built in.
huerita37Author Commented:
The problem with creating a function is that I need to update  [CMS_UNIQUE_KEYS] after I do one INSERT and I don't know how to INSERT into two table in one query.
huerita37Author Commented:
How would I do this in a StoredProcedure?
huerita37Author Commented:
I do understand this is BADDDDD practice and I need to change it.  I need to insert about 200 records ASAP.  Would this technically work?
INSERT INTO tblA

(TEXT_ID, Source_uno,  TXT1, TXT2, LAST_MODIFIED)

SELECT  max(text_ID) + 1,
 hm.MATTER_UNO,   
SUBSTRING(mqa.QBILLINGNOTES, 1, 250), 
SUBSTRING(mqa.QBILLINGNOTES, 251, 250), GETDATE()

FROM tblB hm
LEFT JOIN tblC mqa ON hm._ProLaw_FK = mqa.Matters

Open in new window

Brian CroweDatabase AdministratorCommented:
Since you're stuck with it try something like this...

DECLARE @RowCount	INT;

BEGIN TRAN

INSERT INTO tblA
(
	TEXT_ID,
	Source_uno,
	TXT1,
	TXT2,
	LAST_MODIFIED
)
SELECT ROW_NUMBER() OVER(ORDER BY hm.MATTER_UNO) + (SELECT LastKey FROM CMS_UNIQUE_KEYS WHERE TBNAME = 'TBM_TEXT')
	hm.MATTER_UNO,
	SUBSTRING(mqa.QBILLINGNOTES, 1, 250), 
	SUBSTRING(mqa.QBILLINGNOTES, 251, 250),
	GETDATE()
FROM tblB AS hm
LEFT OUTER JOIN tblC AS mqa
	ON hm._ProLaw_FK = mqa.Matters;

SELECT @RowCount = @@ROWCOUNT;

UPDATE CMS_UNIQUE_KEYS
SET LastKey = LastKey + @RowCount
WHERE TBNAME = 'TBM_TEXT';

COMMIT TRAN

Open in new window

Scott PletcherSenior DBACommented:
INSERT INTO tblA
(TEXT_ID, Source_uno,  TXT1, TXT2, LAST_MODIFIED)

SELECT (SELECT MAX(TEXT_ID) FROM tblA WITH (TABLOCKX)) +
    ROW_NUMBER() OVER(ORDER BY hm.MATTER_UNO) AS TEXT_ID,
hm.MATTER_UNO,
SUBSTRING(mqa.QBILLINGNOTES, 1, 250),
SUBSTRING(mqa.QBILLINGNOTES, 251, 250),
GETDATE()

FROM tblB hm
LEFT JOIN tblC mqa ON hm._ProLaw_FK = mqa.Matters
Brian CroweDatabase AdministratorCommented:
Just for kicks I made a few minor adjustments to make the process a little more robust and fixed the missing comma issue I had.  This would still need to be tested thoroughly and the actual schema references need to be substituted but...

DECLARE @RowCount	INT;

BEGIN TRAN

INSERT INTO tblA
(
	TEXT_ID,
	Source_uno,
	TXT1,
	TXT2,
	LAST_MODIFIED
)
SELECT ROW_NUMBER() OVER(ORDER BY hm.MATTER_UNO) + Keys.LastKey,
	hm.MATTER_UNO,
	SUBSTRING(mqa.QBILLINGNOTES, 1, 250), 
	SUBSTRING(mqa.QBILLINGNOTES, 251, 250),
	GETDATE()
FROM tblB AS hm
LEFT OUTER JOIN tblC AS mqa
	ON hm._ProLaw_FK = mqa.Matters
CROSS APPLY
(
	SELECT LastKey
	FROM CMS_UNIQUE_KEYS WITH (ROWLOCK, HOLDLOCK)
	WHERE TBNAME = 'TBM_TEXT'
) AS Keys;

SELECT @RowCount = @@ROWCOUNT;

UPDATE CMS_UNIQUE_KEYS
SET LastKey = LastKey + @RowCount
WHERE TBNAME = 'TBM_TEXT';

COMMIT TRAN

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.