Issue:
The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error:
Msg 2627, Level 14, State 1, Procedure sp_ETL_S1_tbl1, Line 40
Violation of PRIMARY KEY constraint 'PK__tbl1__EE1C17C10200000'. Cannot insert duplicate key in object 'S1.tbl1'. The duplicate key value is (0).
The statement has been terminated.
Hypothesis:
I believe this is due to the first stored procedure containing the DROP TABLE
statements, and for some reason it doesn't get called first when all the stored procedures are run together.
Offending Code:
mssql
execute_sps.sql
USE db1;
GO
EXEC sp_ETL_db1_CreateSchemas;
EXEC sp_ETL_dbo_tbl1;
EXEC sp_ETL_dbo_tbl2;
Procedures above defined:
Stored procedure to create schema and tables.
sp_ETL_db1_CreateSchemas.sql
USE db1;
DROP PROCEDURE sp_ETL_db1_CreateSchemas;
GO
CREATE PROCEDURE sp_ETL_db1_CreateSchemas
AS
GO
DROP TABLE S1.tbl1
DROP TABLE S1.tbl2
DROP SCHEMA S1;
GO
CREATE SCHEMA S1;
GO
CREATE TABLE S1.tbl1(
SalesGroupID varchar(20)
NOT NULL PRIMARY KEY
, SalesGroupType int
, SalesGroup varchar(20)
)
;
GO
CREATE TABLE S1.tbl2(
[NameID] int
NOT NULL PRIMARY KEY
, [FirstName] varchar(50)
, [MiddleName] varchar(50)
, [LastName] varchar(50)
, [LastUpdated] datetime
)
;
Stored procedures to ETL data from (olddb.tbl1, olddb.tbl2)
to (db1.S1.tbl1 , db1.S1.tbl2)
USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl1;
GO
CREATE PROCEDURE sp_ETL_S1_tbl1
AS
SET NOCOUNT ON
IF object_id('tempdb.dbo.#SalesGroup') IS NOT NULL
BEGIN DROP TABLE #SalesGroup END
SELECT
[SalesGroupID]
, [SalesGroupType]
, [SalesGroup]
INTO #SalesGroup
FROM olddb.tbl1
;
INSERT INTO db1.S1.tbl1(
[SalesGroupID]
, [SalesGroupType]
, [SalesGroup]
)
SELECT
[SalesGroupID]
, [SalesGroupType]
, [SalesGroup]
FROM #SalesGroup
USE db1;
GO
DROP PROCEDURE sp_ETL_S1_tbl2;
GO
CREATE PROCEDURE sp_ETL_S1_tbl2
AS
SET NOCOUNT ON
IF object_id('tempdb.dbo.#Names') IS NOT NULL
BEGIN DROP TABLE #Names END
SELECT
[NameID]
, [FirstName]
, [MiddleName]
, [LastName]
, [LastUpdated]
INTO #Names
FROM olddb.tbl2
;
INSERT INTO db1.S1.tbl2(
[NameID]
, [FirstName]
, [MiddleName]
, [LastName]
, [LastUpdated]
)
SELECT
[NameID]
, [FirstName]
, [MiddleName]
, [LastName]
, [LastUpdated]
FROM #Names
IF EXISTS
andTRUNCATE
if it does? Does the behaviour change if you putGO
in between eachEXEC
in execute_sps.sql? – KevH Aug 19 '19 at 22:30sp_ETL_dbo_tbl1
procedure. – Laughing Vergil Aug 19 '19 at 23:12sp_
prefix has a special meaning in SQL Server. Do not use it for user code. See here. – Michael Green Aug 20 '19 at 9:34