INSERT Statement

Transact-SQL
1

Hi,

I have the following query;

INSERT INTO dbo.EmisPractice ( PracGuidDigest, NPC,  PracID)
SELECT  PracGuidDigest, NPC, PracID
FROM dbo.EmisPracExtract

Sample dbo.EmisPracExtract

PracGuidDigest     NPC              PracID
Q2372GG             W23533         NULL
LL38DD80            N83563          NULL 
H64GGUE            K82653          20001
JKGGE33            A36346          20002

The property of Prac ID = int, PK, Identity Yes, Identity Increment = 1, Identity seed = 20001

So when I do the INSERT I want to have the final results inserted as

PracGuidDigest     NPC              PracID
Q2372GG             W23533        20003
LL38DD80            N83563         20004
H64GGUE            K82653          20001
JKGGE33            A36346          20002

How can I change the INSERT statement to accommodate this update?

Thank you so much

2

This part of the query is not guaranteed to return results in any particular order. Try adding an order by clause.

See Ordering guarantees in SQL Server...

3

Hi,

I have the Order By PracID in the query (dbo.EmisPracExtract)

SELECT     TOP (100) PERCENT OrganisationGuidDigest AS PracGuidDigest, NationalPracticeCode AS NPC, 
                      PracID
FROM         EMISNov15DB.dbo.PracIDMatchList WITH (tablock)
ORDER BY PracID

Sorry updated the results at the top accordingly

Any idea how I can achieve this ?

Thanks

4

You don't have an ORDER BY in the INSERT query:

So SQL will insert the rows in any order it likes. Add an ORDER BY clause to the INSERT query.

5

NO I don't - Does it matter ?

My issue was primarily that some PRACIDs have values and some NULL - the one's with values to be inserted as it is however if it is a NULL then pick the Max value in the table lets say in the example above its 2002, then do the incremental value to the NULL values i.e., 20003, 20004 etc

6

yes, it matters. That's what I've been explaining. Did you read the link I posted?

7

Yes - I read the post..

Will this work, will it retain the PracID that already exists and populate the other records with a continuation i.e., 20003, 20004 in the above example...

INSERT TOP 100 PERCENT INTO dbo.EmisPractice ( PracGuidDigest, NPC, PracID )
SELECT  PracGuidDigest, NPC, PracID
FROM dbo.EmisPracExtract
ORDER BY PracID

Thanks

8

Wait, in your original post, the source table already has a PracID column, which is null for Q2372GG and LL38DD80.

Inserting them again will not cause PracID to be reassigned. It will just duplicate the rows.

For that matter your query should fail unless you SET IDENT_INSERT ON for that table.

9

I did change the script to

SET IDENTITY_INSERT dbo.EmisPractice1 ON

INSERT INTO dbo.EmisPractice1 ( PracID, PracGuidDigest, NPC)
SELECT  PracID, PracGuidDigest, NPC
FROM dbo.EmisPracExtract
ORDER BY PracID

SET IDENTITY_INSERT dbo.EmisPractice1 OFF

I received the error message as shown;

Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'PracID', table 'EMISRESNvMaster.dbo.EmisPractice1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Please assist - Thanks

10

you'll need to break it up

SET IDENTITY_INSERT dbo.EmisPractice1 ON
INSERT INTO dbo.EmisPractice1 ( PracID, PracGuidDigest, NPC)
SELECT  PracID, PracGuidDigest, NPC
FROM dbo.EmisPracExtract
WHERE PracID IS NOT NULL
ORDER BY PracID
SET IDENTITY_INSERT dbo.EmisPractice1 OFF

INSERT INTO dbo.EmisPractice1 (  PracGuidDigest, NPC)
SELECT PracGuidDigest, NPC
FROM dbo.EmisPracExtract
WHERE PracID IS NULL
ORDER BY PracGuidDigest