Hot answers tagged

60 votes
Accepted

Why is a .bacpac file so small compared to a .bak file of the same database?

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\...
user avatar
  • 61.3k
27 votes

Does it save anything to use a smaller varchar or nvarchar size instead of some large value like 4000?

Often SQL Server will have to allocate working memory to run a query. The amount of memory granted depends on many things including column data type and length. For (n)varchar it guesses actual values ...
user avatar
21 votes
Accepted

Why is BULK INSERT Considered Dangerous?

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 ...
user avatar
18 votes
Accepted

Get minimal logging when loading data into temporary tables

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 ...
user avatar
  • 72.4k
16 votes

How can I monitor the progress of an import of a large .sql file?

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 ...
user avatar
15 votes

How can I monitor the progress of an import of a large .sql file?

If you just want to check if it is stalled you can query show processlist; and see what is being executed.
user avatar
  • 251
15 votes
Accepted

Importing large SQL dump with millions of INSERT statements

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 ...
user avatar
  • 56.7k
14 votes
Accepted

InnoDB import performance

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 ...
user avatar
13 votes

SQL import data when both source and destination has data and identity column

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 ...
user avatar
  • 3,491
13 votes

Create a table from a CSV file with headers

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 --...
user avatar
12 votes
Accepted

Import CSV or JSON file into DynamoDB

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. ...
user avatar
12 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I've solved my issue by replacing all occurrences of utf8mb4_0900_ai_ci with utf8mb4_general_ci in my SQL dump file.
user avatar
10 votes
Accepted

Import Data from 48 GB csv File to SQL Server

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) ...
user avatar
  • 61.3k
10 votes
Accepted

How to import CSV file in SQL server 2008?

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 ...
user avatar
9 votes

Create index on a Postgres table as fast as possible

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, ...
user avatar
  • 580
9 votes
Accepted

MySQL settings useful to speed up a mysqldump import

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 ...
user avatar
9 votes
Accepted

SQL Server avoid repeating the collation type multiple times in a query

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.
user avatar
  • 10.3k
9 votes
Accepted

Shall I use enum when are too many "categories" with PostgreSQL?

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 ...
user avatar
8 votes
Accepted

Importing SQL Server database from a .sql file

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 ...
user avatar
8 votes

InnoDB import performance

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: ...
user avatar
  • 14.2k
8 votes

How to import blanks as nulls instead of zeros while importing txt using wizard

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 ...
user avatar
  • 15.4k
8 votes

csv file with fields having commas in them

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. ...
user avatar
  • 30.9k
8 votes

Directly import a csv gzip'ed file into SQLite 3

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 ...
user avatar
  • 181
7 votes

How to import a .sql file in MySQL?

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> > <...
user avatar
  • 191
7 votes

How to import blanks as nulls instead of zeros while importing txt using wizard

IF you have at least SSMS v 17.3 and are importing to a newly created table you can use the Import Flat File to SQL Wizard for this. Running it against the CSV file in Tom's answer. With the ...
user avatar
  • 78.5k
7 votes

Directly import a csv gzip'ed file into SQLite 3

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 ...
user avatar
  • 171
7 votes

Import in postgres json data in a csv file

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; ...
user avatar
  • 56.7k
7 votes
Accepted

Easiest way to import access db (.accdb) into SQL Server 2016?

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 ...
user avatar
7 votes

Fast way to import 2GB csv file into SQL Server 2008

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 ...
user avatar
7 votes

Does it save anything to use a smaller varchar or nvarchar size instead of some large value like 4000?

Presumably, the point of importing the data is to allow someone to use it. If so, then someone has to figure out how much space to allocate. And in the vast majority of cases, it makes much more ...
user avatar

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