Hot answers tagged

58

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


39

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


21

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


18

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

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


14

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';


14

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

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


12

If you just want to check if it is stalled you can query show processlist; and see what is being executed.


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


10

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


10

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


9

SUGGESTION #1 No need to run unique_checks = 0 and foreign_key_checks = 0. See my 3-year-old post Speeding up mysqldump / reload (ASPECT #2 shows a standard header of a mysqldump. Lines 13 and 14 handle the disabling of those checks for you) SUGGESTION #2 Please note the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko) If you want to reload ...


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

With an average of 2.4 characters (more relevant: avg bytes - but that's the same for all ASCII characters) I would not bother to use enums. Those occupy 4 bytes on disk plus, possibly, alignment padding. (text does not require alignment padding.) You are not even saving storage and get more overhead for it. With most values below 7 characters (= 8 bytes on ...


8

Create an empty database or if you have one already target that one. Open CMD with elevated privilege and run: sqlcmd -S SERVERNAME -d MYDATABASE -U USERNAME -P PASSWORD -i C:\path\mysqlfile.sql -o C:\path\results.txt basically: -S: is your servername or localhost -d: is the database you are targeting -U: is the username -P: is the password -i: is 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

If it's an option, you could pre-sort the data externally, before database import. In my test using PostgreSQL 9.6.1, a table with three double precision columns and 10M records with random values, creating an index on the same data, but preordered, shaved off 70% of index creation time: db=# create table indexing_test_data (a varchar(50), b varchar(50), c ...


8

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


8

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


8

I found that sqlite3 custom init script can have meta-command as well as SQL statement: #!/bin/sh commandfile=$(mktemp) # create temporary init script cat <<EOF > $commandfile .mode csv tablename .import /dev/stdin tablename EOF # import bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db


8

You can use powershell to fast import large CSV into sql server. This script High-Performance Techniques for Importing CSV to SQL Server using PowerShell - by Chrissy LeMaire (author of dbatools) Below is the benchmark achieved : 5.35 million rows a minute for non-indexed tables and 4.35 million rows a minute for tables with clustered indexes. The script ...


7

Another way to import dump files when source <filename> doesn't work is to do the following: Dump ~> mysqldump --user=<user> --password=<password> <db_name> > <export_file_name>.sql Import > mysql -u <user> -p <pass> <db_name> mysql> USE <db_name>; (if you didn't already select) mysql> ...


7

If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this: cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin mycsvtable' SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. ...


7

The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing. CREATE TEMP TABLE baz AS SELECT 1::int, '{"a":"b"}'::jsonb; This is your exact sample data. Now we can test different settings.. # COPY baz TO STDOUT; 1 {"a": "b"} COPY baz TO STDOUT DELIMITER ','; 1,{"a": "b"} ...


7

1.Open MS SQL Server Import and Export Wizard. 2.For “Data Source” choose “Microsoft Office 12.0 Access Database Engine OLE DB Provider”. 3.Click “Properties” enter the location of the .accdb file in the “Data Source” field. That is the physical location of the Access database file, like C:\Temp. 4.Under login information, choose a blank username and be ...


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


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

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


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