Link to home
Start Free TrialLog in
Avatar of Steve Hougom
Steve HougomFlag for United States of America

asked on

help with sql select and insert script

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.
Avatar of Ess Kay
Ess Kay
Flag of United States of America image

INSERT INTO [tbFuelTransaction]
           ([TransactionId]
           ,[TransactionDate]
           ,[UnitNumber]
           ,[DriverCode]
           ,[CardNumber]
           ,[Category]
           ,[Quantity]
           ,[Price]
           ,[Amount]
           ,[LocationId]
           ,[CreateDate]
           ,[CreateUserId]
           ,[ChangeDate]
           ,[ChangeUserId]
           ,[ChangeHostName]
           ,[ChangeProcessName])
     VALUES
           (SELECT TransactionId
           ,TransactionDate
           ,UnitNumber
           ,DriverCode
           ,CardNumber
           ,Category
           ,Quantity
           ,Price
           ,Amount
           ,LocationId
           ,CreateDate
           ,CreateUserId
           ,ChangeDate
           ,ChangeUserId
           ,ChangeHostName
           ,ChangeProcessName   FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93274 AND 93335')
ORDER BY FUPDAT)
Avatar of Steve Hougom

ASKER

Im trying to do something like but with my mappings in mind above....im not sure how to handle the filtering/where/case when stuff from the as400

INSERT INTO [Equipment].[tbFuelTransaction]
([UnitNumber],[Category],[Quantity],[Amount],[LocationId],[CreateDate],[CreateUserId],[ChangeDate],[ChangeUserId],[TransactionDate])
Values(SELECT * FROM OPENQUERY(AS400, 'SELECT FUTRAC, FUTYPE, FUQTY, FUAMT,FUSCOD,  FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93244 AND 93335')
ORDER BY FUPDAT, GetDate, myusername, GetDate, myusername, callsomefunctionforTranDate())

Open in new window

You want to use an INSERT INTO ... SELECT.

INSERT INTO [tbFuelTransaction] (
/*
Column list here;
Exclude TransactionID if this is IDENTITY/PRIMARY KEY column.
*/
)
SELECT ICCDateToSQLDate(/* FUPDAT + FUTIME */)
     , FUTRAC
     /* Convert FUTYPE to Category. */
     , CASE FUTYPE WHEN 'D' THEN 'ULSD' END

     , FUSNAM
FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93274 AND 93335')
ORDER BY FUPDAT
;

