INSERT into an identity column not allowed on table variables
. [TestMethod]
public void InsertUsers__ThroughProcessUserSP__3People()
{
DataTable usersInDB = PeopleBuilderForInjection.GenerateDT_WithUsers();
SqlCommand theCommand = new SqlCommand();
theCommand.Connection = _theConnection;
theCommand.CommandType = CommandType.StoredProcedure;
theCommand.CommandText = "[dbo].[User_Process]";
SqlParameter paramTU = new SqlParameter();
paramTU.ParameterName = "@allHFRUsers";
paramTU.SqlDbType = SqlDbType.Structured;
paramTU.TypeName = "[dbo].[UserType]";
paramTU.Value = usersInDB;
theCommand.Parameters.Add(paramTU);
_theConnection.Open();
SqlDataReader reader = theCommand.ExecuteReader();
.....
--------------------------CREATE TYPE [sitel].[UserType] AS TABLE(
[id] [int] NOT NULL,
[EmployeeID] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Gender] [nvarchar](15) NULL,
[JobTitle] [nvarchar](255) NULL,
[PreferredLanguage] [nvarchar](50) NULL,
[Email] [nvarchar](255) NULL,
[HomePhone] [nvarchar](50) NULL,
[ExtensionAttribute5] [nvarchar](1024) NULL,
[MobileNumber] [nvarchar](50) NULL,
[StreetAddress] [nvarchar](1024) NULL,
[City] [nvarchar](100) NULL,
[ZipCode] [nvarchar](15) NULL,
[State] [nvarchar](100) NULL,
[Country] [nvarchar](50) NULL,
[Active] [bit] NULL DEFAULT ((0)),
[BusinessPhone] [nvarchar](50) NULL,
[ExtensionAttribute1] [nvarchar](1024) NULL,
[ExentsionAttribute2] [nvarchar](1024) NULL,
[ExtensionAttribute27] [nvarchar](1024) NULL,
[ExtensionAttribute3] [nvarchar](1024) NULL,
[Province] [nvarchar](100) NULL,
[Description] [nvarchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
ALTER PROCEDURE [sitel].[User_Process]
(
@allHFRUsers [sitel].[UserType] readonly
)
AS
declare @ProcessResult as [sitel].[ProcessReportType] /* pour les logs */
declare @ii int, @iiMax int /* iterateurs limites */
declare @nbInserted int = 0, @nbUpdated int = 0, @nbDeleted int = 0
declare @nbErrors int = 0, @nbWarnings int = 0
-- for current user received in entry
declare @CurrentUser as [sitel].[UserType]
-- for user
declare @HFRUCUser as [sitel].[UserType]
-- assigning index range for looping through users received.
select @ii=MIN(id), @iiMax =MAX(id) from @allHFRUsers
-- loop for controlling if it's an update, delete or a new person
While @ii<=@iiMax
BEGIN
-- get an user - from received param - and set it in @CurrentUser
delete @CurrentUser
insert into @CurrentUser ([EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage] /*, ... */ )
select [EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage]
from @allHFRUsers
where id=@ii
-- get the Current user out of HFRUniversalConnector.User and assign to @HFRUCUser
delete @HFRUCUser
insert into @HFRUCUser ([EmployeeID],[FirstName], [LastName], [Gender], [JobTitle], [PreferredLanguage] /*, ... */ )
select [EmployeeID], [FirstName], [LastName] , [Gender], [JobTitle], [PreferredLanguage] --....
from [dbo].[User]
where [EmployeeID] = (select top 1 [EmployeeID] from @CurrentUser)
.....
insert into @CurrentUser (@ii, [EmployeeID],
where [EmployeeID] = (select top 1 [EmployeeID] from @CurrentUser)
where [EmployeeID] = @ii
thank you for your asnwer.
sorry for the explanation if they are not clear enough :/
actually i'm trying to pass unittest against the store proc. the store proc has it's adhoc type with the identity(1,1) as first column for the id.
is there a possibility to keep the table type (with it's id identity(1,1) as it is ?
thank you in advance for further help.
toshi