Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

help with sql select and insert script

Avatar of Steve Hougom
Steve HougomFlag for United States of America asked on
Microsoft SQL Server
25 Comments1 Solution486 ViewsLast Modified:
I need help writing a sql script that does a openquery select from the as400 and inserts into a sql server table.

Here is the select:
SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93274 AND 93335')
ORDER BY FUPDAT

I need to insert those rows into another table:  (see mapping below)
INSERT INTO [tbFuelTransaction]
           ([TransactionId]
           ,[TransactionDate]
           ,[UnitNumber]
           ,[DriverCode]
           ,[CardNumber]
           ,[Category]
           ,[Quantity]
           ,[Price]
           ,[Amount]
           ,[LocationId]
           ,[CreateDate]
           ,[CreateUserId]
           ,[ChangeDate]
           ,[ChangeUserId]
           ,[ChangeHostName]
           ,[ChangeProcessName])
     VALUES
           (<TransactionId, int,>
           ,<TransactionDate, datetime,>
           ,<UnitNumber, varchar(10),>
           ,<DriverCode, varchar(10),>
           ,<CardNumber, varchar(50),>
           ,<Category, varchar(50),>
           ,<Quantity, decimal(9,2),>
           ,<Price, decimal(9,3),>
           ,<Amount, decimal(9,2),>
           ,<LocationId, int,>
           ,<CreateDate, datetime,>
           ,<CreateUserId, varchar(50),>
           ,<ChangeDate, datetime,>
           ,<ChangeUserId, varchar(50),>
           ,<ChangeHostName, varchar(255),>
           ,<ChangeProcessName, varchar(500),>)


Here are the mappings:
(As400 table)                            (Sql Table)
I93File.FuelHST                       tbFuelTransaction              Notes
----------------------                   ------------------------             -----------
FUPDAT + FUTIME                  TransactionDate         (there is a function on the sql server  that                                   needs to be used for julian date its called:  ICCDateToSQLDate

FUTYPE                                    Category                             FUTYPE of D = Category 'ULSD'
FUTRAC                                    UnitNumber
FUQTY                                      Quantity                          FUQTY is rounded to tenths but Quantity is rounded to hundredths
FUAMT                                     Amount
FUSCOD                                   LocationId
FUSCTY                                     CityName
FUSST                                        State
FUSNAM                                   Name                             FUSNAM and Name should be compared to make sure they are a match before inserting.  Just some sort of wildcare like comparison is good enough.

For now just worry about the mapped columns i will fill in the rest.

Hope  someone can help.