Tag Info

Hot answers tagged

10

Don't just use the whole table. Instead choose the option to write a query. Then simply write a select statement that doesn't use that Identity Column. Alternatively, when you map columns, if you click "Delete rows in destination table", it will truncate the target table before loading data. If the opportunity exists for data to be removed from the ...


6

First, you need to know what you are doing to InnoDB when you plow millions of rows into an InnoDB table. Let's take a look at the InnoDB Architecture. In the upper left corner, there is an illustration of the InnoDB Buffer Pool. Notice there is a section of it dedicated to the insert buffer. What does that do ? It is ised to migrate changes to secondary ...


5

Look into BCP or BULK INSERT, both of which can share format files. Quick math based on pure guesses, since no hard details are given: 13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes. 5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = ...


4

I wanted to write a comment (as this is not a definitive answer), but it became too long: I am going to give you several broad pieces of advice, and we can go into details for each one, if you want: Reduce durability (you have already done some of it). Latest versions allow even doing it more. You can go as far as disabling the double write buffer,as ...


4

There is only one REMAP_TABLESPACE parameter in de command. It would be seperated with ",". REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX Oracle doc impdp 11.2 http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


3

You can use a variant of the technique described in Using merge..output to get mapping between source.id and target.id and by Adam Machanic in Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE. You use merge and capture the generated ID with the XML fragment that belongs to that ID in a table variable and then use that table variable when ...


3

imp is an operating system command, not an SQL*Plus command, so it should be run from the OS shell command line. If you insist on running it from SQL*Plus you need to use the SQL*Plus host command to send imp to the host OS for execution.


2

Use this on Windows (three nested double quotes): exp system/password_for_system@dev file=c:\fooadmin.dmp full=yes imp """sys/password12345@dev as sysdba""" FROMUSER=FOOADMIN TOUSER=FOOADMIN file=c:\fooadmin.dmp In general imp would work as intended with sys account, it is just not recommended. But exp with sys account could possibly produce inconsistent ...


2

I imagine the unescaped * character in your password is causing problems in the shell. Escape it and you should be ok.


2

Below is a SQL Table-Valued Function that I got from Jeff Moden, that will return the File Attributes of a single file: /* Return File Attributes about a single file. WARNING: Uses the sp_OACreate method, which should be SA use only. (Note: much of this is copied from Jeff Moden's routines) TEST: SELECT * FROM pps.fnFileInfo('C:\install.exe') */ ...


2

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes). Save your package to a file then open it in SQL Server Data Tools. Go into the data flow and open the source. In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in ...


2

You can add a where clause to each table to filter rows exported: http://www.dba-oracle.com/t_export_where_clause_selective.htm However your referential integrity will not be maintained by this method, because the export is done per-object and filters are applied in the same way. You will need to either be very precise in how you define each where clause, ...


2

Indexes are not disabled in InnoDB using DISABLE INDEX (from http://dev.mysql.com/doc/refman/5.1/en/alter-table.html): For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes For InnoDB, you're stuck ...


2

Full disclosure.. I am the author of the following program. Give it a try and let me know what you think. I have tried many applications including the excel add-in from MySQL, but was not satisfied with them, so I wrote my own. The current version of my program is slow (just like the MySQL add-in), doesn't support international characters, and doesn't ...


2

mysqldump, or .sql files, which is what that module uses is probably the least efficient way to import a database (the only less efficient way I can think of is to import and commit each row at once). If you want to speedup the import process, you should change the method. There are several things that you can do in the MySQL configuration that will speed ...


1

This is a full database import which also includes tablespace definitions. Find out from the original Solaris database what are all the target tablespace names and sizes. Pre-create the tablespaces manually using the datafile paths of your choice (C:\data\) before performing the import. You will see IMP-00015: following statement failed because the object ...


1

The column default for daqa_report_id would have to be: nextval('jira_tickets.daqa_id_seq'::regclass) Not: 'jira_tickets.daqa_id_seq'::regclass That would just fetch the OID for the sequence object from pg_class, which is converted to a meaningless, static bigint number. pgAdmin has nothing to do with this. But just use the pseudo data type bigserial ...


1

First, make sure this chunks/datafiles are from Windows OS too. Isn't compatible read a datafile froma different plataform (windows VS Unix VS Linux) You cannot just move the chunks/datafiles and expected the engine just re-adapt to new path. There internal pointers at theirs configurations where the path is saved and need some administration steps to ...


1

Thanks. I have imported dmp file by following steps.. Command to open SQLPLUS In Command Prompt: C:> sqlplus / as sysdba Clear Screen: SQL> Clear Screen 1- First create a new user by SQL> Create user TestUser1 identified by test; -- (test is password) 2- Grant Privilages to new created user TestUser SQL> Grant dba, IMP_FULL_DATABASE, ...


1

I wasn't clear where you want the transformed id_estado inserted, so I chose "state" as a destionation. You can modify accordingly: insert into courrier_city (country, state, created, modified) select "VE", if(is_estado<10, concat("VE0.",is_estado),concat("VE.",is_estado)), now(), now() from zoom_city You will probably want to modify the example ...


1

So after a little more searching I found the answer :) Here is how this needs to be done. mysql -uroot -xxx! -f main < sq2.sql > /var/www/log.txt &


1

After some careful thought and some experimentation, the AccessDatabaseEngine.exe installed on the server is probably part of the problem. There are actually two versions 32 bit and 64 bit. On my computer 64 bit, I have the 32 bit version of Office installed. The SQL Server on my computer is 64 bit. When I attempt to use openrowset with the same OLEDB ...


1

Yes, the "switch or option" that would allow you to do this is the one your host doesn't want to change. If there were a way for a user to simply work around resource limits, there wouldn't be much point in resource limits. It's not obvious from the documentation, but the source code makes it fairly clear that this is a limit on the number of queries you ...


1

OK, spurred on by RolandoMySQLDBA's comment (I didn't know what to look for earlier), I was able to solve this. Ultimately, I used this blog post to recover the table data from the .frm files. Then, I used the technique here to import the ibd data.


1

Set the text delimiter in the flat file reader properties to the double quote.


1

Store the running time as an integer in total seconds. For display purposes you can convert the number of seconds into a string in hours:minutes:seconds format or whatever display format you choose.


1

What finally solved my problem was: turning archive mode off disallowing connections disabling triggers and constraints truncating all tables performing imports ( attemp of creating existing objects fails but data is inserted ) re-enabling triggers and constraints turning archive mode back on allowing connections back


1

You can use Microsoft Access' built-in import features to import data from just about any source. For Access 2007, the "ribbon" menu has an "External Data" tab, where you can select "Text". From there it is pretty simple to import text-based data. As you can see from the screenshot, there are a number of other types of data you can import.


1

Assuming you took a copy of the whole data directory including pg_xlog, pg_clog, global, base, etc, then you can simply ensure that PostgreSQL 9.3 is installed and: postgres -D /path/to/data/directory -c "port=5433" then in another terminal use pg_dump to dump the database(s) from the database running on port 5433 with (eg) pg_dump "dbname=mydb ...


1

If you've already started the import, you can execute this command in another window to see the current size of your databases. This can be helpful if you know the total size of the .sql file you're importing. SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY ...



Only top voted, non community-wiki answers of a minimum length are eligible