AS400 (linked server object) - Data to SQL Server GDB Table

PaulBarnes2 — Tue, 23 Feb 2016 18:15:58 GMT

I created a new table with schema. I want to INSERT data from the AS400 into my new GDB table. Here's the problem:

The OBJECTID field is an Identity field that auto-populates, so I do not include it in the Insert frunction. However, I receive an error when I run the query because it's "trying" to insert <Null> values into the OBJECTID field. Does anyone have experience trying to do this function, or something similar? Here's my query (cut down for easier viewing):

INSERT INTO [TaxMapping2015].[dbo].[SALES]

( ***[OBJECTID] would go here

[RECNUM]

,[PPIN]

,[PARCEL_ID]

,[SOLDYY]

,[SOLDMM]

,[SOLDDD]

,[LANDPRICE]

,[IMPRPRICE]

,[TOTPRICE]

,...)

SELECT *****some way to populate the identity field would go here

SLH0025,

SLH0020,

SLH0025,

SLH0040,

SLH0050,

SLH0060,

SLH0070,

SLH0080,

SLH0090,

SLH0630 / SLH0090 .....

FROM APSALES.S1081FEB.APSALES.APDSLH01

WHERE SLH0090 > 0 AND SLH0040 >= 2014 AND SLH0110 = 'Y'

ORDER BY SLH0040 DESC

Re: AS400 (linked server object) - Data to SQL Server GDB Table

PaulBarnes2 — Thu, 25 Feb 2016 14:42:56 GMT

I temporarily solved the problem. I recreated the SALES table and set all fields as type text except for RECNUM, which I gave type long integer so I could use that field to populate the OBJECTID. This isn't a great solution, but the linked server object (AS400) automatically assigns the string type to all columns. If I can determine a way to convert the 400 data to the correct types, I will post the solution.

topic AS400 (linked server object) - Data to SQL Server GDB Table in Geodatabase Questions

AS400 (linked server object) - Data to SQL Server GDB Table

Re: AS400 (linked server object) - Data to SQL Server GDB Table