TMSLoader with an Identity Column

[GNiessen]

Is it possible to insert data into a table that has an Identity field as the first column?

I have a Table:

Code: Select all

CREATE TABLE [dbo].[LipidCoord](
	[LipidCoord] [int] IDENTITY(1,1) NOT NULL,
	[LipidID] [int] NOT NULL,
	[LipidX] [numeric](15, 8) NOT NULL,
	[LipidY] [numeric](15, 8) NOT NULL,
 CONSTRAINT [PK_LipidCoord] PRIMARY KEY CLUSTERED 
(
	[LipidCoord] ASC,
	[LipidID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Which I am filling by query:

Code: Select all

INSERT INTO dbo.LipidCoord (LipidID, LipidX, LipidY)
VALUES(:pLipidID, :pLipidX, :pLipidY)

But I want to use the loader to improve performance for the 1000+ entries.

but I am getting an "Unspecified Error" in the PutData when I call the PutColumnData for the second row.

Code: Select all

  for Idx := 1 to FLines.Count-13 do        //Skips header
  begin
    Sender.PutColumnData(0,Idx,Null);
    Sender.PutColumnData(1,Idx,FLipidID);
    DataLine.DelimitedText := FLines[Idx+12];
    X := StrToFloatDef(DataLine[0],-1);
    Y := StrToFloatDef(DataLine[1],-1);
    Sender.PutColumnData(2,Idx,X);
    Sender.PutColumnData(3,Idx,Y);
  end;

[ViktorV]

Thank you for the information. We will investigate the behavior of SDAC and inform you about the results.
As a workaround, to make server insert a value for the IDENTITY field automatically, set the TMSLoader.Options.KeepIdentity property to False. And in the TMSLoader.OnPutData event handler, set a non-Null value for this field.
If you need to insert a user value for the IDENTITY field, then before calling the TMSLoader.Load method, set the TMSLoader.Options.KeepIdentity property to True, and in the TMSLoader.OnPutData event handler, set this field to the needed value.