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 ...


7

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 ...


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


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 ...


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.


3

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

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

What you want is nearly impossible, no one can know if you want to import '1' as a characters string, n decimal integer, a base-2 integer, an hex integer, a float, a blob, a "boolean", ... You can try heuristic programs, or import them all as strings and run an analysis program like SELECT... ANALYZE TABLE();. It would be easier if the csv had the datatypes ...


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

Most of the good tips has been given so far, but without lots of explanations for the best ones. I will give more details. First, delaying index creation is a good one, with enough details in other responses. I will not come back on it. A larger InnoDB log file will help you a lot (if you are using MySQL 5.6 as it is not possible to increase it in MySQL ...


2

You would need to use Dynamic SQL to pass a dynamic file path to the Bulk Insert. DECLARE @FileName NVARCHAR(4000); SET @FileName = '/path/to.csv'; DECLARE @sql NVARCHAR(4000) = 'BULK INSERT #CSV FROM ''' + @FileName + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )'; EXEC(@sql);


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

You can't directly restore a MySQL backup to MSSQL. What you can do is use tools such as Microsoft SQL server migration assistant. But those still won't help you if you don't have the proper permissions on the destination server. You'll most likely be creating a database, editing schemas and creating logins and users. Which means you need the sysadmin ...


1

From BOL: If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always ...


1

The size needed in a temporary tablespace depends on the data volume manipulated, but including potentially other actions also requiring temporary space. What you should be doing is this : monitor temporary tablespace usage, (temporarily) expand the temporary tablespace. Maybe, you need to recreate your temp tablespace. That is, if there is corruption of ...


1

I was able to automate the process enough, keyword being enough. To help anyone else dealing with exporting from Microsoft Access and importing to PostgreSQL I'm providing details that helped us work through the errors we encountered. While I'm fairly competent with MySQL and new (though still reasonably well at PostgreSQL) the people who setup the various ...


1

Assuming that the script contains only INSERTs, UPDATEs and DELETEs to the target table(s), and that the app never attempts row locks (SELECT ... FOR UPDATE/SHARE) or DML on those tables, then the script shouldn't affect the app except for the increased load on the DB server. When the script commits, the changes will instantly become visible to new ...


1

You can specify your queries per table with the QUERY parameter with Data Pump export. For example: QUERY=employees:"WHERE department_id > 10 AND salary > 10000" With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000. More information in the official documentation: ...


1

Given the clarifications on your comment, I would recommend 2 options: Use LOAD DATA INFILE IGNORE to load the data directly from the filesystem. This will insert new domains and not touch the "old" ones, but it will not delete the ones that are removed. On the bright side, it will reduce IO a lot. Go for your approach: use LOAD DATA INFILE on a new table, ...


1

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type: mysql> LOAD DATA INFILE '/tmp/alarms.csv' INTO TABLE alarms COLUMNS TERMINATED BY ',' IGNORE 1 LINES (@Date, @Time, ALARM_01, ...


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

To dump a database mysqldump -u root -p databasename > databasename.sql Dumping particular table alone then this would apply mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql


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

Your local table is likely not empty, thus the error when conflicting with existent data. If you do not want to keep any of the current data, you need to delete all rows before the insert. One fast way to do that is with: TRUNCATE TABLE exp_accessories; Usually, mysqldump will drop the table before import, like this: DROP TABLE IF EXISTS ...



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