How Can I Backup an Existing SQL Table?

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.
Who is Participating?

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
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
matrix0511Author Commented:
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?
Chris MangusDatabase AdministratorCommented:
Try this syntax:

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

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.