Solved

help with sql select and insert script

Posted on 2013-12-26
25
431 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now