Wie kopiere ich eine Tabelle mit SELECT INTO, ignoriere aber die IDENTITY-Eigenschaft?
Ich habe eine Tabelle mit Identitätsspalte sagen:
create table with_id (
id int identity(1,1),
val varchar(30)
);
Es ist bekannt, dass dies
select * into copy_from_with_id_1 from with_id;
führt zu copy_from_with_id_1 mit identity on id.
Das folgende Stapelüberlauffrage erwähnt die explizite Auflistung aller Spalten.
Lass es uns versuchen
select id, val into copy_from_with_id_2 from with_id;
Hoppla, auch in diesem Fall ist id eine Identitätsspalte.
Was ich will, ist ein Tisch wie
create table without_id (
id int,
val varchar(30)
);
Von Online-Bücher
Das Format von new_table wird durch Auswerten der Ausdrücke in der Auswahlliste bestimmt. Die Spalten in new_table werden in der in der Auswahlliste angegebenen Reihenfolge erstellt. Jede Spalte in new_table hat denselben Namen, Datentyp, dieselbe Nullfähigkeit und denselben Wert wie der entsprechende Ausdruck in der Auswahlliste. Die IDENTITY-Eigenschaft einer Spalte wird übertragen , außer unter den Bedingungen, die unter "Arbeiten mit Identitätsspalten" im Abschnitt "Bemerkungen" definiert sind.
Unten auf der Seite:
Wenn eine vorhandene Identitätsspalte in einer neuen Tabelle ausgewählt wird, erbt die neue Spalte die IDENTITY-Eigenschaft, es sei denn, eine der folgenden Bedingungen ist erfüllt:
- Die SELECT-Anweisung enthält einen Join, eine GROUP BY-Klausel oder eine Aggregatfunktion.
- Mehrere SELECT-Anweisungen werden mithilfe von UNION verbunden.
- Die Identitätsspalte wird mehrmals in der Auswahlliste aufgeführt.
- Die Identitätsspalte ist Teil eines Ausdrucks.
- Die Identitätsspalte stammt aus einer entfernten Datenquelle.
Wenn eine dieser Bedingungen erfüllt ist, wird die Spalte NICHT NULL erstellt, anstatt die IDENTITY-Eigenschaft zu erben. Wenn in der neuen Tabelle eine Identitätsspalte erforderlich ist, eine solche Spalte jedoch nicht verfügbar ist oder Sie einen Startwert oder einen Inkrementwert wünschen, der sich von der Quellidentitätsspalte unterscheidet, definieren Sie die Spalte in der Auswahlliste mit der Funktion IDENTITY. Siehe "Erstellen einer Identitätsspalte mit der IDENTITY-Funktion" im folgenden Abschnitt "Beispiele".
Also ... könnte man theoretisch davonkommen mit:
select id, val
into copy_from_with_id_2
from with_id
union all
select 0, 'test_row'
where 1 = 0;
Es wäre wichtig, diesen Code zu kommentieren, um ihn zu erklären, damit er nicht entfernt wird, wenn jemand ihn das nächste Mal betrachtet.
Inspiriert von Erics Antwort fand ich die folgende Lösung, die nur von den Tabellennamen abhängt und keinen bestimmten Spaltennamen verwendet:
select * into without_id from with_id where 1 = 0
union all
select * from with_id where 1 = 0
;
insert into without_id select * from with_id;
Bearbeiten
Es ist sogar möglich, dies zu verbessern
select * into without_id from with_id
union all
select * from with_id where 1 = 0
;
Sie können einen Join verwenden, um die neue Tabelle auf einmal zu erstellen und zu füllen:
SELECT
t.*
INTO
dbo.NewTable
FROM
dbo.TableWithIdentity AS t
LEFT JOIN dbo.TableWithIdentity ON 1 = 0
;
Wegen dem 1 = 0 Bedingung, die rechte Seite hat keine Übereinstimmungen und verhindert somit das Duplizieren der linken Seitenreihen. Da dies eine äußere Verknüpfung ist, werden auch die linken Seitenreihen nicht entfernt. Da es sich um einen Join handelt, wird die IDENTITY-Eigenschaft entfernt.
Wenn Sie daher nur die Spalten auf der linken Seite auswählen, wird eine exakte Kopie von dbo.TableWithIdentity nur in Bezug auf die Daten erstellt, d. H. Wenn die IDENTITY-Eigenschaft entfernt wird.
Alles, was gesagt wird, Max Vernon hat einen gültigen Punkt in einem Kommentar angesprochen, der es wert ist, im Auge behalten zu werden. Wenn Sie sich den Ausführungsplan der obigen Abfrage ansehen:
sie werden feststellen, dass die Quelltabelle nur einmal im Ausführungsplan erwähnt wird. Die andere Instanz wurde vom Optimierer entfernt.
Wenn der Optimierer also korrekt feststellen kann, dass die rechte Seite des Joins im Plan nicht benötigt wird, sollte zu erwarten sein, dass in einer zukünftigen Version von SQL Server möglicherweise herausgefunden werden kann, dass die IDENTITY-Eigenschaft nicht erforderlich ist wurde ebenfalls entfernt, da in der Quellzeile, die gemäß dem Abfrageplan festgelegt wurde, keine weitere IDENTITY-Spalte mehr vorhanden ist. Dies bedeutet, dass die obige Abfrage möglicherweise irgendwann nicht mehr wie erwartet funktioniert.
Wie jedoch von ypercubeᵀᴹ korrekt angegeben, hat das Handbuch bisher ausdrücklich angegeben, dass die IDENTITY-Eigenschaft bei einem Join nicht erhalten bleibt:
Wenn eine vorhandene Identitätsspalte in einer neuen Tabelle ausgewählt wird, erbt die neue Spalte die IDENTITY-Eigenschaft, es sei denn, [...] [t] die SELECT-Anweisung enthält einen Join.
Solange das Handbuch dies erwähnt, können wir wahrscheinlich sicher sein, dass das Verhalten gleich bleibt.
Ein großes Lob an Shaneis und ypercubeᵀᴹ für das Aufrufen eines verwandten Themas im Chat.
Versuchen Sie diesen Code ..
SELECT isnull(Tablename_old.IDENTITYCOL + 0, -1) AS 'New Identity Column'
INTO dbo.TableName_new
FROM dbo.TableName_old
Der Aufruf ISNULL stellt sicher, dass die neue Spalte mit der Nullability NOT NULL Erstellt wird.
Nur um einen anderen Weg zu zeigen:
Sie können einen Verbindungsserver verwenden.
SELECT *
INTO without_id
FROM [linked_server].[source_db].dbo.[with_id];
Sie können vorübergehend einen Verbindungsserver zum lokalen Server erstellen, indem Sie Folgendes verwenden:
DECLARE @LocalServer SYSNAME
SET @LocalServer = @@SERVERNAME;
EXEC master.dbo.sp_addlinkedserver @server = N'localserver'
, @srvproduct = ''
, @provider = 'SQLNCLI'
, @datasrc = @LocalServer;
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'localserver'
, @useself = N'True'
, @locallogin = NULL
, @rmtuser = NULL
, @rmtpassword = NULL;
An diesem Punkt würden Sie das select * into Code, der auf den vierteiligen Namen des Verbindungsservers localserver verweist:
SELECT *
INTO without_id
FROM [localserver].[source_db].dbo.[with_id];
Bereinigen Sie anschließend den Verbindungsserver localserver folgendermaßen:
EXEC sp_dropserver @server = 'localserver'
, @droplogins = 'droplogins';
Oder Sie können die Syntax OPENQUERY verwenden
SELECT *
INTO without_id
FROM OPENQUERY([linked_server], 'SELECT * FROM [source_db].dbo.[with_id]');
Die Identitätseigenschaft wird nicht übertragen, wenn die select-Anweisung einen Join enthält
select a.* into without_id from with_id a inner join with_id b on 1 = 0;
gibt auch das gewünschte Verhalten an (der kopierten Spalte id, um die Eigenschaft IDENTITY nicht beizubehalten. Dies hat jedoch den Nebeneffekt, dass überhaupt keine Zeile kopiert wird! (wie bei einigen anderen) Methoden), also müssen Sie dann tun:
insert into without_id select * from with_id;
(Danke AakashM!)
Der einfache Weg besteht darin, die Spalte zu einem Teil eines Ausdrucks zu machen.
Beispiel :
Wenn die Tabelle dbo.Employee eine Identität in der ID-Spalte hat, hat im folgenden Beispiel die temporäre Tabelle #t auch eine IDENTITY in der ID-Spalte.
--temp table has IDENTITY
select ID, Name
into #t
from dbo.Employee
Ändern Sie dies, um einen Ausdruck auf ID anzuwenden, und Sie haben keine IDENTITÄT mehr in der ID-Spalte. In diesem Fall fügen wir der ID-Spalte einen einfachen Zusatz hinzu.
--no IDENTITY
select ID = ID + 0, Name
into #t
from dbo.Employee
Andere Beispiele für Ausdrücke für andere Datentypen könnten sein: convert (), String-Verkettung oder Isnull ()
Manchmal möchten Sie aus einer Tabelle einfügen, in der Sie nicht wissen (oder sich nicht darum kümmern), ob die Spalte mit IDENTITY erstellt wurde oder nicht. Möglicherweise handelt es sich nicht einmal um eine Ganzzahlspalte, mit der Sie arbeiten. In diesem Fall funktioniert Folgendes:
SELECT TOP(0) ISNULL([col],NULL) AS [col], ... INTO [table2] FROM [table1]
ALTER TABLE [table2] REBUILD WITH (DATA_COMPRESSION=page)
INSERT INTO [table2] ...
ISNULL löscht das IDENTITY-Attribut aus der Spalte, fügt es jedoch mit demselben Namen und Typ wie die ursprüngliche Spalte ein und macht es auch nicht nullbar. TOP (0) erstellt eine leere Tabelle, in die Sie ausgewählte Zeilen einfügen können. Sie können die Tabelle bei Bedarf auch komprimieren, bevor Sie Daten einfügen.
select convert(int, id) as id, val
into copy_from_with_id_without_id
from with_id;
wird Identität entfernen.
Der Nachteil ist, dass id nullbar wird, aber Sie können diese Einschränkung hinzufügen.