As shown above, you can handle the specific transformations within the SELECT portion of the query.  A few notes, though.  You mention comparing FUSNAM and Name, which suggests you need to merge data versus INSERT every time.  Therefore, you may want to consider INSERT INTO a staging table (@table_variable or #temp_table) first, then you can handle the INSERT/UPDATE accordingly.  If this is SQL 2008 or higher, you can use MERGE.

Other notes are in code above like not including TransactionID if this is the identity column.  I hope that makes sense.
The TransactionId is not a key field so will be left blank for now.



I tried to do it like this so far but not sure if if this is going to work.  Will look at your example.  Thanks.

if (1=11)
begin
drop table mytable
--go

CREATE TABLE #mytable
(
[UnitNumber] [varchar](10) NOT NULL,
[Category] [varchar](50) NOT NULL,
[Quantity] [decimal](9, 2) NOT NULL,
[Amount] [decimal](9, 2) NOT NULL,
[LocationId] [int] NOT NULL,
[UpdateDate] char(5) not null,
[UpdateTime] char(4) not null,
CreateDate datetime null
)

INSERT #mytable ([UnitNumber],[Category],[Quantity],[Amount],[LocationId],[UpdateDate],[UpdateTime])
SELECT * FROM OPENQUERY(AS400, 'SELECT FUTRAC, FUTYPE, FUQTY, FUAMT,FUSCOD, FUPDAT , FUTIME   FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93244 AND 93335')
end

--update #mytable

--Case When Category = D Then 'ULSD'
--Quantity = Round(Quantity,hundredths)
--TransactionDate = I93File.dbo.ICCDateToSQLDate(UpdatedDate) + I93File.dbo.ICCDateToSQLDate(UpdatedTime)

--NOW I NEED TO INSERT TO tbFuelTransaction not sure how


--delete mytable

Open in new window


As you can see im struggling with this.
So using your example ive got this.

INSERT INTO [tbFuelTransaction] (
--Column list here
TransactionDate,
UnitNumber,
Category,
Quantity,
Amount,
LocationId,
CreateDate,
CreateUserId
)
SELECT ICCDateToSQLDate(FUPDAT + FUTIME)
     , FUTRAC
     /* Convert FUTYPE to Category. */
     , CASE FUTYPE WHEN 'D' THEN 'ULSD' END
     ,FUQTY
	 ,FUAMT
	 ,FUCOD
	 , GetDate()
	 ,'myusername'
FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93274 AND 93335')
ORDER BY FUPDAT
;

Open in new window


I have a table that could be used to lookup and compare the Name column in fuelLocation table to the FUSNAM column from the 400.  Based on LocationId.  LocationId also exists in FuelTransaction and is called FUCOD in 400.

User generated image
If I understand correctly, you could JOIN fuelLocation on [Name] = FUSNAM.  You can then INSERT both the [LocationID] and [Name] columns.  Regarding how you INSERT into FuelTransaction table, it appears you have the correct syntax above.  You could just perform the second insert from #mytable, after cleaning up data as necessary.
I know your right Kevin.  Im just having problems setting up the structure of it especially involving the temp table.  For some reason those things are the #bane of my existence.  

And yes the FuelLocation table can be used to sort of cross check the FUSNAM column to make certain its the right transaction.  Apparently some rows have already been moved over to the FuelTransaction table via another system.  So whether its name or whatever column im trying hard to make sure I dont "merge" a duplicate over to the FuelTransaction table.  

There is no set rule how I compare to make sure its not already in Fuel Transaction.  I would think amount of gas and gallons of gas and location would be a dead giveaway.  For example truck abc stops at local convenience store to get gas on december 1st.

Its possible Im too hung up on the Location Cross check.
Here are 3 screens of sample rows from each table.

as400

User generated image

Fuel Transactions Sql server table which gets inserted into from 400 (merged)

User generated image
Fuel Locations table which "could" be used to cross check the Name/LocationId first before inserting a duplicate row into fueltrans table in sql.

User generated image
Example:
INSERT INTO [tbFuelTransaction] (
--Column list here
TransactionDate,
UnitNumber,
Category,
Quantity,
Amount,
LocationId,
CreateDate,
CreateUserId
)
SELECT ICCDateToSQLDate(a.FUPDAT + a.FUTIME)
     , a.FUTRAC
     /* Convert FUTYPE to Category. */
     , CASE a.FUTYPE WHEN 'D' THEN 'ULSD' END
     , a.FUQTY
	 , a.FUAMT
	 , a.FUCOD
     , l.LocationId
	 , GETDATE() 
	 , 'myusername'
FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93274 AND 93335') a
JOIN fuellocations l ON l.[Name] = a.FUSNAM
/* Can filter records that already exist in fuel transactions table. */
WHERE NOT EXISTS (
    SELECT 1
    FROM tbFuelTransaction t
    /* Use criteria that accurately matches existing/duplicate rows. */
    WHERE t.UnitNumber = a.FUTRAC
    AND t.LocationId = l.LocationId
    AND t.TransactionDate = ICCDateToSQLDate(a.FUPDAT + a.FUTIME)
)
ORDER BY FUPDAT
;

Open in new window


I hope the above helps.  It demonstrates a JOIN to the locations table to get the location ID, which you can use to INSERT into fuel transactions.  In addition, to avoid duplicates, you can filter the INSERT to only the rows that do not already exist.  Therefore, you can use location ID, unit number, transaction date, or whatever combination makes a record unique that you have at the time of import.
Kevin,

Is there a way to make it create a sql insert script instead of doing the insert so I can look at the results apparently because this is production I have to figure out a way to eyeball them first.
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kevin,

Here is the error that I got when testing.


Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'TransactionId', table 'tbFuelTransaction'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Apparently TransactionId is a non-null column in tbFuelTransaction.

User generated image
I was just told that 0 for the TransactionId would be fine.

Also is there anyway I can convert/round the FUQTY to hundredths vs tenths?

Thanks again!
Yes, it appears you have Id as the IDENTITY and TransactionId as a separate integer value.  I was going to ask where that value comes from, but I see you just replied it can be a static 0 every time.  Regarding the FUQTY, note that if you have a value rounded to tenths in AS/400 like 3.3, then "rounding" it on SQL to a lower precision will not change the value.  You would need the original number for that.  Once you have the original number, you can use ROUND(FUQTY, 2).  However, I think the issue likely is the display not the data.  I am guessing you want to see 3.30 instead of 3.3.  You can handle that with CONVERT during SELECT.  With the column data type set to DECIMAL(9,2), though, it should be fine by default.
Thats all thats left I think.  Ive matched up the columns and values.  No more not null column required errors!
Regarding qty.  If you look at the screens of the sample data look at the fueltransaction column quantity.  Thats how it ends up in sql server.  So either sql server handles it automagically via its type (precision 2 or whatever) OR i have to do some kind of select convert like you said.  Thanks again
Here is what the fueltrans quantity column type is in sql server.

User generated image
You will be fine because the FUQTY in I93FILE.FUELHST converts implicitly without rounding to the Quantity in tbFuelTransaction.  In other words, 78.2 is the same as 78.20.  If you want to explicitly convert the value, you can use CONVERT(DECIMAL(9,2), FUQTY).
Gotcha.  Im waiting for confirmation on this but at this point probably a new hero of mine!
So my sql guy changes the query saying that the join to location table wasnt needed.  Also he suggested more checks for duplicity.  Below is his version.  Im wondering how much more duplicity validation I can even add to the where exists.  Amount maybe but where else?  Let me know if you have any thoughts esp on duplicity checking.

BEGIN TRANSACTION;

INSERT INTO [tbFuelTransaction] (
    TransactionId,
    TransactionDate,
    UnitNumber,
    DriverCode,
    CardNumber,
    Category,
    Quantity,
    Price,
    Amount,
    LocationId,
    CreateDate,
    CreateUserId,
    ChangeDate,
    ChangeUserId
)
SELECT 0 --transactionid
    , ICCDateToSQLDate(a.FUPDAT, a.FUTIME) -- transactiondate
    , LTRIM(RTRIM(a.FUTRAC)) -- unitnumber
    , '' -- driver code
    , '' -- cardnumber
    , CASE a.FUTYPE WHEN 'D' THEN 'ULSD' END -- category
    , a.FUQTY
    , 0.0 -- price
    , a.FUAMT -- amount
    , a.FUSCOD -- locationid
    , GETDATE() -- createdate
    , 'myusername' -- createuser
    , GetDate() -- changedate
    , 'myusername' -- changeuser
FROM OPENQUERY(AS400, 'SELECT * FROM I93FILE.FUELHST WHERE FUPDAT BETWEEN 93244 AND 93335') a
/* Can filter records that already exist in fuel transactions table. */
WHERE NOT EXISTS (
    SELECT 1
    FROM [tbFuelTransaction] t
    /* Use criteria that accurately matches existing/duplicate rows. */
    WHERE t.UnitNumber = LTRIM(RTRIM(a.FUTRAC))
    AND t.LocationId = a.FUSCOD
    AND ROUND(t.Quantity, 1) = a.FUQTY
    AND t.TransactionDate = ICCDateToSQLDate(a.FUPDAT, a.FUTIME)
)
ORDER BY FUPDAT;

--ROLLBACK TRANSACTION;

Open in new window

Whatever helps.  The key is you want the minimum amount of columns that identifies a duplicate row.  You will know you need more if, for example, you have a transaction for same unit number, location, and quantity but the amount is different.  In that case, the first four matches do not uniquely identify the row.  I hope that makes sense.
Yeah I only added 1 additional validation on the amount.   Couldnt really find anything else that would identify a duplicate row.  Soon as I find out more will hopefully close this out.
Thanks
you can always remove duplicate rows in another SQL statement


see this document for instructions

http://support.microsoft.com/kb/139444
Kevin Cross' help was outstanding.  What this site is all about.  Thank you very much sir!
You are most welcome!
Best regards and happy coding,

Kevin