Solved

help with sql select and insert script

Posted on 2013-12-26
25
437 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
  • 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 59

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 59

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 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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 59

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 59

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 59

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 59

Expert Comment

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

Kevin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

839 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