Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

help with sql select and insert script

Posted on 2013-12-26
25
Medium Priority
?
446 Views
Last Modified: 2013-12-30
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.
0
Comment
Question by:hougie40
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 8
  • 2
25 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39740604
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)
0
 

Author Comment

by:hougie40
ID: 39740644
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39740670
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:hougie40
ID: 39740848
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.
0
 

Author Comment

by:hougie40
ID: 39740880
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.

fuel locations
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39741705
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.
0
 

Author Comment

by:hougie40
ID: 39741825
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.
0
 

Author Comment

by:hougie40
ID: 39741857
Here are 3 screens of sample rows from each table.

as400

as400 fuel data

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

fuel trans table (where the data gets inserted to)
Fuel Locations table which "could" be used to cross check the Name/LocationId first before inserting a duplicate row into fueltrans table in sql.

fuel location lookup table
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39741910
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.
0
 

Author Comment

by:hougie40
ID: 39742056
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.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39742090
Definitely.  It is one reason I was suggesting the import to a staging table first, so you have an opportunity to make sure the data was coming out how you want.  One easy way is to perform the query as a SELECT first:

/*
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


Remove the /* and */ from the INSERT portion of the code when you are ready to INSERT.
0
 

Author Comment

by:hougie40
ID: 39742142
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.

TransactionId in FuelTransaction Table on sql server
0
 

Author Comment

by:hougie40
ID: 39742191
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!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39742228
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.
0
 

Author Comment

by:hougie40
ID: 39742229
Thats all thats left I think.  Ive matched up the columns and values.  No more not null column required errors!
0
 

Author Comment

by:hougie40
ID: 39742236
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
0
 

Author Comment

by:hougie40
ID: 39742242
Here is what the fueltrans quantity column type is in sql server.

fueltransaction quantity column
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39742257
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).
0
 

Author Comment

by:hougie40
ID: 39742462
Gotcha.  Im waiting for confirmation on this but at this point probably a new hero of mine!
0
 

Author Comment

by:hougie40
ID: 39744900
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39745910
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.
0
 

Author Comment

by:hougie40
ID: 39746081
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
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39746125
you can always remove duplicate rows in another SQL statement


see this document for instructions

http://support.microsoft.com/kb/139444
0
 

Author Closing Comment

by:hougie40
ID: 39747146
Kevin Cross' help was outstanding.  What this site is all about.  Thank you very much sir!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39747363
You are most welcome!
Best regards and happy coding,

Kevin
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question