Data import with sqlcmd - wiltondb/wiltondb GitHub Wiki

On this page:

• Prerequisites
• Export data to SQL file
• Import data into WiltonDB
• Partial export

Prerequisites

1. Install WiltonDB
2. Setup TDS connection from SSMS
3. Changing TDS port from 1433
4. Schema import with SSMS

Export data to SQL file

We will use AdventureWorks sample database.

The process of exporting data to plain-text SQL file is the same as with schema export just the "Types of data to script" option in "Advanced" settings needs to be changed to "Data only":

Resulting aw_data.sql file is created with size 558 MB.

Import data into WiltonDB

We assume that WiltonDB is installed on the same machine and its TDS port is changed to 7433.

Unlike with schema file, the data SQL file wits size of hundreds of megabytes cannot be easily opened and run from SSMS. Instead we use sqlcmd command line utility to import AdventureWorks data into WiltonDB running it like this (note, it may be better to use SET NOCOUNT ON option to make output less verbose):

sqlcmd -S 127.0.0.1,7433 -U wilton -P wilton -d adventureworks2019 -i aw_data.sql > sqlcmd_log.txt 2>&1

After import is complete we can run the query (taken from here) to check that the calculation results are the same on MSSQL and WiltonDB:

SELECT t2.ProductCategoryID, 
	   t2.Name, 
	   SUM(LineTotal) AS TotalAmount
FROM
    -- Production.ProductCategory and t1 tables are joined to get the ProductCategoryID information of the subcategories
	(SELECT c.ProductCategoryID, 
			c.Name, 
			t1.ProductSubcategoryID, 
			t1.Name AS SubName, 
			t1.ProductID
	 FROM Production.ProductCategory AS c
	 JOIN 
		  -- Production.ProductSubcategory and Production.Product tables are joined to get information of categories
	      (SELECT s.ProductCategoryID, 
				  s.ProductSubcategoryID, 
				  p.ProductID, 
				  p.Name
		   FROM Production.ProductSubcategory AS s
		   JOIN Production.Product AS p
		   ON s.ProductSubcategoryID = p.ProductSubcategoryID) AS t1
	  ON c.ProductCategoryID = t1.ProductCategoryID) AS t2
 JOIN Sales.SalesOrderDetail AS s
 ON t2.ProductID = s.ProductID
 GROUP BY t2.ProductCategoryID, t2.Name
 ORDER BY t2.ProductCategoryID;

Partial export

With large databases it may be not practical to export all the data into a single plain text SQL file. In this case partial export in SSMS can be used:

Alternatively, the bcp utility can be used to perform export/import in binary format.

Next: Data import with bcp