Gist ee08e7fd08f93da779756e132d1c4db3

100%

Gist ee08e7fd08f93da779756e132d1c4db3

✓ Published0🌍 Public

Last edited Apr 10, 2017

Created on Apr 10, 2017

This example demonstrates a SQL Server table-valued parameter (TVP) workflow, showing how to insert multiple rows of key-value pairs into a target table using a single stored procedure call. The code defines a custom `DemoList` type with `Key` and `Value` columns, then creates a stored procedure that accepts this TVP as a read-only parameter and performs an `INSERT` selecting all rows from the TVP. The visualization highlights the data flow from the TVP declaration through the stored procedure’s parameter passing to the final insert operation. This approach simplifies batch data insertion and ensures type safety, making it ideal for passing structured data from application layers to SQL Server.

AI-generated description

Similar vizzes

Loading thumbnail…

Gist faec7eb6c03589ff608bab991699407b

This example demonstrates how to generate and execute a dynamic SQL script in Power Query (M language) to bulk-insert data into a SQL Server table using a table-valued parameter (TVP). It builds a textual TVP declaration and populates it by chunking source rows into sets of 1,000 entries, then constructs a multi-statement script that inserts each chunk and calls a stored procedure. The code relies on M functions like `Table.TransformRows`, `List.Generate`, `Text.Combine`, and `Value.NativeQuery` to programmatically assemble and run the query. The visualization highlights the data flow from a source table to a SQL stored procedure, emphasizing the chunking logic and the use of native query execution.

HHugoberry

70% match

Loading thumbnail…

Gist 8ddacfa41ce888f458863185ecfc400f

This example demonstrates a stored procedure that converts XML data into relational rows for insertion into a database table. The code defines `usp_InsertDemoXML`, which accepts a `varbinary(max)` parameter, casts it to XML, and then uses XQuery’s `nodes()` and `value()` methods to extract `key` and `value` pairs from each `<row>` element. It shows a data-ingestion pattern for parsing structured XML payloads into a normalized key-value table structure. The procedure relies on T-SQL’s built-in XML support rather than external visualization libraries, focusing on server-side data transformation.

HHugoberry

64% match

Loading thumbnail…

Gist 0ca6276fe1534c7941d45e4bb229cc41

This example demonstrates a SQL Server stored procedure that parses incoming JSON data and inserts it into a relational table. It shows how to use the `OPENJSON` function with an explicit schema definition to extract key-value pairs, mapping the JSON’s `Key` and `Value` fields into corresponding integer and varchar columns. The procedure accepts an `nvarchar(max)` parameter for the JSON payload, making it reusable for dynamic data ingestion. The visualization highlights the transformation from a semi-structured JSON format into a normalized table structure, emphasizing the schema-mapping capabilities of T-SQL’s JSON support.

HHugoberry

62% match

Loading thumbnail…

Gist fad134c9dcebee3dc1c56a9cb22de435

This example demonstrates a Power Query M function that serializes a table into a JSON object and then passes that JSON to a SQL Server stored procedure, `usp_InsertDemoJSON`, which unwraps it as a recordset and performs an INSERT operation. The code uses `Text.FromBinary` and `Json.FromValue` to convert the table, then `Value.NativeQuery` to execute the stored procedure with the JSON payload. It shows a data pipeline from a Power BI or Excel data source into a SQL database, relying on the M language’s native JSON handling and SQL Server’s JSON support.

HHugoberry

59% match

Loading thumbnail…

Gist ba39cf5b6349b2131c577e2e046e8654

This example demonstrates a data-driven approach to XML serialization within the Power Query language, showing how database queries can be wrapped in a custom function. The code defines a let expression that calls the `Value.NativeQuery` function, targeting a SQL Server database named "QueryPower" and executing a stored procedure called `usp_InsertDemoXML` with a binary parameter. The procedure receives an XML payload stored in a variable named `dummy2XML`, which is passed as the `xmlBin` argument. This illustrates a pattern for embedding XML data into SQL Server stored procedures through Power Query, highlighting how native database queries can be integrated into data transformation workflows. The example focuses on the parameter-passing mechanism rather than visual rendering.

HHugoberry

58% match

Loading thumbnail…

Gist be32a2ac117700d3dbde6b0824367ba5

This example demonstrates how to create a simple database table visualization from a SQL schema definition. The code defines a `Demo` table with three columns—`ID`, `Key`, and `Value`—and shows how to map those column types and constraints into a visual entity-relationship style diagram. Using the provided `CREATE TABLE` statement as the data source, the visualization renders each column as a node, with the primary key and nullable attributes indicated through distinct visual encodings. The implementation leverages `d3.forceSimulation` for the node layout and `d3.geoPath` for any geometric rendering, while relying on standard DOM elements for interactivity. No additional behavior beyond the table’s structural representation is implied.

HHugoberry

57% match

Loading thumbnail…

Concept for inserting data into SQL Server

This example demonstrates a concept for inserting data into SQL Server by using Power Query’s M language. It shows a table creation script for a demo table with an identity column, a key, and a value, followed by an M query that iterates over a list of key-value pairs. The code uses `List.Accumulate` to loop through the pairs and calls `OleDb.DataSource` with a SQL `INSERT` statement for each iteration, referencing the SQLOLEDB provider to connect to a local database. The result is the total number of affected records from the inserts, though the actual inserted rows are not visualized or returned in the output.

HHugoberry

56% match

Loading thumbnail…

Exporting data out of Power Query via AdoDb route

This example demonstrates how to export data out of Power Query by directly inserting rows into a SQL Server table through an ADO.NET connection. It uses the `Sql.Database` function to connect to a local server instance and the `Value.NativeQuery` API to execute a parameterized `INSERT` statement, passing literal values for the `@key` and `@value` parameters. The code shows a concrete way to push query results into an external database, bypassing Excel’s native output options. The visible data source is a SQL table named `[dbo].[Demo]`, and the approach highlights ADO.NET’s role in bridging Power Query transformations to persistent storage.

HHugoberry

52% match