Ok finally got the solution. "Create table if not exists newTable as select oldTable.*, "" as newColumn from oldTable"
Regards Venkatesh On Tuesday, August 11, 2015, venkatesh b <venkateshmailingl...@gmail.com> wrote: > Thanks Adam. > > As mentioned we have a need that in a single query it should be done, > since ours is an automated query generation and no manual interactions are > done. > > We need a query like > > *Create table if not exists newTable like oldTable, newColumn datatype* > > *Without copying data from oldTable. * > > > On Monday, August 10, 2015, LaStrange, Adam <adam.lastra...@sig.com > <javascript:_e(%7B%7D,'cvml','adam.lastra...@sig.com');>> wrote: > >> How about: >> >> >> >> create table XXX like YYY; >> >> alter table XXX add columns (new_column int); >> >> >> >> *From:* venkatesh b [mailto:venkateshmailingl...@gmail.com] >> *Sent:* Monday, August 10, 2015 9:28 AM >> *To:* Wangwenli >> *Cc:* user >> *Subject:* Re: How to create new table like existing with an extra >> column in single query. >> >> >> >> Thanks Wenli for quick reply. >> >> >> >> Yes, we have a requirement exactly the same which you mentioned. >> >> >> >> I have tried the query which you provided but got an error saying that >> >> "Invalid column reference for newColumn possible columns are ......" >> >> >> >> >> >> >> >> Thanks >> >> Venkatesh >> >> On Monday, August 10, 2015, Wangwenli <wangwe...@huawei.com> wrote: >> >> something like this? >> >> create table newTable as select oldTable.*, newCol1,newCol2 from oldTable; >> >> >> ------------------------------ >> >> wenli >> >> Regards >> >> >> >> *From:* venkatesh b >> >> *Date:* 2015-08-10 20:54 >> >> *To:* user >> >> *Subject:* How to create new table like existing with an extra column in >> single query. >> >> Hello, >> >> >> >> In hive we got a need that we need to create a new table like the old >> table with an extra column at run time. This must be done in a single >> query. No second query with alter table statement. >> >> >> >> Query like: >> >> >> >> Create table new_table like old_table , new_column datatype; >> >> >> >> Please help me out, searched in many forums but not found the solution. >> >> Please inform me if any other information is needed. >> >> >> >> Thanks & Regards >> >> Venkatesh >> >> >> ------------------------------ >> >> IMPORTANT: The information contained in this email and/or its attachments >> is confidential. If you are not the intended recipient, please notify the >> sender immediately by reply and immediately delete this message and all its >> attachments. Any review, use, reproduction, disclosure or dissemination of >> this message or any attachment by an unintended recipient is strictly >> prohibited. Neither this message nor any attachment is intended as or >> should be construed as an offer, solicitation or recommendation to buy or >> sell any security or other financial instrument. Neither the sender, his or >> her employer nor any of their respective affiliates makes any warranties as >> to the completeness or accuracy of any of the information contained herein >> or that this message or any of its attachments is free of viruses. >> >
