Import pipe inventory into SQL server

Import pipe inventory into SQL server

Info
This article is for IT professionals familiar with Microsoft SQL Server and related technologies
Warning
The user assumes all responsibilities for any data integrity issues or corruption that may occur when updating a database using anything other than a PypeServer product. It is recommended that a backup of the PypeServerDB database be done before any bulk import.

Pipe table schema

The SQL database PypeServer has several related tables with foreign key constraints for relational data integrity. Pipe data is stored in the Pipes table located in the SQL database PypeServerDB.  Most of the columns in this table are for internal use only and should not be set during import. Product updates may add, rename, or remove any of these columns. 

Columns

These columns may be set when inserting new pipe inventory.

Column and Data Type Description
[BarCode] [nvarchar](max) NULL Stock barcode
[Cost] [money] NULL Stock cost
[Customer] [nvarchar](max) NULL Intended customer
{DateCreated] [date] NULL,
Stock received date
[Job] [nvarchar](max) NULL Intended job
[IsMetric] [bit] NOT NULL Default is false. Set to true/1 when pipe is metric.
[MaterialData] [nvarchar](max) NULL Material heat number
[MaterialType] [nvarchar](max) NULL Material name
[NominalSize] [nvarchar](max) NULL Stock nominal diameter, i.e 6"
[OuterDiameter] [decimal](18, 3) NOT NULL Stock diameter, i.e. 6.625
[PipeLength] [decimal](18, 3) NULL Stock length
[PipeNotes] [nvarchar](max) NULL Stock notes or additional pipe data
[TrueOuterDiameter] [decimal](18, 3) NULL Measured stock diameter, may be different than OuterDiameter
[TrueWallThickness] [decimal](18, 3) NULL Measured stock wall thickness, which may be different than WallThickness
[WallThickness] [decimal](18, 3) NOT NULL Stock wall thickness, i.e. 0.28

Table updates

When updating the Pipes table it is recommended that SQL transactions are used when updating the the Pipes table. This can be done by adding BEGIN TRANSACTION and COMMIT statements to the update process so inventory is either committed or rolled back as one unit of work. 

Inserting a new row will automatically set the primary key column PipeID [bigint] to a database unique value.