Merge statement, external tables ...

v11.50.fc7w3 Aix 6.1 Can an external table be used with a merge statement, or would the external table need to be loaded into a real table before being part of a merge statement ... In my case the external table would not be the updated table .. that I would think wouldn't work .. Thanks in advance for any and all assistance .... If someone has done this, how well does it work, or are you just better at loading the external table first ... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309

The external table should be able to be a part of a MERGE as long as it is not the target of the insert/update but one of the source tables. In fact, the first example in the documentation for the MERGE statement in the Guide to SQL Syntax appears to use an external table as the data source. The description doesn't mention it, but the text is: MERGE INTO customer c USING ext_customer e ON c.customer_num=e.customer_num WHEN MATCHED THEN UPDATE SET c.fname = e.fname, c.lname = e.lname, c.company = e.company, c.address1 = e.address1, c.address2 = e.address2, c.city = e.city, c.state = e.state, c.zipcode = e.zipcode, c.phone = e.phone WHEN NOT MATCHED THEN INSERT (c.fname, c.lname, c.company, c.address1, c.address2, c.city, c.state, c.zipcode, c.phone) VALUES (e.fname, e.lname, e.company, e.address1, e.address2, e.city, e.state, e.zipcode, e.phone); It looks to me that the ext_customer table is an external table. Give it a try! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 17, 2010 at 10:03 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > v11.50.fc7w3 > Aix 6.1 > > Can an external table be used with a merge statement, or would the > external table need to be loaded into a real table before being part of a > merge statement ... In my case the external table would not be the > updated table .. that I would think wouldn't work .. Thanks in advance > for any and all assistance .... If someone has done this, how well does > it work, or are you just better at loading the external table first ... > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30433eb2fa5803049541228e

Thanks Art ... maybe next time I should actually look at the documentation ... Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Art Kagel" <art.kagel@gmail.com> To: ids@iiug.org Date: 11/17/2010 10:15 AM Subject: Re: Merge statement, external tables ... [21969] Sent by: ids-bounces@iiug.org The external table should be able to be a part of a MERGE as long as it is not the target of the insert/update but one of the source tables. In fact, the first example in the documentation for the MERGE statement in the Guide to SQL Syntax appears to use an external table as the data source. The description doesn't mention it, but the text is: MERGE INTO customer c USING ext_customer e ON c.customer_num=e.customer_num WHEN MATCHED THEN UPDATE SET c.fname = e.fname, c.lname = e.lname, c.company = e.company, c.address1 = e.address1, c.address2 = e.address2, c.city = e.city, c.state = e.state, c.zipcode = e.zipcode, c.phone = e.phone WHEN NOT MATCHED THEN INSERT (c.fname, c.lname, c.company, c.address1, c.address2, c.city, c.state, c.zipcode, c.phone) VALUES (e.fname, e.lname, e.company, e.address1, e.address2, e.city, e.state, e.zipcode, e.phone); It looks to me that the ext_customer table is an external table. Give it a try! Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Nov 17, 2010 at 10:03 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > v11.50.fc7w3 > Aix 6.1 > > Can an external table be used with a merge statement, or would the > external table need to be loaded into a real table before being part of a > merge statement ... In my case the external table would not be the > updated table .. that I would think wouldn't work .. Thanks in advance > for any and all assistance .... If someone has done this, how well does > it work, or are you just better at loading the external table first ... > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30433eb2fa5803049541228e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.