Fortlaufende Nummer als Primärschlüsselteil bei Masseninsert

Beschreibung:

Ich stand gerade vor dem Problem und möchte die Lösung mit euch teilen, fals jemand wieder mal vor so einem Problem stehen sollte:
Zur Ausgangsbasis: Ich habe eine Datenbank in die Daten aus einer externen Datenbank migriert werden sollen.
Diese wurde bereits auf den SQL Server geladen und es gibt schon fertige Views die die Daten aufbereiten.

Konkret geht es nun um Adressen: In meiner Datenbank ist der PK der Adress-Tabelle die PersonNr und eine innerhalb der Person fortlaufende Nr (das ist historisch bedingt). Aus der externen DB krieg ich aber keine fortlaufend Nr, die ist in der View also immer null.

Beim Masseninsert hab ich nun natürlich das Problem, dass der SQL Server meckert (PK-Verletzung), wenn ich die Daten einfüge, weil ja die Nr immer die selbe ist.

Daher müssen also die Nummern nun irgendwie aufbereitet werden. Hier kommt nun der folgende Code ins Spiel:

BEGIN TRAN

-- Daten aus der View in eine temporäre Tabelle holen
-- Die Daten werden dabei gleich so sortiert, dass die Nr richtig ermittelt werden können
-- d.h. die älteste Adresse erhält Nr 1, dann 2 etc.
SELECT * INTO #tmpAddress
FROM External_PersonAdresses
ORDER BY PersonNr,CreatedDate,ChangedDate

-- Weil es aber durchaus sein kann, 
-- dass 2 Adressen bei der Person mit dem selbem Datum eingefügt werden,
-- und der nachfolgende Durchlauf dann nicht mehr funktioniert,
-- brauchen wir eine ReferenzID. 
-- Welche der Adressen zuerst kommt, wenn alle sortier-werte gleich sind, ist egal
ALTER TABLE #tmpAddress ADD ID int IDENTITY(1,1)

DECLARE @Nr as int
SET @Nr = 1

-- Dieser Teil geht die Tabelle durch und holt immer den "Ältesten" Datensatz,
-- und setzt die fortlaufende Nr, und das solange bis alle Nr gesetzt wurden
WHILE EXISTS ( SELECT * FROM #tmpAddress WHERE AdrNr is null)
BEGIN
	print 'AdrNr: ' + cast(@Nr as varchar)
	UPDATE tmp SET AdrNr = @Nr
	FROM #tmpAddress tmp
	JOIN (
		SELECT PersonNr,Min(ID) as Eldest
		FROM #tmpAddress
		WHERE AdrNr is null
		GROUP BY PersonNr
	) E on tmp.PersonNr = E.PersonNr AND tmp.ID = E.Eldest
	WHERE AdrNr is null

	SET @Nr = @Nr+1
END

-- ID brauchen wir nicht mehr
ALTER TABLE #tmpAddress DROP Column ID

-- Daten in die interne Tabelle kopieren
INSERT INTO Internal_Address
SELECT * FROM #tmpAddress

COMMIT

Sollte irgendjemand eine bessere Idee haben, dies zu lösen ... bitte gerne und wenn so nett, auch posten.

Schlagwörter: FortlaufendeNr einfügen, MassenInsert

Gibt es beim SQL Server nicht die Option, für eine neue Zeile (also nach einem Insert) eine aufsteigende Nummer zu vergeben? Die benutzen wir bei uns, klappt das bei dir nicht?

Dann hättest du nämlich die ganze Arbeit umsonst gemacht xD

mfg, Rasta

Ich benutze ab und an DateTime, um einen "eindeutigen" Schlüssel zu erhalten.
Der Schlüssel ändert sich ca. alle 5ms, was in der Regel reicht.

DateTime.Now.Ticks.ToString()

Mit noch genaueren APIs kann man damit fast eindeutige Schlüssel generieren, ohne viel Aufwand zu betreiben.

Was auch geht:

INSERT INTO DeineTabelle(ID) VALUES (SELECT MAX(Deine_ID)+1 FROM DeineTabelle);

Aber Achtung: die Nummern werden vorher nicht reserviert.

Eine andere Möglichkeit: Eine Sequence-Tabelle anlegen, in dem der letzte Wert gespeichert wird und der immer wieder erhöht und danach abgefragt wird. Dann sind die Nummern immer eindeutig, da Session-abhängig nach dem Commit der Eintrag angelegt und abgefragt werden kann. Außerdem kann diese Tabelle für jede weitere Tabelle mit eindeutigen Schlüsseln benutzt werden. Orcale macht es mit SEQ.NEXTVAL nicht viel anders 😃