Problemfeldbeschreibung

Beim Arbeiten mit Daten im Microsoft SQL Server kommt man gelegentlich an den Punkt, an dem man sich das „Drehen“ von Tabellendaten als nützlich entpuppt. Anwendungsgebiete sind u.a. die Prognoserechnung, Zeitreihenanalyse oder einfach Matrizenmultiplikation.

Nehmen wir z.B. den folgenden Datensatz, er soll gedreht werden:

200901     1    2009    0    30
200902     2    2009    58    16
200903     3    2009    520    15
200906     6    2009    780    15
200809     9    2008    0    7
200909     9    2009    260    8
200810    10    2008    0    

Der unerfahrene Datenbankentwickler beginnt dann häufig mit der Implementierung von langsamen Cursorskripten á la

DECLARE @compcode varchar(255)
DECLARE @market varchar(255)
DECLARE @salesdiv varchar(255)
DECLARE @material varchar(255)
DECLARE @month int
DECLARE @year int
DECLARE @bruttoumsatz varchar(255)
DECLARE @amount varchar(255)
DECLARE @spalte varchar(255)

DECLARE mmg_cursor CURSOR FOR
SELECT compcode, market, salesdiv, material, month, year, bruttoumsatz, amount
FROM cube WHERE CAST(CAST(year as varchar)+(CASE WHEN month<10 THEN ('0'+CAST(month as varchar)) ELSE CAST(@month as varchar) END ) as int) >=200610

OPEN mmg_cursor

FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material,
@month, @year, @bruttoumsatz, @amount

WHILE @@FETCH_STATUS = 0
BEGIN

