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