Solved

help with sql select and insert script

Posted on 2013-12-26
25
435 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

919 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

16 Experts available now in Live!

Get 1:1 Help Now