Skip to main content
62 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\...
Kin Shah's user avatar
  • 62.3k
29 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 ...
Michael Green's user avatar
24 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_unicode_520_ci in my SQL dump file.
Francesco Borzi's 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 ...
Solomon Rutzky's user avatar
20 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB. Don't use "_general_ci", as it does not correctly sort or compare ...
thomasrutter's user avatar
17 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 ...
Marcelo Luiz Onhate's user avatar
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 ...
Evan Carroll's user avatar
  • 63.3k
14 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) ...
Kin Shah's user avatar
  • 62.3k
11 votes
Accepted

Disable foreign key checks during import

The command SET FOREIGN_KEY_CHECKS=0; sets the value of a variable in a session scope. So it affects on the current session only, and do not affect on another sessions, both already running and ...
Akina's user avatar
  • 19.9k
11 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 ...
Erwin Brandstetter's user avatar
10 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 ...
Francesco Mantovani's user avatar
10 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, ...
hruske's user avatar
  • 610
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 ...
Aaron Bertrand's user avatar
9 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 ...
shr's user avatar
  • 191
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 ...
RolandoMySQLDBA's 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.
sepupic's user avatar
  • 11k
8 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 ...
humbads's user avatar
  • 181
8 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; ...
Evan Carroll's user avatar
  • 63.3k
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 ...
Martin Smith's user avatar
  • 84.7k
7 votes
Accepted

Export to format compatible with SQL Server

There are some issues with this request: What version and edition of SQL Server is the target system? How much data is being imported? 10k, 10 Mb, more? How many CSV files are there? You have stated ...
Solomon Rutzky's user avatar
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 ...
bwilliamson's 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 ...
Remus Rusanu's user avatar
  • 51.9k
7 votes

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

I fully support Erwin's answer, but I wanted to add a warning against enums. Enums are a good choice if you have a fixed number of possible values that can never change (at least there must be a ...
Laurenz Albe's user avatar
  • 51.9k
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 ...
Justin Cave's user avatar
  • 20.3k
6 votes

Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited

Polybase is powerful but not really that sophisticated so will not be able to deal with this weird format. You have three options as I see it: Correct the file format at source. Instead of having ...
wBob's user avatar
  • 10.4k
6 votes
Accepted

import 100 csv files into postgresql

You can use pgScript: DECLARE @i, @filename; SET @i = 1; WHILE @i <= 100 BEGIN SET @filename = 'C:\\Program Files\\PostgreSQL\\9.5\\data\\' + CAST(@i AS STRING) + '.csv' ; COPY location ...
joanolo's user avatar
  • 13.4k
6 votes
Accepted

Why does my database structure and SELECT operations generate NULLs?

There's a problem with your first NOT EXISTS. Let's take a look at your current query: INSERT INTO dzp.contractid(vnr) SELECT DISTINCT a.Vnr FROM dzp.accounts_stage a WHERE NOT EXISTS( SELECT ...
Lamak's user avatar
  • 2,576
6 votes

Why is BULK INSERT Considered Dangerous?

Another possibility is the impact of running a BULK INSERT operation. Normally, this sort of thing would be run off-hours when possible, so as not to interfere with normal activity. A bulk insert ...
RDFozz's user avatar
  • 11.6k
6 votes

Why is BULK INSERT Considered Dangerous?

This was kind of alluded to in an earlier answer ("... disable triggers"), but does not explain why disabling would be un-desirable from business standpoint. In many businesses, triggers on main ...
DVK's user avatar
  • 183
6 votes
Accepted

Character Conversion Issue Importing From CSV File

there are various words that are "written" to the database incorrectly. No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the ...
Solomon Rutzky's user avatar

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