Hot answers tagged

101

If you're just importing from a dump file from the CLI on *nix, e.g. mysql -uxxx -pxxx dbname < /sqlfile.sql then first install pipe viewer on your OS then try something like this: pv sqlfile.sql | mysql -uxxx -pxxxx dbname which will show a progress bar as the program runs. It's very useful and you can also use it to get an estimate for ...


20

To backup: mysqldump -u user -p database > backup.sql To import: mysql -u user -p database < backup.sql


20

You'll need to create a user (or Schema) first C:\>sqlplus system/password SQL> create user CLIENT_TEST identified by client_test_password; SQL> grant connect, unlimited tablespace, resource to CLIENT_TEST; SQL> exit Then you can use the fromuser= and touser= IMP switches to import the data into the new user/schema: C:\>imp ...


19

LOAD DATA INFILE and extended INSERTs each have their distinct advantages. LOAD DATA INFILE is designed for mass loading of table data in a single operation along with bells and whistles to perform tings like: Skipping Initial Lines Skipping Specific Columns Transforming Specific Columns Loading Specific Columns Handling Duplicate Key Issues Less ...


16

Export: mysqldump -u username –-password=your_password database_name > file.sql Import: mysql -u username –-password=your_password database_name < file.sql


14

When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Naturally, the reload of the mysqldump into a database would also be in alphabetical order. You could just do a SHOW PROCESSLIST; and find out the DB Connection running the mysqldump. When the dump is reloaded, the DB Connection will vanish. If you want to know ...


12

There is current open bug report on this one. The bug report has a suggested work around at the bottom entry [22 Jan 2010 6:46]: replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename Give it a Try !!!


11

A solution I've used in the past (and have recommended here and on StackOverflow before) is to create two additional schemas: CREATE SCHEMA shadow AUTHORIZATION dbo; CREATE SCHEMA cache AUTHORIZATION dbo; Now create a mimic of your table in the cache schema: CREATE TABLE cache.IPLookup(...columns...); Now when you are doing your switch operation: ...


11

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


10

It's not actually possible to specify a different tablespace when importing using the oracle imp utility. However, as a workaround, you can pre-create the tables by doing a ROWS=N import into the USERS tablespace, then alter table mytable move tablespace BLOG_DATA; for each table to move them to the new tablespace, then do the import again with the IGNORE=Y ...


9

Instead of export/import, you should use alter table t shrink space; which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s): set verify off column file_name format a50 ...


9

I am unsure if its necessary to add the TABLOCK table hint to an empty temporary table, defined with a clustered index in order to get minimal logging. No. Local temporary tables (#temp) are private to the creating session, so a table lock hint is not required. A table lock hint would be required for a global temporary table (##temp) or a regular table ...


8

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


8

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL. You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention. It will be a lot cleaner than trying to split a single file.


8

SSIS is the way to go on this. If you've never built a package before, and you know your source files (read also: spreadsheets) are always going to be the same ones, what you can do is use SQL Server's Import/Export wizard. In SSMS right-click database and select Tasks > Import (or Export) Data... This opens a wizard which walks you through the steps of ...


8

All you need is configured ODBC connection to Oracle DB 1. Install Oracle Client SW (download from otn.oracle.com). I recommend installing same version as your Oracle DB 2. Define connection in tnsnames.ora 3. Create and test ODBC connection to Oracle DB in Control Panel > Administrative Tools > Data Sources (ODBC) - use Oracle's driver in your ORA_HOME, ...


8

It is not common to create tables as SYS or SYSTEM in Oracle, as those two accounts are administrators. Error means that your client created tables as user ABCDE. You should create that user before import, then you should import data as that user, This should eliminate the error because exported file contains permissions and other informations related ...


7

If that is the actual DDL you are using to create the table, you could use the NLS_LENGTH_SEMANTICS parameter. If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character). ...


7

Why insert into tblusers directly at all? I always use staging tables. You can use SSIS of course for the same result at with greater complexity INSERT INTO [staging].[Users] ([username], [password]) VALUES ('user1', 'pass1'), ('user2', 'pass2') INSERT INTO [tblUsers] ([username], [password]) SELECT DISTINCT [username], [password] ...


7

You can also import an .sql file as an already connected user to the database manner : mysql> use your_database_name; mysql> source file.sql;


7

Achieving optimal import performance in this scenario requires three things: Minimally-logged base table inserts Minimally-logged nonclustered index builds Avoiding physical reads Minimal Logging Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires: Using either the SIMPLE or BULK_LOGGED database ...


6

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


6

You could use Flashback to return the table data back to its original state. Is Flashback enabled? Database needs to be in archive mode: select log_mode from v$database; Should return "ARCHIVEMODE" Check if flashback is enabled: select flashback_on from v$database; Should return "YES" If "NO", you can enable flashback by runing: alter database ...


6

You just need to add OPTIONALLY ENCLOSED BY '"': LOAD DATA INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES; Documentation link. File: n1,s1,s23,n2,s4,s56 1,string1,"str2,str3",2,string4,"str5, str6" Table and test: mysql> create table tbl_name ( ...


6

I would use Bulk insert. If you have a delimited file, it should be pretty easy. http://msdn.microsoft.com/en-us/library/ms188365.aspx A sample looks like this BULK INSERT dbo.MyTable FROM 'c:\test\test.txt' WITH ( FIELDTERMINATOR ='|', ROWTERMINATOR =' \n' ); EDIT: Based on the given requirements (the file cannot ...


6

You first need to create user "ABCDE" Something like In SQL*PLUS: create user ABCDE identified by password; grant connect, resource to ABCDE; There's a squillion options on "create user" but this would use the defaults.


6

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version ...


5

Log files in /var/log/postgres are only for your information (in a standard installation) and do not serve the system itself, especially not "ACID compliancy". They need to be writable once configured, that's all the server needs. There are a number of settings in your postgresql.conf that govern what is logged. Most of them can also be set at the command ...



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