[Last Call] Learn how to a build a cloud-first strategyRegister Now


How Can I Backup an Existing SQL Table?

Posted on 2011-10-17
Medium Priority
Last Modified: 2012-06-27
Hi, I know this will be a very simple one for you DBAs out there. we use Oracle 11g. I'm just trying to backup an existing table (SY811.F986101). I'm currenty connected via SQL Developer. I can do a select * from to pull all recordsfrom this table so I know I am connected to the DB and table successfully.

But when I try to execute the following command to create an exact copy of the table I get the attached syntax error. What am I missing??? Thanks.

SELECT * INTO SY811.F986101BKJ FROM SY811.F986101

So F986101BKJ is the backup table.
Question by:matrix0511
LVL 13

Accepted Solution

themrrobert earned 2000 total points
ID: 36982342

SELECT * FROM SY811.F986101 INTO SY811.F986101BKJ

also make sure that the new table has a current structure identical to the original. I will check on some other things and be back
LVL 13

Expert Comment

ID: 36982357
Yes your original syntax is correct and should work, be sure to double check that the new table has the appropriate columns.  If that still isn't the problem, then I'm guessing its an issue with SQL Developer, and that takes it out of my scope, hopefully another expert can help you with Oracle SQL Developer

Author Comment

ID: 36982420
Themrrobert, you said make sure the new table has current structure. But the new table does exist yet. That's why I'm running this command to create that new table.

How can I make sure when I run this command that it will create the same table structure?
LVL 17

Expert Comment

by:Chris Mangus
ID: 36982505
Try this syntax:

create table SY811.F986101BKJ as select * from SY811.F986101;

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36984875
oracle or ms sql?
the syntax is not the same.

also, if the backup table exists already, you need to use INSERT INTO ... SELECT FROM  and not SELECT ... INTO ... FROM etc

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

830 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