September 12, 2019 at 11:25 pm
Hi
I need to add of list of customers to a table from a file probably a CSV.
The file import will probably happen once a week/month.
I want to stop any other process that is trying to insert getting the same id, but hopefully not stop any updates/selects on previous rows.
CREATE TABLE dbo.tblCustomer(CustID int NOT NULL,CustName varchar(20) NOT NULL,
CONSTRAINT PK_tblCustomer PRIMARY KEY CLUSTERED (CustID ASC))
insert into tblcustomer (CustID, CustName) values(1, 'Andy'), (2, 'Bob'), (3, 'Charlie'), (4,'Diane')
-- Populate temp table #File with contents of File
Create table #File (CustName varchar(20) NOT NULL);
insert into #File (CustName) values ('Edward'), ('Fiona'), ('Grace');
As this is a 3rd Party DB, I cannot make CustID an Identity field
CustID int IDENTITY(1,1) NOT NULL
Or create a new table to store next CustID
My solution
BEGIN transaction;
insert into tblcustomer
SELECT ISNULL(MAX(custid),0) + T.offset, T.Custname
FROM tblCustomer (SERIALIZABLE )
CROSS APPLY (
select CustName, offset = ROW_NUMBER() OVER( ORDER BY CustName) from #File
) as T
GROUP BY T.offset, T.Custname
COMMIT;
-- Do it many times while executing single insert in another connection to try and get PK error
GO 1000
I ran below while it was inserting from the temp table 1000 times - NO PK errors
insert into tblcustomer (CustID, CustName)
select MAX(CustID) + 1, 'zzzz' from tblcustomer
SEEMS to work or was I just lucky?
Thanks
September 13, 2019 at 12:45 am
Why not use an IDENTITY column for this?
Or create a SEQUENCE and use that.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 13, 2019 at 7:28 am
Hi Phil
It’s a 3rd Party DB/App, I cannot change the tables.
September 13, 2019 at 1:34 pm
Hi Phil
It’s a 3rd Party DB/App, I cannot change the tables.
Apologies for not taking the time to read your original post thoroughly enough!
Can you not emulate exactly what the third-party app is doing when it creates new rows? Presumably, they have already cracked any locking/concurrency issues? (Or, given the Max(Id) + 1 design, perhaps not!!)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 13, 2019 at 6:47 pm
No problem Phil - thank you for looking at my post
I jumped the gun somewhat, had chat with colleague. It stores next available CustID on the Application server in a "Custom DB format" - NOT MSSQL not sure what though.
So hopefully I can call their API something like "reserve_customers ##" where ## is the number of customers I have in my file.
For my knowledge/understanding
I was using MAX(id) + 1 in another query window while I was executing, the (SERIALIZABLE) insert 1000 times on a different connection.
I got no PK clashes. I don't know if this was by luck or by design?
Would
insert into tblA (id)
select max(id) + 1 from tblA
Cause PK clashes if many connections where executing it? I'm sure it would be a disaster for performance but wouldn't it be safe?
September 13, 2019 at 6:54 pm
Using serializable should do the trick in your example, I agree.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 16, 2019 at 10:27 am
Most third party applications that I come across have there own import routines. It may be worth doing some research to see if the app you are using has import routines. Also, the license of third party apps tends to stop you directly modifying the data so you had better check this as well.
September 19, 2019 at 9:04 am
Hi Ken - you're right - couldn't agree more.
I've contacted the vendor, I would never do this without checking first.
In this case It was more for my understanding, I've found out how little I knew about locking isolation levels
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply