Skip to content

Instantly share code, notes, and snippets.

@Hugoberry

Hugoberry/TVP.m

Last active Apr 11, 2017
Embed
What would you like to do?
let
//building the Table Value Constructor for a record
rows = (t) => Table.TransformRows(t, each "("&Text.From([key])&",'"&[value]&"')"),
//group entries in sets of 1000 because Table Value Constructor have a limitation of 1000 entries
values = List.Generate(()=>0,
(i)=>i<1000,
(i)=>i+1,
(i)=>Text.Combine(rows(Table.Range(dummy,i*1000,1000)),",")),
//adding the declaration of TVP and the call to SP that uses TVP
TVP = Text.Combine({"declare @p1 dbo.DemoList #(lf)"}&
List.Transform(values, each "insert into @p1 values"&_&"#(lf)")
&{"#(lf)exec dbo.usp_InsertDemoTVP @TVP=@p1"}),
//call the lenghty SQL statement
out = Value.NativeQuery(Sql.Database(".", "QueryPower"),TVP)
in
out
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.