Creating Temp Tables using existing Table

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

Old Forums

CLOSED - General SQL Server

Creating Temp Tables using existing Table

Author

Topic

amitgujrathi
Starting Member

17 Posts

Posted - 2004-06-04 : 16:06:43

Is there a way to create a temp table using an existing tables structure?

e.g. CREATE TABLE #tempClient as Client

Limitations:
1. The client table contains more than 200 fields & the tempTable is needed in a stored procedure to temporarily store data from
retrieved from OPENXML rowset.
2. I Don't want to use the datatypes & column names anywhere in the stored procedure so that the store procedure doesn't require anychanges when a column gets added or a column type/name gets changed

Here is the body of the store proc:

<note:
-- @xmlDocument is the input param to the store proc
-- @localName is the input param for the local name of the node in XML Document>

<Body starts here>

declare @docHandle int

exec sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument

<here is where I need help>
CREATE TABLE #tempClient as Client

INSERT INTO Client
SELECT *
FROM OPENXML (@docHandle, @localName, 1)
WITH #tempClient

<note: In the WITH Clause above, I'm not using the Client Table directly because Client Table has a IDENTITY Column CID, which is also primary key of the table & OPENXML doesn't let you select the CID column with table name in the WITH Clause. Since I don't want to use datatypes & column names anywhere in the stored procedure, I cannot use the Meta Properties also with the WITH clause's SCHEMA DECLARATION method>

exec sp_xml_removedocument @docHandle

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-04 : 16:09:25

Well...I don't like where this is headed but....

Here's some rope...

SELECT * INTO #myTemp99 FROM Orders

Brett

8-)

amitgujrathi
Starting Member

17 Posts

Posted - 2004-06-04 : 16:16:11

Why don't you like it? Are there any contraints or performance issue? Since my XMLDocument is going to be quite big & the main code of the insertion is going to in a loop based on a cursor & there would be many insertions in a single call to the stored proc? Please let me know, if I shoud take care of something in advance.

Thanks

Amit

amitgujrathi
Starting Member

17 Posts

Posted - 2004-06-04 : 16:22:30

By the way I just tested the way you mentioned using:
Select * into #tempClient From Client

It gives the same result as I was getting by directly using Client Table in the WITH clause, i.e the OPENXML doesn't let you fetch the IDENTITY Column CID from the XMLdocument into the ROWSET.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources