SELECT TOP 0 INTO

Was tut SELECT TOP 0 INTO?

Gelegentlich kommt es vor, dass ihr eine leere Tabelle erzeugen möchtet, um die Daten einer Abfrage zu speichern. Ihr könnt nun natürlich aus allen Quelltabellen die Metadaten aller Spalten in der Abfrage prüfen und ein DDL Statement schreiben. Das kann aber bei Abfragen mit mehreren Joins und möglicherweise noch Manipulationen in den Spalten ziemlich kompliziert werden. Wenn ihr nun Daten in eine neu zu erstellende Tabelle kopieren möchtet, könnt ihr das mit SELECT INTO machen. Wenn ihr stattdessen nur die Struktur kopieren möchtet, könnt ihr einfach die TOP 0 Zeilen kopieren, also keine Zeilen, wodurch ihr eine Tabelle erhaltet, die nur die Struktur des Quellstatements abbildet.

Wofür könnt ihr SELECT TOP 0 INTO verwenden?

Wie bereits oben beschrieben, könnt ihr mit einem SELECT TOP 0 INTO eine leere Tabelle erstellen, die strukturgleich mit eurer Abfrage ist. Wir wollen das einmal tun indem wir uns einige Attribute der Customers-Tabelle in der WideworldImporters Datenbank vornehmen, die wir in eine MainCustomers-Tabelle speichern möchten:

DROP TABLE IF EXISTS [Sales].[MainCustomers]
SELECT TOP (0) 
       [CustomerID]
      ,[CustomerName]
      ,[CreditLimit]
      ,[AccountOpenedDate]
      ,[StandardDiscountPercentage]
      ,[IsStatementSent]
      ,[IsOnCreditHold]
      ,[PaymentDays]
      ,[PhoneNumber]
      ,[FaxNumber]
      ,[DeliveryRun]
      ,[RunPosition]
      ,[WebsiteURL]
      ,[DeliveryAddressLine1]
      ,[DeliveryAddressLine2]
      ,[DeliveryPostalCode]
      ,[DeliveryLocation]
      ,[PostalAddressLine1]
      ,[PostalAddressLine2]
      ,[PostalPostalCode]
  INTO [Sales].[MainCustomers]
  FROM [Sales].[Customers]

In eurer Datenbank findet ihr nun eine MainCustomers-Tabelle, die genau die hier ausgewählten Felder enthält mit identischen Metadaten. Das Ganze geht natürlich noch etwas komplexer, wenn ihr, statt nur die Metadaten aus einer Tabelle zu kopieren einen JOIN ausführt. Dafür ermitteln wir zunächst den Gesamtumsatz und die Anzahl der Bestellungen unserer Kunden:

SELECT 
[CustomerID]
,SUM(TransactionAmount) as [TotalCustomerValue]
,COUNT(Distinct CustomerTransactionId) as [NumberOfTransactions]
FROM [Sales].[CustomerTransactions]
GROUP BY [CustomerID]

Diesen führen wir nun in einer CTE aus, die wir an die obige Ergebnismenge joinen und wiederum eine Tabelle anlegen, in die wir das Resultat schreiben könnten:

DROP TABLE IF EXISTS [Sales].[MainCustomers]

;WITH CTE_SalesPerCustomer AS (
    SELECT 
         [CustomerID]
        ,SUM(TransactionAmount) as [TotalCustomerValue]
        ,COUNT(Distinct CustomerTransactionId) as [NumberOfTransactions]
    FROM [Sales].[CustomerTransactions]
    GROUP BY [CustomerID]
)
SELECT TOP (0) 
       c.[CustomerID]
      ,[CustomerName]
      ,[CreditLimit]
      ,[AccountOpenedDate]
      ,[StandardDiscountPercentage]
      ,[IsStatementSent]
      ,[IsOnCreditHold]
      ,[PaymentDays]
      ,[PhoneNumber]
      ,[FaxNumber]
      ,[DeliveryRun]
      ,[RunPosition]
      ,[WebsiteURL]
      ,[DeliveryAddressLine1]
      ,[DeliveryAddressLine2]
      ,[DeliveryPostalCode]
      ,[DeliveryLocation]
      ,[PostalAddressLine1]
      ,[PostalAddressLine2]
      ,[PostalPostalCode]
      ,[TotalCustomerValue]
      ,[NumberOfTransactions]
  INTO [Sales].[MainCustomers]
  FROM [Sales].[Customers] c
  LEFT JOIN CTE_SalesPerCustomer spc 
  ON c.CustomerID = spc.CustomerID

Im Resultat haben wir nun eine Tabelle, deren Metadaten wir uns ansehen möchten:

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('Sales.MainCustomers')
ORDER BY c.column_id;

Hier seht ihr als letzte Zeile der Ergebnismenge die Einträg TotalCustomerValue als decimal(38,2)-Spalte und NumberOfTransactions als Int-Spalte.

Nun möchten wir es aber noch wilder treiben und an die Tabelle eine berechnete Spalte anfügen:

ALTER TABLE [Sales].[MainCustomers] 
    ADD [AverageTransactionValue] 
    AS [TotalCustomerValue]/CAST(NumberOfTransactions AS Decimal(10,2))

Führen wir nun von dieser Tabelle aus wiederum ein SELECT TOP 0 INTO aus:

SELECT TOP 0 * 
INTO [Sales].[MainCustomersExtended]
FROM [Sales].[MainCustomers] 

Und fragen wir nun die Metadaten beider Tabellen ab:

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE (
           c.object_id = OBJECT_ID('Sales.MainCustomers') 
        OR c.object_id = OBJECT_ID('Sales.MainCustomersExtended')
      ) AND c.name = 'AverageTransactionValue'
ORDER BY c.column_id;

Dann sehen wir zwei identische Spalten. Wenn wir uns nun allerdings die CREATE TABLE Statements beider Tabellen ausgeben lassen, so sehen wir, dass bei MainCustomers die Definition der Computed Column übernommen wurde, während die Spalte bei MainCustomersExtended einfach nur als leere Spalte existiert.

Das bedeutet, dass bei einem SELECT TOP 0 alle die Informationen, die über die reinen Metadaten (welcher Datentyp wird hier gespeichert) hinausgehen, verloren gehen. Das umfasst natürlich auch Informationen wie Filegroups, Indexe, Primärschlüssel und so weiter. Die schnelle Abhilfe, um stukturgleiche Tabellen anzulegen, ist also ein praktisches Hilfsmittel, am Ende muss bei produktiven Datenbanken aber immer noch auf die Details geachtet werden, denn diese tun euch im Betrieb am Ende möglicherweise ganz schön weh.

Referenzen

• Heute mal ohne weiterführende Links.