Update a Table from a Query Result.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update a Table from a Query Result.

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-08-08 : 05:58:23

Hi all,

Can someone please help.

I have a stored procedure in SQL 7 that conatains a query that returns certain data from my SQL Database .I need the results of the Query to be created into a table and then the table to be updated ( Refreshed ) every couple of minutes.This table is then displayed on a webpage.

I know that I can schedule the SP to run but what is the best way ( Syntax) to update the data from a result set into a table.

On other SQL Forums it was suggested that I do the following.
1) Insert your data into a staging table (tmptabA)
2) DROP your data table (tabA)
3) Rename your staging table to your match the name of your data table (RENAME tmptabA -> tabA).

This works OK But I have no Control over the Data types as SQL Decides what the data lookslike and creates a table accordingly.. Also any Empty String Data returned ('') is not interpreted as NULL..
I need Empty strings returned as Null..

Should I Start by creating an Empty table with my own data types and then updating this table ..

What is the best procedure to do this as this must be a common issue with Websites that use SQL as their backend?

Thx for any help
Ray..

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-08 : 06:48:39

You can always use CAST or CONVERT to explicitly specify what datatypes you want in your resultant table. As far as '' being interpreted as NULL goes, er why would it be? You can use NULLIF here if required.
I'd be inclined to just drop the original table then do a SELECT INTO (within a transaction so you can rollback if there's any problem).
Alternatively, you could truncate the existing table before inserting new rows. Experiment and see which performs better. Either way, I don't see the benefit of going down the route you've been advised elsewhere.

Mark

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-08-08 : 07:58:42

Hiya,
Thx for the reply..
Yeah I need to have Nulls instead of empty strings..
Does Nullif have to be used on a column by column basis?
ie:SELECT NULLIF (business_ch, '') AS Business_CH, NULLIF (cust_code, '') AS cust_code, etc etc

My SQL is complicated..You see I am using a distributed query from SQL to IBM DB2.
I would like to have data returned and interpreted correctly.
Any help on using cast convert in a Distributed query..?

Currently ( Thx to your help) I have the following Query.

SELECT NULLIF (business_ch, '') AS Business_CH,NULLIF (cust_code, '') AS cust_code, NULLIF (Ref_Code, '') AS Ref_code INTO Table1
FROM OPENQUERY (MOVEX_EXTRANET,
'SELECT OBRGDT AS Entry_Date, MMPRGP AS Business_CH, OAOREF AS Ref_Code
FROM MVXADTA.MITMAS ')

The 3 columns are returned and are correctly Nulled if Empty.
However, the dataype of each column is char when Clearly entry_date should be of data type date.
I think Nullif is causing this as If i don;t use nullif then some columns come back as numeric.

Any help with cast or convert here to make entry_date of type datetime and say ref_code as numeric..

Thx,
Ray..

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-08 : 08:20:56

Hi Ray,
Am I missing something here? Your openquery is returning Entry_Date, Business_CH, and Ref_Code, but your outer select is selecting Business_CH, Cust_Code, Ref_Code...

quote:
SELECT NULLIF (business_ch, '') AS Business_CH,NULLIF (cust_code, '') AS cust_code, NULLIF (Ref_Code, '') AS Ref_code INTO Table1
FROM OPENQUERY (MOVEX_EXTRANET,
'SELECT OBRGDT AS Entry_Date, MMPRGP AS Business_CH, OAOREF AS Ref_Code
FROM MVXADTA.MITMAS ')

Anyway, there should be no problem in applying NULLIF and CAST to the same column. I.e.

SELECT CAST(NULLIF(Entry_Date, '') AS DATETIME) ...

However, if this is returning '' then it looks as though it's being stored as character data. If this is the case, there's always the chance that the CAST will fail. You may want to use ISDATE to validate it, and make sure that SET DATEFORMAT corresponds with the order of the date elements in the data that you're getting through.

Mark

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-08-08 : 10:33:42

Sorry Mark,
That was a typo on my part.The code is an example of what I am trying to do..
Here is the correct code now using Cast on Entry_Date..
I think you helped me before with this..!!
The DB2 Openquery was returning the date as numeric and in the form YYYYMMDD
I needed to use SUBSTR to make YYYYMMDD look like YYYY-MM-DD.
Then I can CAST AS DATETIME and SQL sees it as a proper date..

Do I have my syntax etc correct..It does all seem to work..?
Do I need to run NULLIF on each individual Column..?

The full code is here that I plan to use
1.create a TMP table ,
2. Create into TMPTable
3. Drop data_Table
4. Rename tmp_table to Data_Table.

Therefore the only downtime on update is the renaming of the table.
Would Truncating work better..
I cant create into an existing table ?

SELECT CAST(NULLIF(ENTRY_DATE, '') AS DATETIME) AS ENTRY_DATE,NULLIF (BUSINESS_CH, '') AS BUSINESS_CH, NULLIF (Ref_Code, '') AS Ref_code
INTO TMP_Table
FROM OPENQUERY (MOVEX_EXTRANET,
'SELECT DATE((SUBSTR(CHAR(MVXADTA.OOLINE.OBRGDT),1,4)
||''-''||
SUBSTR(CHAR(MVXADTA.OOLINE.OBRGDT),5,2)
||''-''||
SUBSTR(CHAR(MVXADTA.OOLINE.OBRGDT),7,2))) AS Entry_Date, MVXADTA.MITMAS.MMPRGP AS Business_CH, MVXADTA.OOHEAD.OAOREF AS Ref_Code
FROM MVXADTA.OOLINE, MVXADTA.OOHEAD, MVXADTA.MITMAS ')
ORDER BY ENTRY_DATE DESC
Drop table Data_Table
EXEC sp_rename TMP_Table , Data_Table

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-08 : 10:50:17

Hi Ray,
1. Don't bother doing all that string manipulation in DB2. You can cast a date string of format 'YYYYMMDD' directly in SQL with no problems.
2. Rest of the syntax looks fine
3. You need a nullif on each column that may contain empty strings that you want to store as nulls.
4. As I said previously, I don't see any benefit in using a temp table. Simply drop Data_Table then select into it (thereby recreating it). Put this within a transaction testing for errors after each statement and rolling back if any are found.

Mark

rwaldron
Posting Yak Master

131 Posts

Posted - 2006-08-09 : 05:04:53

Hiya Mark,
I've tried using Cast as datetime directly in SQL but its not working..
If I do "SELECT CAST(ENTRY_DATE AS datetime) AS ENTRY_DATE"
I get the error "Arithmetic overflow error converting expression to data type datetime." I need to format the data BEFORE the convert ?

Also, Just on the the TempTable usage..
My query may take 20 secs to run.
If I drop the original Table and do a Select into using the Query, does this not result in 20 sec downtime of the table?
Ie: The table doesn't exist while the query is running.?
Thats why I tried the rename temp to data as the only downtime is the time it takes to rename the table?

Ray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources