How Can I Backup an Existing SQL Table?

Posted on 2011-10-17
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


    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

    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

    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

    Try this syntax:

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

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now