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

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

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

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


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

A DMP file is just a copy of the whole database contents. During the Import, it does not get modified, so you can use that file to "jump back" to the time when that dump file was created as often as you want.


1

I would write a python script that would, parse the columns descriptor file, get their widths and names iterate over all the data in the input file, splitting out the data into variables construct a sql query with these variables to the sql server with SQLAlchemy to be sent one by one or as a batch of rows or 1000 at a time This script would take the ...


1

What's below works for your data - be careful of any variation in the field width - that's why delimited files are better - csv, or better still, if you can get it, pipe-delimited (|). It also successfully deals with the header line. I did the following: My code will do for both MD5 and SHA2 passwords - uncomment the relevant bits. CREATE TABLE my_user ( ...


1

Yes, there is a safe way (not faster though) to alter schemas if you use tool. This does not require any downtime. The one we use everyday on our production is to use pt-online-schema-change this tool will show you the progress of the SQL. I hope this helps


1

As a solution for someone who can't get pv to work or for whom pv tells lies. You can monitor the size of ibdata1 file in /var/lib/mysql which contains the data. This will end up the same size (or thereabouts) of the filesize in your source server. If there are many tables you can also watch them appear one by one in /var/lib/mysql/< database name>.


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

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.



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