Using TSQL with bulk insert to create a table in Sql Server 2014 via Sql Server Management Studio.
Encountered the following error message upon specifying "Non NULL" in the CREATE TABLE section for a composite primary key that is comprised of three fields; "JurisdictionCode", "AccountNo", "SequenceNbr."
[Bold]
What is the preferred method to setup a composite primary key during Bulk Insert?[/Bold]
The three fields (JurisdictionCode, AccountNo, SequenceNbr) uniquely identify the record and is primarily used to join with another table, "EquipmentTax", that has the same composite primary keys. Of course, there are additional lookup tables as well.
One may argue that all tables should have auto-generated PKs. It appears that if the primary key(s) change, then an auto-generated identity column may have value... However,I have already designated the three fields as composite primary keys...
[Bold]
So, would a solution be the addition of an identity column and set it as the primary key?[/Bold]
For example, Of the 20,000 records that I receive during the month of June 2017, if there are 1,000 records that have a nonsensical value in any one of the primary key fields, the 1000 records will not be allowed in my table, "Equipment_2017", because there will be a NULL in one of the primary key fields.
As this is the first Sql Server database that I have setup, my objective is to initially setup the database correctly and efficiently as possible.
[Bold]What modifications to the code below is necessary to accomplish the objective?[/Bold]
Encountered the following error message upon specifying "Non NULL" in the CREATE TABLE section for a composite primary key that is comprised of three fields; "JurisdictionCode", "AccountNo", "SequenceNbr."
Code:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'AccountNo', table 'EquipmentLocation.dbo.Equipment_2016'; column does not allow nulls. INSERT fails.
The statement has been terminated.[Bold]
What is the preferred method to setup a composite primary key during Bulk Insert?[/Bold]
The three fields (JurisdictionCode, AccountNo, SequenceNbr) uniquely identify the record and is primarily used to join with another table, "EquipmentTax", that has the same composite primary keys. Of course, there are additional lookup tables as well.
One may argue that all tables should have auto-generated PKs. It appears that if the primary key(s) change, then an auto-generated identity column may have value... However,I have already designated the three fields as composite primary keys...
[Bold]
So, would a solution be the addition of an identity column and set it as the primary key?[/Bold]
For example, Of the 20,000 records that I receive during the month of June 2017, if there are 1,000 records that have a nonsensical value in any one of the primary key fields, the 1000 records will not be allowed in my table, "Equipment_2017", because there will be a NULL in one of the primary key fields.
As this is the first Sql Server database that I have setup, my objective is to initially setup the database correctly and efficiently as possible.
[Bold]What modifications to the code below is necessary to accomplish the objective?[/Bold]
Code:
USE master ;
GO
DROP DATABASE EquipmentLocation ;
GO
CREATE DATABASE EquipmentLocation
go
Use EquipmentLocation
Go
CREATE TABLE #EquipmentData1 (
[JurisdictionCode] [varchar] (60 ) NULL,
[AccountNo] [varchar] (60 ) NULL,
[SequenceNbr] [varchar] (60 ) NULL,
[Zipcode] [varchar] (60 ) NULL,
[SaleAmount] [varchar] (60 ) NULL,
[SaleDate] [varchar] (60 ) NULL,
)
CREATE TABLE [Equipment_2017](
[JurisdictionCode] [varchar] (5 ) NOT NULL, --<<<< Changed from "NULL" to "NOT NULL" to create composite primary key
[AccountNo] [varchar] (45 ) NOT NULL, --<<<< Changed from "NULL" to "NOT NULL" to create composite primary key
[SequenceNbr] int NOT NULL, --<<<< Changed from "NULL" to "NOT NULL" to create composite primary key
[Zipcode] [varchar] (9) NULL,
[SaleAmount] int NULL,
[SaleDate] date NULL, ---format is (YYYYMMDD)
PRIMARY KEY ([JurisdictionCode], [AccountNo], [SequenceNbr])
)
BULK INSERT #EquipmentData1
FROM 'C:\EquipmentLocation\Data\201706EquipmentData.txt'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
Select * from #EquipmentData1
insert into Equipment_2017
select
RTRIM(LTRIM([JurisdictionCode])),
RTRIM(LTRIM([AccountNo])),
RTRIM(LTRIM([SequenceNbr])),
CASE
When rtrim(ltrim([Zipcode])) LIKE '[0-9][0-9][0-9][0-9][0-9]' then rtrim(ltrim([Zipcode]))
When rtrim(ltrim([Zipcode])) LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' then substring(rtrim(ltrim([Zipcode])),1 , 5)
else null
end as [Zipcode],
CASE
WHEN ISNUMERIC(RTRIM(LTRIM([SaleAmount]))) = 0
THEN Null
ELSE RTRIM(LTRIM([SaleAmount]))
END as [SaleAmount],
CASE
when len([SaleDate]) = 8
and isdate([SaleDate]) = 1
and rtrim(ltrim([SaleDate])) like '[0-9][0-9][0-9][0-9][0-1][0-2][0-3][0-9]'
then convert(date,[SaleDate], 112)
else cast(null as date)
end as [SaleDate]
from #EquipmentData1
where
[SaleDate] between convert(date, '2017-05-01') and convert(date, '2017-05-31')
and RTRIM(LTRIM([SaleAmount])) is not null
OR RTRIM(LTRIM([SaleDate])) is not null
OR RTRIM(LTRIM([SequenceNbr])) is not null
Select * from Equipment_2017