Tag Info

Hot answers tagged

37

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


15

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


13

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


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


10

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


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


7

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


7

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.


7

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


7

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

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


6

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


6

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


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

SQL Server cannot import what it cannot see. The reason that you can import it via SSMS is because it opens a Windows File Dialog box, which uses your permissions and not SQL Servers to find the file. If you must import the file then the only way you can do this is to make the file accessible to SQL Server. How you decide to do this is up to you, whether ...


5

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

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.


5

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


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

It just works, like it should. It doesn't lock the table, so start testing.


4

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


4

Using import with show=y won't actually do the import, but will show which tables the import will run against. There's no practical way, however, to show what rows import will write; it's assumed that you, running the import, would know that. You may wish to look at some of Oracle's extensive documentation about these utilities to determine what options ...


4

As always with Oracle, there are many ways to reach your goal. You might want to check out flashback database. This enables you to restore your database in a previously saved state and might be the easiest in your case. Using Flashback Database and Restore Points Drop user cascade could also do the trick, followed by impdb as you described. What was the ...


4

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


4

You don't have a choice of character set on XE so you cannot change it to suit the database you are trying to import. Would it be practical to migrate the source database before export? The import should work, but character set conversion might mean some text columns with non-ascii characters won't look the same after the import. And rows can be rejected if ...


4

Here is one example on how to import data from excel to SQL Server. One of the main problems is making sure you use Data Conversion component between Excel and SQL Server and do a conversion from NVARCHAR to VARCHAR as excel treats the data as NVARCHAR. http://www.mssqltips.com/tip.asp?tip=1393


4

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database. MySQL Use db syntax The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement ...


4

This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data. mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema ...


4

Choose to save the package instead of running it right away. Then edit the dtsx file and on your copy task chek "keep nulls". Then run the dtsx. I didn't test it but think it will solve your case.


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



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