Hot answers tagged

267

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


49

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


42

A .bacpac file = Schema + Data. Note that Data is BCP'ed out using Native format (not readable by Human). You can rename the .bacpac to .zip to see the actual contents. You can use ...DAC\bin\sqlpackage.exe commandline to extract the .bacpac contents programatically. It is a snapshot that includes User data + Schema from SQL Server or Azure SQL Database. ...


28

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;


27

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 MiB" FROM information_schema.TABLES GROUP ...


26

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


19

Given that there are probably just as many unfounded fears as there are unknown risks, I would think that it is difficult to really say why a policy is in place without asking whomever created the policy why they are concerned. However, I would guess that it probably has something to do with what BULK INSERT / SqlBulkCopy / BCP / OPENROWSET(BULK ...) allow ...


17

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


17

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


15

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


13

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


13

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


13

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


13

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


13

This dump was dumped as individual statements (with pg_dump --inserts) INSERT INTO esa2010_codes VALUES (11002, 'Národn INSERT INTO esa2010_codes VALUES (11003, 'Nefina INSERT INTO esa2010_codes VALUES (12502, 'Národn INSERT INTO esa2010_codes VALUES (11001, 'Verejn INSERT INTO esa2010_codes VALUES (12602, 'Národn INSERT INTO esa2010_codes VALUES (12603, '...


12

In the end I coded a Python function import_csv_to_dynamodb(table_name, csv_file_name, colunm_names, column_types) that imports a CSV into a DynamoDB table. Column names and column must be specified. It uses boto, and takes a lot of inspiration from this gist. Below is the function as well as a demo (main()) and the CSV file used. Tested on Windows 7 x64 ...


11

The mysqldump, by default, will drop the table. You should specified the --no-create-info option like this: mysqldump -u... -p... --no-create-info --skip-extended-insert mydb t1 > mydb_table.sql That way, you only have inserts to deal with. Using --skip-extended-insert will insert one row at a time. This help deal with duplicate issues, but you will ...


10

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


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

You can once create a view using column definitions like this: FirstName COLLATE SQL_Latin1_General_CI_AS, ... Then anytime you'd like to use User tables, you can use this view instead.


9

SQL Server has always supported bulk inserting from CSV files, you just have to specify field/row terminators. file.csv contains: foo,bar,1 blat,splunge,2 Then we do this: CREATE TABLE #foo(a varchar(32), b varchar(32), c int); BULK INSERT #foo FROM 'c:\temp\file.csv' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = ','); SELECT * FROM #foo; Results: a ...


8

Every 2 seconds you will see the processes running. watch 'echo "show processlist;" | mysql -uuser -ppassword'; If you want it less frequent then add -n x where x is the number of seconds. 5 seconds would be: watch -n 5 'echo "show processlist;" | mysql -uuser -ppassword';


8

You are probably seeing an error like: Msg 7357, Level 16, State 1, Line xx Cannot process the object "<query text>". The OLE DB provider "<provider>" for linked server "<server>" indicates that either the object has no columns or the current user does not have permissions on that object. This occurs when SQL Server tries to discover the ...


8

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


8

You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files): Linux or Mac OS X free and open source sudo pip install csvkit Example: csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql It creates a CREATE TABLE statement based on the file content. Column names are taken from ...


7

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess. db2 grant dataaccess on database to user winuser1 If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be ...


7

Unfortunately that's not possible if you insist on using the wizard, you would need to edit the package using Visual Studio. There is an option 'keep null's' when editing the package in Visual Studio Given this .csv file: StringField,IntField a,1 b,5 c, d,6 e, f,8 g, h, i,10 j, and this table definition CREATE TABLE [dbo].[NullTest]( [StringField] [...


7

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


7

You need to use bcp.exe or SSIS. Read Import and Export Bulk Data by Using the bcp Utility (SQL Server) and The Data Loading Performance Guide. Also, read How to Analyze SQL Server Performance to be able to tell why something is slow. After you've read those, come back with a more actionable question, including structure of table, csv structure, ...


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