SET @spalte=CAST(@year as varchar)+(CASE WHEN @month<10 THEN ('0'+CAST(@month as varchar)) ELSE CAST(@month as varchar) END ) EXEC('UPDATE hypercube SET ['+@spalte+']='+@amount+' WHERE [compcode]='''+@compcode+''' AND [salesdiv]='''+@salesdiv+''' AND [market]='''+@market+''' AND [material]='''+@material+''' ') FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material, @month, @year, @bruttoumsatz, @amount END DEALLOCATE mmg_cursor [/javascript]

Ansatz

Es bedarf keines Benchmarks oder einer Messung um festzustellen, dass diese Methode sehr zeitaufwändig und unzeitgemäß ist. Microsoft rät von solchen Cursor-Skripten ab und rät von der Nutzung der PIVOT-Funktion, die an Performance und Handhabung kaum zu überbieten ist.

Lösung

Das folgende SQL-Skript dreht die Tabelle mit PIVOT (Die MS SQL Server-Datenbank muss hierzu mind. auf MSSQL2005-Kompatibilität gestellt werden.

declare @columns varchar(max)
declare @columnsoben varchar(max)
declare @count int
declare @max int
declare @current int
declare @sql nvarchar(max)
declare @t table(id int identity(1,1), datekey int)
insert into @t select distinct datekey from vglobe2 order by 1
set @columns = “
set @columnsoben = “
set @count = 1
set @max = (select max(id) from @t)
while (@count <= @max) begin set @current = (select datekey from @t where id = @count) set @columns = @columns + '[' + cast(@current as varchar(6)) +'], ' set @columnsOben = @columnsOben + '[' + cast(@current as varchar(6)) +'],' + '0 as [' + cast(@current as varchar(6)) +'stat],' set @count = @count+1 end set @columns = substring(@columns, 1, len(@columns)-1) -- letztes komma set @columnsOben = substring(@columnsOben, 1, len(@columnsOben)-1) -- letztes komma set @sql = 'SELECT Artikel, ' + @columnsoben + N' FROM (SELECT artikel, bruttoumsatz , datekey FROM vglobe2) p PIVOT ( sum(bruttoumsatz) FOR datekey in (' + @columns + N') ) AS pvt ORDER BY 1' print @sql exec sp_executesql @sql [/sql]

Problem

Eine große CSV Datei soll in eine Tabelle importiert werden. Hierbei sollen nicht alle Felder importiert werden.

Ansatz

Die Verwendung des Kommandos BULK INSERT wurde von Microsoft für sehr große Dateien vorgesehen, um den SQL Server ohne die Verwendung von langsamen Cursor-Iterationen zu füllen

Lösung

Vorgehensweise

Erstellen einer Tabelle mit CREATE TABLE in die importiert wird
Zunächst erstellt man eine Tabelle in die importiert werden soll, hier wird eine fiktive Tabelle RAWBEIL erstellt.

CREATE TABLE [dbo].[rawbeil](
	[gruppe] [varchar](255) NULL,
	[bezeichnung] [varchar](255) NULL,
	[feldname] [varchar](255) NULL,
	[sapcolumn] [varchar](50) NULL,
	[saptable] [varchar](50) NULL
)

Erstellen einer IMPORT-Datei für die Feldzuweisung

Der RECORD-Tag muss den kompletten Aufbau der CSV-Datei enthalten; der ROW-Teil darf enthält die Tabellenspalten, in die importiert wird. FIELD ID wird auf COLUMN SOURCE gemappt. ROW/COLUMN SOURCE kann auch Lücken enthalten, wenn man Felder aus der CSV Datei weglassen möchte..

<?xml version="1.0" ?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<RECORD>
		<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255"/>
		<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="10" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="11" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="12" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="13" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="255" />
		<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\r\n" 
                  MAX_LENGTH="255" />
	</RECORD>
	<ROW>
		<COLUMN SOURCE="1" NAME="gruppe" xsi:type="SQLVARYCHAR" />
		<COLUMN SOURCE="2" NAME="bezeichnung" xsi:type="SQLVARYCHAR" />
		<COLUMN SOURCE="3" NAME="feldname" xsi:type="SQLVARYCHAR" />
		<COLUMN SOURCE="4" NAME="sapcolumn" xsi:type="SQLVARYCHAR" />
		<COLUMN SOURCE="5" NAME="saptable" xsi:type="SQLVARYCHAR" />
	</ROW>
</BCPFORMAT>

SQL Anweisung

CODEPAGE wird verwendet um das Umlaute-Problem zu beheben, FIRSTROW ist die erste Spalte ab der aus CSV importiert wird (meistens will man den Header ausschließen).

DECLARE @sql nvarchar(4000)
SET @sql =
'BULK INSERT rawbeil FROM ''D:\web\karpbjde\import\db.csv''
WITH (
FORMATFILE = ''D:\web\karpbjde\import\beilmade.fmt'',
CODEPAGE=''raw'',
FIRSTROW = 2
)'
EXEC(@sql)

BULK INSERT mit Typecast in einen bestimmten Datentyp

/* Bulk-Import von einer .CSV-Datei in eine Tabelle incl. der Datentypen
* es werden Zahlen im Vormat
* xxx,yy
* xxx.yy
* -xxx,yy
* -xxx.yy
* xxx,yy-
* xxx.yy
* unterstützt. Diese werden als Money-Datentyp convertiert
* Bulk-Import
*
* gewidmet an Björn K.von Nico Gerbig
*/ 

-- Zuerst als Varchar lesen, dann richtig
create table ordersbulk (col1 varchar(50), col2 varchar(50), col3 varchar(50));
create table ordersbulk2 (col1 varchar(50), col2 money, col3 money) 

-- Eintragen in Tabelle mit Varchars
BULK INSERT OrdersBulk
    FROM 'c:\test.csv.txt'
    WITH
    (
        --FIRSTROW = 2,
        FIELDTERMINATOR = ';',
        ROWTERMINATOR = '\n'
    ) 

-- und Kopieren in die Tabelle mit den richtigen Datentypen
insert into ordersbulk2 (col1, col2, col3)
    select col1,
          dbo.ConvertVarcharToMoney(col2),
          dbo.ConvertVarcharToMoney(col3)
    from ordersbulk
go
-- testen
select * from ordersbulk2
go 

-- dafür notwendige funktion (c)  -=e!ninio=-
create function ConvertVarcharToMoney(@abc varchar(255))
    returns money
as begin
    declare @invert money;
    declare @index int;
    declare @newabc money;
    set @invert = 1;
    set @abc = Replace(@abc,',','.')
    set @abc = Replace(@abc,' ','')
    set @index = CHARINDEX('-',@abc)
    if @index > 0
    begin
        set @invert = -1;
        set @abc = Replace(@abc, '-', '')
    end
    set @newabc = CAST(@abc as money) * @invert
    return @newabc
end
go 

-- Test für die Funktion selbst
select dbo.ConvertVarcharToMoney('13,41')
select dbo.ConvertVarcharToMoney('13.41')
select dbo.ConvertVarcharToMoney('-13,41')
select dbo.ConvertVarcharToMoney('13,41-')
select dbo.ConvertVarcharToMoney('- 13,41')
select dbo.ConvertVarcharToMoney('13,41 -')