Introduction Staging data is an important part of the ETL (or ELT) process. The general concept is this: before you bring your data into your database, data store, data mart or data warehouse, you need to pre-load it. This is the process of extracting and then loading before moving it on, otherwise known as “staging”. … Continue reading Strategies for Staging Data to SQL
Introduction In SQL Server you might know you can select the first N rows of a query by using TOP(N), but you can also do the same and skip rows using OFFSET FETCH. Skipping Rows To skip rows you can use the OFFSET <N> ROWS syntax like so... SELECT OrderId ,CustomerID ,OrderDate FROM [Sales].[Orders] ORDER … Continue reading Limit and Offsetting Rows in SQL Server
In a previous article I demonstrated how you can import XML data using the OPENROWSET() function. In this article I'm going to show how you can do the same but with a JSON file and the OPENJSON() function. Example JSON [ { "id": 1, "product_name": "white paint", "status": "in stock", "orders": [ { "order_id": 1, … Continue reading Import JSON data to SQL Server
In this article I'm going to provide an example of how you can read data from an XML file in SQL Server.To achieve this we're going to use OPENROWSET and OPENXML functions. In this example I'm using the XML sample books which you can obtain from here. Open Row Set Using the open row set … Continue reading Import XML data to SQL Server
Creating a slowly changing dimension (SCD) is an essential part of Data Warehousing. There are many tools for the job but I prefer to do the work using T-SQL against the database rather than using something like SSIS. This will generally involve staging the data from source and then merging the source copy against target. … Continue reading Create a Slowly Changing Dimension Type 2 with T-SQL
