Manchmal braucht man Dinge...
if object_id('dbo.OrderStringparts') is not NULL drop function dbo.OrderStringparts
go
create function dbo.OrderStringparts
(
@String nvarchar(max),
@Delimiter nvarchar(max)
)
returns nvarchar(max)
as
begin
declare @out nvarchar(max);
with tab(Part) as -- http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum
(
select
substring(@String, n, charindex(@delimiter, @String + @delimiter, n) - n)
from dbo.Numbers(1, len(@String)) -- http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly
where
substring(@delimiter + @String, n, len(replace(@delimiter, ' ', '_'))) = @delimiter
),
string(string) as
(
select
@Delimiter + Part
from tab
order by
Part
for xml path('')
)
select
@out = substring(string, 2, 2147483647)
from string
return @out
end
go
Viele Grüße
Christoph Ingenhaag
Eine Möglichkeit, eine fortlaufende Summe zu erzeugen ist die Nachfolgende.
Voraussetzung ist eine fortlaufende Nummer ohne Lücken und aus Gründen der Performance ein Index auf die fortlaufenden Nummer.
Da man diese Voraussetzungen selten vorfindet, lege ich eine entsprechende temporäre Tabelle an, die diesen Voraussetzungen genügt.
Aus den Testdaten
begin try drop table ##test, #test end try begin catch end catch
select
n as ID,
cast(abs(checksum(newid())) * 1.0 / 10000000 as int) as Betrag
into ##test
from dbo.numbers(1,100000) -- http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly
where
n % 3 = 1 or
n % 5 = 1
go
erzeuge ich die temporäre Tabelle:
select
row_number() over (order by ID) as RNR,
ID,
Betrag
into #test
from ##test
go
create unique clustered index cuidx on #test(RNR)
go
Und hier die Abfrage
;with posten as
(
select
RNR,
ID,
Betrag,
1 as MinRNR,
count(*) over (partition by 1) as MaxRNR
from #test
),
zeilenweise as
(
select
RNR,
ID,
Betrag,
Betrag as Summe,
MinRNR,
MaxRNR
from posten
where
RNR = MinRNR
union all
select
z.RNR + 1,
p.ID,
p.Betrag,
z.Summe + p.Betrag,
z.MinRNR,
z.MaxRNR
from zeilenweise z
inner join posten p
on
p.RNR = z.RNR + 1
where
z.RNR < z.MaxRNR
)
select
ID,
Betrag,
Summe
from zeilenweise
option (maxrecursion 0)
Die Ausführungsgeschwindigkeit ist bis ca. 100.000 Datensätze durchaus annehmbar...
Viele Grüße
Christoph Ingenhaag
http://www.sommarskog.se/arrays-in-sql-2008.html#Workarounds
http://www.sommarskog.se/arrays-in-sql-2005.html#fixed-length
Aber es wird dort nur beispielhaft auf eine Liste von ganzen Zahlen eingegangen. Hier im Beispiel aber von ganzen Datensätzen.
Im nachfolgenden Beispiel wird nur ein TVP verwandt. Ziel im Beispiel ist es, Personendatensätze in eine Tabelle zu schreiben. Damit es nicht allzu langweilig ist, sollen vorhandene Sätze geändert und nicht vorhandene Sätze eingefügt werden.Das wird mittels MERGE und dem übergebenen TVP in einer Stored Procedure realisiert, für die eine weitere Stored Procedure als Wrapper verwendet wird, in der wiederum eine Funktion aufgerufen wird, die den BLOB als Parameter erhält und daraus den TVP zurück gibt, der dann an die aufzurufende Stored Procedure weitergegeben wird.
Achtung, man benötigt man die Numbers Funktion von Steve Kass:
http://stevekass.com/2006/06/03/how-to-generate-a-sequence-on-the-fly/
bzw. eine Numbers Tabelle (siehe z.B. http://www.glorf.it/blog/2010/08/17/sql-talk/number-helper-table )
Nun zu erst einmal die Tabelle und den passenden TVP erzeugen:
if object_id('dbo.Persons') is not NULL drop table dbo.Persons
if object_id('dbo.tvpTest') is not NULL drop procedure dbo.tvpTest
if object_id('dbo.fn_get_tvpPerson') is not NULL drop function dbo.fn_get_tvpPerson
if object_id('dbo.tvpTestWrapper') is not NULL drop procedure dbo.tvpTestWrapper
begin try drop type dbo.tvpPersons end try begin catch end catch
go
create table dbo.Persons
(
ID int primary key clustered,
Vorname nvarchar(30),
Name nvarchar(30),
GebDat datetime
)
go
create type dbo.tvpPersons as table
(
ID int primary key clustered,
Vorname nvarchar(30),
Name nvarchar(30),
GebDat datetime
)
go
Die Frage, ob ein TVP einen Primary Key benötigt und ggf. sogar unique constraints, muss man anhand des Verwendungszweckes klären. Hier im Beispiel sollen Datensätze bei Vorhandensein aktualisiert werden, sonst eingefügt werden. Nachfolgend die entsprechende Stored Procedure:
create proc dbo.tvpTest
@tvp as dbo.tvpPersons readonly
as
set nocount on
set xact_abort on
merge into dbo.Persons t
using @tvp s
on s.ID = t.ID
when matched then
update set
t.ID = s.ID,
t.Vorname = s.Vorname,
t.Name = s.Name,
t.GebDat = s.GebDat
when not matched then
insert
(
ID,
Vorname,
Name,
GebDat
)
values
(
s.ID,
s.Vorname,
s.Name,
s.GebDat
);
go
da die Client API nicht mit TVPs umgehen kann, nun die Wrapper Prozedur, die knapp gehalten ist, da die Funktionalität in der aufzurufenden Funktion liegt (der Parameter @p ist hier als output definiert um sich im SQL Server Profiler den Inhalt des Parameters als Textdata anschauen zu können)
create proc dbo.tvpTestWrapper
@p varbinary(max) output
as
set nocount on
set xact_abort on
declare @tvp as dbo.tvpPersons
insert into @tvp
select * from dbo.fn_get_tvpPerson(@p)
exec dbo.tvpTest @tvp
go
Die Funktion hat die Aufgabe, den BLOB in die entsprechenden Häppchen zu schneiden und in die entsprechenden Datentypen zu konvertieren. Was der Client da binär anliefert, lässt sich nicht immer einfach von binary zu dem gewünschten Datentypen konvertieren. Hier im Beispiel muss die Byte Reihenfolge beim INT umgedreht werden und das Datum als String übertragen werden. Natürlich könnte man alles als String übertragen, würde damit aber auch entsprechend mehr Bandbreite benötigen. Da bekannt ist, wie viele Bytes ein Datensatz im BLOB hat, ist mit Hilfe der Numbers Funktion das Teilen in einzelne Sätze kein Problem.
create function dbo.fn_get_tvpPerson
(
@p varbinary(max)
)
returns @tvp table
(
ID int primary key clustered,
Vorname nvarchar(30),
Name nvarchar(30),
GebDat datetime
)
as
begin
if datalength(@p) % 140 = 0
begin
insert into @tvp
select
convert
(
int,
substring(@p, 4 + (n -1) * 140, 1) +
substring(@p, 3 + (n -1) * 140, 1) +
substring(@p, 2 + (n -1) * 140, 1) +
substring(@p, 1 + (n -1) * 140, 1)
),
convert(nvarchar(30), substring(@p, 5 + (n -1) * 140, 60)),
convert(nvarchar(30), substring(@p, 65 + (n -1) * 140, 60)),
convert(nvarchar(16), substring(@p, 125 + (n -1) * 140, 16))
from dbo.Numbers(1, datalength(@p)/140)
end
return
end
Als Test-Client fungiert hier VBA mit ADO 2.8.
Nachfolgend die Klasse TVP4ADO28
Private adoStream As ADODB.stream
Private Type typRecord
ID As Long '4 Byte
Vorname As String * 30 '60 Byte
Name As String * 30 '60 Byte
GebDat As String * 8 '16 Byte
End Type
Private Type typBinRecord
record(1 To 140) As Byte
End Type
Private Sub Class_Initialize()
Set adoStream = New ADODB.stream
adoStream.Type = adTypeBinary
adoStream.Mode = adModeReadWrite
adoStream.Open
End Sub
Public Sub AddRecord(ID As Long, Vorname As String, Name As String, GebDat As Date)
Dim t As typRecord
Dim r As typBinRecord
Dim s() As String
s() = Split(CStr(GebDat), ".")
t.ID = ID
t.Vorname = Vorname
t.Name = Name
t.GebDat = s(2) & s(1) & s(0)
LSet r = t
adoStream.Write r.record
End Sub
Public Property Get GetStream() As ADODB.stream
adoStream.Position = 0
Set GetStream = adoStream
End Property
Private Sub Class_Terminate()
adoStream.Close
Set adoStream = Nothing
End Sub
und hier der Aufruf (Der Application Name im Connection String dient zum einfachen Filtern im SQL Server Profiler)
Public Sub Test()
Dim conn As New ADODB.Connection
Dim com As New ADODB.Command
Dim i As Long
Set tvp = New TVP4ADO28
For i = 1 To 10000
tvp.AddRecord i, "Klaus", "Müller", "09.06.1957"
Next i
With conn
.ConnectionString = _
"Provider=SQLNCLI10;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=True;" & _
"Initial Catalog=Test;" & _
"Data Source=MeinServer\MeineInstanz;" & _
"Application Name=MeineApplikation"
.CursorLocation = adUseClient
.IsolationLevel = adXactReadCommitted
.Mode = adModeReadWrite
.Open
End With
With com
.CommandType = adCmdStoredProc
.CommandText = "dbo.tvpTestWrapper"
.ActiveConnection = conn
.NamedParameters = False
.Parameters.Append .CreateParameter("@p", adVarBinary, adParamInputOutput, _
tvp.GetStream.Size, tvp.GetStream.Read)
Dim t As Single
t = Timer
.Execute Options:=adExecuteNoRecords
t = Timer - t
End With
Debug.Print t
conn.Close
Set com = Nothing
Set conn = Nothing
Set tvp = Nothing
End Sub
Viele Grüße
Christoph Ingenhaag
]]>
Der Einfachheit halber habe ich für das Beispiel die Stored Procedure sp_help verwendet:
Rückgabe in eine Tabelle schreiben
select *
into #t1
from openrowset
(
'SQLNCLI',
'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',
'exec sp_help;'
) as a;
select * from #t1
go
drop table #t1
go
oder einfach als Funktion weiterverwenden
create function dbo.fn_help()
returns table
as
return
(
select *
from openrowset
(
'SQLNCLI',
'Server=MeinServer\MeineInstanz;Trusted_Connection=yes;',
'exec sp_help;'
) as a
);
go
select * from dbo.fn_help()
drop function dbo.fn_help
Dieses Vorgehen sollte aber die Ausnahme bleiben, ad hoc eben.
Ergänzend siehe auch
http://www.insidesql.org/blogs/uricken/2010/08/20/verwendung-von-udf-auf-linked-server
Viele Grüße
Christoph Ingenhaag
]]>
IN Operator
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)
Bekannt ist, das man mit dem IN Operator prüfen kann, ob Werte in einer Spalte vorhanden sind:
where Spalte1 in (1,2,3)
das es auch andersherum geht, ist eher unbekannt. Obwohl nach Onlinehilfe die test_expression "nur" ein gültiger Ausdruck sein muss:
where 1 in (Spalte1, Spalte2, Spalte3)
siehe hierzu auch:
http://sqlblog.com/blogs/denis_gobo/archive/2009/04/09/13186.aspx
Gültige Ausdrücke sind
{ constant | scalar_function | [ table_name. ] column | variable
| ( expression ) | ( scalar_subquery )
| { unary_operator } expression
| expression { binary_operator } expression
| ranking_windowed_function | aggregate_windowed_function
}
also where (Ausdruck) in (Spalte1, Spalte2, Spalte3)
Aber der Ausdruck darf nicht mehr als eine Zeile und Spalte zurückgeben (skalarer Ausdruck). Mit einem kleinen Trick kann man aber trotzdem mehrere Werte übergeben. Dazu ein Beispiel mit Testdaten:
begin try drop table #t end try begin catch end catch;
select
identity(int, 1, 1) id,
*
into #t
from
(
values
(0,0,0,0,0,0), (1,0,0,0,0,0), (0,1,0,0,0,0),
(0,0,1,0,0,0), (0,0,0,1,0,0), (0,0,0,0,1,0),
(0,0,0,0,0,1), (2,0,0,0,0,0), (0,2,0,0,0,0),
(0,0,2,0,0,0), (0,0,0,2,0,0), (0,0,0,0,2,0),
(0,0,0,0,0,2), (3,0,0,0,0,0), (0,3,0,0,0,0),
(0,0,3,0,0,0), (0,0,0,3,0,0), (0,0,0,0,3,0),
(0,0,0,0,0,3)
) t(sp1, sp2, sp3, sp4, sp5, sp6);
Ein Crossjoin mit den zu überprüfenden Werten funktioniert:
select distinct id
from #t,
(
select arg
from (values (1), (2)) a(arg)
) t
where
arg in (sp1, sp2, sp3, sp4, sp5, sp6);
Noch ein anderes Beispiel, mit dem man alle Lottozahlen 6 aus 49 inkl. Zusatzzahl erzeugen kann (vielleicht weiß ja jemand, wie man damit den Jackpot knacken kann ;-) )
;with n as
(
select 1 n
union all
select n + 1
from n
where
n < 49
),
z as
(
select 1 z
union all
select z + 1
from z
where
z < 10
)
,lotto as
(
select
n1.n as Zahl_1,
n2.n as Zahl_2,
n3.n as Zahl_3,
n4.n as Zahl_4,
n5.n as Zahl_5,
n6.n as Zahl_6,
z.z as Zusatzzahl
from n n1, n n2, n n3, n n4, n n5, n n6, z
where
n1.n not in (n2.n, n3.n, n4.n, n5.n, n6.n) and
n2.n not in (n1.n, n3.n, n4.n, n5.n, n6.n) and
n3.n not in (n1.n, n2.n, n4.n, n5.n, n6.n) and
n4.n not in (n1.n, n2.n, n3.n, n5.n, n6.n) and
n5.n not in (n1.n, n2.n, n3.n, n4.n, n6.n) and
n6.n not in (n1.n, n2.n, n3.n, n4.n, n5.n) and
n2.n > n1.n and
n3.n > n2.n and
n4.n > n3.n and
n5.n > n4.n and
n6.n > n5.n
)
select top(1) * -- aus 139838160 Zeilen
from lotto
order by
newid();
Viele Grüße
Christoph Ingenhaag
]]>Hiermit soll nicht aufgezeigt werden, dass dies der richtige Weg ist, eine solche Aufgabenstellung zu lösen, sondern die Möglichkeit der gemeinsamen Anwendung der neuen Features in T-SQL. Quasi als Ideengeber. Wer tatsächlich solche Problemstellungen hat, sollte sich mit dem Themenkreis rund ums Datawarehouse auseinandersetzen.
Für das Beispiel wird die Beispieldatenbank AdventureWorks2008 OLTP benötigt (http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=16040)
Zuerst einmal der Code, um aufzuräumen:
use AdventureWorks2008; set nocount on; go if object_id('Production.Refresh', 'P') is not NULL drop proc Production.Refresh; go if object_id('Production.MatProd_data', 'U') is not NULL drop table Production.MatProd_data; go if object_id('Production.MrgAlertLog', 'U') is not NULL drop table Production.MrgAlertLog; go if object_id('Production.Prod_data', 'V') is not NULL drop view Production.Prod_data; go if not exists(select * from sys.dm_sql_referencing_entities('dbo.grpset', 'type')) and exists(select * from sys.types where name = 'grpset' and is_table_type = 1) drop type dbo.grpset; go
Ich stelle solchen Code gerne an den Beginn und an das Ende von Beispiel-/Testcode, damit ich auch bei Fehlern das Beispiel nach Korrektur ohne weiteres Ausführen von Bereinigungscode starten kann und in jedem Fall nach vollständiger Ausführung alle Spuren beseitigt habe.
Was hier auffällt ist die neue DMV im untersten IF-Konstrukt um den Type zu löschen, wenn vorhanden. Diese DMV sys.dm_sql_referencing_entities sowie die DMV sys.dm_sql_referenced_entities sind neu in SQL Server 2008 und geben verlässliche(!) Auskunft über Abhängigkeiten zwischen Objekten zurück. Hierüber könnte man auch einen kurzen Artikel schreiben, ein Blick in die BOL tut‘s aber locker auch.
So, leicht vom Thema abgekommen - nun wieder zurück zur Aufgabenstellung:
Zuerst brauchen wir Testdaten, daher auch der Zugriff auf die AdventureWorks2008.
create view Production.Prod_data with schemabinding as select PC.Name Category ,PS.Name Subcategory ,P.Name Product ,datepart(yy, T.TransactionDate) TA_year ,datepart(qq ,T.TransactionDate) TA_quarter ,datepart(mm, T.TransactionDate) TA_month ,datepart(dw, T.TransactionDate) TA_weekday ,T.Quantity Qty ,T.ActualCost Cost from Production.Product P inner join Production.ProductSubcategory PS on P.ProductSubcategoryID = PS.ProductSubcategoryID inner join Production.Productcategory PC on PC.ProductCategoryID = PS.ProductCategoryID inner join Production.TransactionHistoryArchive T on T.ProductID = P.ProductID; go
Ob diese Daten in diesen Verknüpfungen Sinn machen, ist unerheblich. Hauptsache Beispieldaten.
Mit 2 kombinierten Rollups erfüllen wir einfach mal die Anforderung, „die man sinnbildlich uns für die Auswertung herangetragen hat“:
select grouping_id(TA_weekday, TA_month, TA_quarter, TA_year, Product, Subcategory, Category) Grpid ,cast(hashbytes('MD5', isnull(cast(TA_weekday as varchar), '') + isnull(cast(TA_month as varchar), '') + isnull(cast(TA_quarter as varchar), '') + isnull(cast(TA_year as varchar), '') + isnull(Product, '') + isnull(Subcategory, '') + isnull(Category, '')) as varbinary(100)) HashVal ,Category ,Subcategory ,Product ,TA_year ,TA_quarter ,TA_month ,TA_weekday ,sum(Qty) sumQty ,avg(Cost) avgCost into Production.MatProd_data from Production.Prod_data group by rollup(Category, Subcategory, Product), rollup(TA_year, TA_Quarter, TA_month, TA_weekday);
Damit wir nicht nur die einzelnen Gruppierungen wieder finden, sondern auch die unterschiedlichen Werte der einzelnen Gruppen eindeutig wieder erkennen können ohne alle Spalten miteinander vergleichen zu müssen, wird zusätzlich ein Hashwert über die Spalten gebildet (ohne Aggregate). Ob Hashwert oder Checksum oder ein ähnliches Verfahren entscheidet sich danach, ob die Daten mit GROUPING_ID und dem berechneten Wert eindeutig unterscheidbar sind. Nicht nur um dies sicherzustellen erzeugen wir einen geclusterten eindeutigen Index.
create unique clustered index cuiProductionMatProd_data_GrpidHashVal on Production.MatProd_data(Grpid, HashVal);
Ein zusammengesetzter Primärschlüssel auf diese beide Spalten ist auch eine gute Idee, ich aber verzichte in solchen Szenarien grundsätzlich darauf, da es sich hierbei ja „nur“ um Auswertungen handelt. HashVal ist zwar der eindeutig selektivere Wert (im Idealfall eindeutig), da aber nicht über HashVal zugriffen wird, sondern über Grpid, die oben angegebene Reihenfolge im Index.
So, die Daten liegen jetzt also vor. Weiter geht’s:
Jetzt brauchen wir noch einen Type, der die Daten enthalten wird, die zur Änderung der Auswertung erstellt werden.
create type dbo.grpset as table ( Grpid int ,HashVal varbinary(100) ,Category nvarchar(100) ,Subcategory nvarchar(100) ,Product nvarchar(100) ,TA_year int ,TA_quarter int ,TA_month int ,TA_weekday int ,sumQty int ,avgCost money ,primary key clustered(GrpID, HashVal) );
Da man bei einem Type keinen Index definieren kann, hier der Umweg über die Definition eines Primärschlüssels.
Da wir nach Änderungen im Anschluss einfach feststellen wollen, schreiben wir ein Protokoll über die Änderungen. Dazu legen wir eine entsprechende Tabelle an:
create table Production.MrgAlertLog ( Grpid int ,HashVal varbinary(100) ,Action nvarchar(10) ,I_sumQty int ,I_avgCost money ,D_sumQty int ,D_avgCost money );
Der Name der Tabelle zeugt von Wichtigkeit und beeindruckt vielleicht den einen oder anderen Kollegen. Zumindest kann man damit glänzen, wenn man sich damit brüstet, wertvolle Informationen darin entdeckt zu haben ;-)
Damit der Join zu den materialisierten Daten zügig durchgeführt wird, erzeugen wir einen dazu analogen Index.
create unique clustered index cuiProductionMrgAlertLog_GrpidHashVal on Production.MrgAlertLog(Grpid, HashVal); go
So, jetzt fehlt ja noch die Prozedur, die die eigentliche Arbeit erledigen soll und die Arbeit, die man ihr gibt.
Fangen wir mit dem Arbeiter an:
create proc Production.Refresh @GrpSet dbo.GrpSet readonly as set nocount on begin try insert into Production.MrgAlertLog select Grpid ,HashVal ,Action ,I_sumQty ,I_avgCost ,D_sumQty ,D_avgCost from ( merge into Production.MatProd_data T using @GrpSet S on T.Grpid = S.GrpID and T.HashVal = S.HashVal when matched and ( T.sumQty <> S.sumQty or T.avgCost <> S.avgCost ) then update set T.sumQty = S.sumQty, T.avgCost = S.avgCost when not matched by target then insert ( Grpid ,HashVal ,Category ,Subcategory ,Product ,TA_year ,TA_quarter ,TA_month ,TA_weekday ,sumQty ,avgCost ) values ( S.Grpid ,S.HashVal ,S.Category ,S.Subcategory ,S.Product ,S.TA_year ,S.TA_quarter ,S.TA_month ,S.TA_weekday ,S.sumQty ,S.avgCost ) output S.Grpid ,S.HashVal ,$Action ,inserted.sumQty ,inserted.avgCost ,deleted.sumQty ,deleted.avgCost ) O (Grpid, HashVal, Action, I_sumQty, I_avgCost, D_sumQty, D_avgCost) where I_sumQty < isnull(D_sumQty, 0) + 10 or I_avgCost > isnull(D_avgCost, 0) + 10 or Action = 'INSERT'; end try begin catch return error_number(); end catch return 0; go
Nehmen wir hier einmal an, dass wir nur dann Logeinträge wünschen, wenn sich die Aggregierungen um einen angegebenen Wert geändert haben (+10) oder neue Gruppierungen hinzugefügt wurden. Einfach haben wir es uns mit der Fehlerbehandlung gemacht, die die Arbeit dem Aufrufer aufhalst.
Nun noch die Arbeit für unsere Prozedur erzeugen, an die Prozedur übergeben und schauen was herauskommt. Zuerst müssen wir natürlich wieder Testdaten erzeugen. Wer kein Problem damit hat, die AdventureWorks zu verändern kann die Transaktion auch weglassen. Das hier Nützliche am TABLE TYPE ist, dass er wie auch Variablen vom Typ Table von der Transaktion unbeinflusst bleibt und somit das ROLLBACK nur die Änderung der Daten in der AdventureWorks zurückrollt.
begin tran insert into Production.TransactionHistoryArchive select TransactionID + 100000 ,ProductID ,ReferenceOrderID ,ReferenceOrderLineID ,dateadd(qq, 1, TransactionDate) TransactionDate ,TransactionType ,Quantity + 2 ,ActualCost ,ModifiedDate from Production.TransactionHistoryArchive T where datepart(yy ,T.TransactionDate) = 2003 and datepart(qq ,T.TransactionDate) = 3 update Production.TransactionHistoryArchive set ActualCost = ActualCost + 10 where datepart(yy ,TransactionDate) = 2003 and datepart(qq ,TransactionDate) = 3 declare @grpset as dbo.grpset; insert into @grpset select grouping_id(TA_weekday, TA_month, TA_quarter, TA_year, Product, Subcategory, Category) Grpid ,cast(hashbytes('MD5', isnull(cast(TA_weekday as varchar), '') + isnull(cast(TA_month as varchar), '') + isnull(cast(TA_quarter as varchar), '') + isnull(cast(TA_year as varchar), '') + isnull(Product, '') + isnull(Subcategory, '') + isnull(Category, '')) as varbinary(100)) HashVal ,Category ,Subcategory ,Product ,TA_year ,TA_quarter ,TA_month ,TA_weekday ,sum(Qty) sumQty ,avg(Cost) avgCost from Production.Prod_data group by rollup(Category, Subcategory, Product), rollup(TA_year, TA_Quarter, TA_month, TA_weekday); rollback
Hiermit wurde das 3. Quartal 2003 geändert und das 4. Quartal 2003 hinzugefügt.
Der Aufruf der Prozedur der mit obigem Code zusammenerfolgen muss, da sonst @grpset nicht mehr im Scope ist, folgt hier:
declare @r int; exec @r = Production.Refresh @grpset; if @r <> 0 select 'Error:', @r;
Dann schauen wir uns mal an, was alles aktualisiert wurde:
select L.Action ,M.Category ,M.Subcategory ,M.Product ,M.TA_year ,M.TA_quarter ,M.TA_month ,M.TA_weekday ,L.I_sumQty ,L.I_avgCost ,L.D_sumQty ,L.D_avgCost from Production.MrgAlertLog L inner join Production.MatProd_data M on L.Grpid = M.Grpid and L.HashVal = M.HashVal where Action = 'UPDATE'
Zum Schluss wieder aufräumen und falls durch Fehlerfall eine Transaktion noch offen sein sollte, ein Rollback machen:
if object_id('Production.Refresh', 'P') is not NULL drop proc Production.Refresh; go if object_id('Production.MatProd_data', 'U') is not NULL drop table Production.MatProd_data; go if object_id('Production.MrgAlertLog', 'U') is not NULL drop table Production.MrgAlertLog; go if object_id('Production.Prod_data', 'V') is not NULL drop view Production.Prod_data; go if not exists(select * from sys.dm_sql_referencing_entities('dbo.grpset', 'type')) and exists(select * from sys.types where name = 'grpset' and is_table_type = 1) drop type dbo.grpset; go if @@trancount > 0 rollback
Wie bereits geschrieben: dieses Beispiel soll nur als Denkanstoss oder Anreiz dienen, sich mit den neuen Features von T-SQL auseinander zu setzen. Ob dies als Lösung Sinn macht, ermisst sich aus den Anforderungen und für die Anforderung eines Beispiels mag ich die Lösung ;-).
]]>Zur Erinnerung: Die OUTPUT Klausel gibt die einem DML Statement (UPDATE, INSERT, DELETE, MERGE) Einträge der systemeigenen Tabellen inserted und deleted zurück (Besonderheiten bei MERGE im nächsten Artikel). Diese können direkt an den Datenkonsumenten zurückgegeben werden oder in eine Variable vom Typ Table geschrieben werden, um dort weiterverarbeitet zu werden, beispielsweise um Logs zu schreiben.
INSERT over DML kürzt den letzteren Weg ab. Statt die Daten in eine Variable vom Typ Table zu schreiben und von dort aus in eine Logging Tabelle, kann mittels INSERT over DML direkt in die Logging Tabelle geschrieben werden. Zusätzlich können die Daten mit WHERE gefiltert werden. Dies ist eine atomare Operation.
Zuerst einmal Testdaten erzeugen
use tempdb create table dbo.products ( id int, price money ) create table dbo.prodlog ( id int, diff money, ) declare @prodlog table ( id int, diff money ) insert into dbo.products values (1, 1), (2, 1.23), (3, 2.05), (4, 1.99), (5, .5) --Die Transaktion dient dazu, die Daten wieder im Ursprungszustand wiederherzustellen begin tran --SQL Server 2005 Vorgehensweise update dbo.products set price *= 1.25 output inserted.id id ,inserted.price - deleted.price diff into @prodlog insert into dbo.prodlog select id ,diff from @prodlog where diff > .5 select * from dbo.prodlog rollback --SQL Server 2008 Vorgehensweise insert into dbo.prodlog select id ,diff from ( update dbo.products set price *= 1.25 output inserted.id id ,inserted.price - deleted.price diff ) U where diff > .5 select * from dbo.prodlog --Aufräumen drop table dbo.products ,dbo.prodlog
Hieraus lassen sich gedanklich die interessantesten Anwendungsmöglichkeiten konstruieren, aber leider erlauben die Einschränkungen, außer den Anwendungsfälle des Loggings/Auditing/Change Managements sowie die vereinfachte Bearbeitung von „Slowly changing dimensions“ im Zusammenhang mit MERGE als BI Thema, eigentlich nichts. (Da diese Einschränkungen sehr umfangreich sind, bitte in den BOL nachschlagen).
Der nächsten Artikel handelt von MERGE.
]]>