Импорт в sql с ошибками

The answer to your question might involve the HOW data is stored in Excel.

I just ran into the same problem. I had a column containing two types of values: numeric and numeric with a dash. (ex. 51000 and 2009-00949) I needed to import them as either NVARCHAR or VARCHAR, but the SQL import wizard (SQL2005) would only import the numerics and not the numerics with a dash or the numerics with a dash and not the numerics, depending on which type was in the first record. The values which were not imported would have a ‘null’ value in the field, rendering my records useless.

I tried using the Format|Cells menu option in Excel (numeric, text, general, etc) and various mapping options in SQL (nvar, nvarchar, varchar, sqlvariant, float) without success.

The solution was to use the Data|Text To Columns option in Excel, not the cell formatting option to store the numeric value as text. When I used this on the column, the numeric values (ex. 51000) converted to numbers stored as a text value (little green triangle error appeared in the upper left corner), the numerics with a dash remained as text. I had to leave the data in this format, with the little green triangle error, otherwise it would not successfully import to SQL. When the data in the column was stored in this format, the two types of column data (numeric and numeric with a dash) imported correctly with the other record data, no null values in the column.

One Click Feedback

Please help us to improve the site by rating the quality of this article
by clicking a button below.

The SQL Server Import and Export Wizard is useful for copying data from one data source (e.g. a SQL Server database) to
another. Although the interface is fairly simple there are a few “gotchas” to be aware of. Here are a few issues I’ve
found while loading data from one SQL Server database into another.

Identity Columns

The wizard doesn’t treat identity columns any differently to other columns, so will usually fail when trying to insert data into a
table that has an identity column. However the error message can be a bit misleading :

The issue here is that the ‘SystemInformationID’ column in this table is defined as an identity column, although that’s
not immediately obvious from the error message. Fortunately there is an easy solution in that you can select an option in
the wizard to allow specific values to be inserted into the identity column, much like you can with a SQL query. To do this
select the relevant tables in the ‘Select Source Tables and Views’ page by clicking the checkbox in the header for all tables,
or you can just select which tables you want copying. Make sure the tables you want are actually selected (i.e. not just
checked). In my case I’ve just selected all the tables in my database :

Click on the ‘Edit mappings…’ button towards the bottom of the screen and the following window should appear:

If you select the ‘Enable identity insert’ as indicated in the picture above then the values of any identity columns will
simply be copied across from the source database.

Timestamp Columns

The wizard will also attempt to copy any timestamp columns in the same way it would for a column of any other data type. Unfortunately
timestamp columns can’t be explicitly set to a specific value so this will always fail. If you try it then you will probably get an
error message like this one (obviously the column name will be different for you) :

Again the error is perhaps a bit misleading as there is no mention of ‘timestamp’, but row version is just a
synonym for timestamp. The solution is to not copy any columns that are timestamps. To do this you just need to click
the ‘Edit Mappings…’ in the ‘Select Tables and Views’ screen for the table in question. This should display a screen
similar to this one :

As you can see in my table the ‘LastUpdated’ column is a timestamp column. To stop the error occurring just set the
destination to ‘ignore’ in the drop down that appears when you click on that cell :

If there is more than one table with a timestamp you’ll need to repeat this for each table.

Constraints

If the table has foreign key constraints on then the chances are you will get a constraint failure message at some point. The Wizard
does not load tables in any specific order for constraints, so it is quite possible that the foreign key table will get loaded
before the table it refers to is loaded, causing a foreign key constraint failure. The error message will be something like :

“The INSERT statement conflicted with the FOREIGN KEY constraint».

The error in the Wizard will probably be similar to :

The easiest way to get around this is to disable constraints during the load and then re-enable them again afterwards.

To disable all constraints on a specific table run the following SQL (in this case for the Address table) :

ALTER TABLE Address NOCHECK CONSTRAINT ALL

However you’ll need to disable constraints on all tables to be sure of avoiding errors. You can do this with the undocumented stored
procedure sp_MSforeachtable as follows :

EXEC sp_MSforeachtable @command1=»ALTER TABLE ? NOCHECK CONSTRAINT ALL»

This will disable all constraints on all tables. Once the data has been loaded constraints can be re-enabled with the following :

EXEC sp_MSforeachtable @command1=»ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL»

The ‘WITH CHECK’ option specifies that the data is validated against the constraint.

Triggers

If you have triggers in your database then these will not fire when using the import wizard. If you were relying on the
trigger code running then you will need to do this manually once the data is imported.

Added March 2016 :

202 and 200 Data Conversion Errors

When importing or exporting data using a query, the VARCHAR data type is incorrectly recognised as a ‘200’ data type and the NVARCHAR
as a ‘202’ data type. This causes the wizard to fail, with a message similar to that below in the ‘Review Data Type Mapping’ screen.

This is a bug in some versions of SSIS and it only occurs when the data source is a query (rather than a table or view).
The workaround I use is to insert the data from the query into a table, and then use the table as the data source. This avoids
the need to use a query for the data source. An alternative is to create a view that references the query and use that as the data source.

In both cases the table or view can be deleted once the data has been imported.

Битые символы в базе данных образуются, в большинстве случаев, из-за несовпадения кодировок в файле источнике, клиенте БД или таблице БД. Пройдемся по проблемным местам, один из пунктов ниже обычно помогает решать проблемы с «кракозябрями».

1. Файл-источник данных.

С этим пунктом все просто, текстовые данные в файле нужно хранить в utf8. Проверить кодировку можно в большинстве популярных IDE, например, в PhpStorm или текстовом редакторе наподобие Notepad++.

Если нет прямого доступа к БД, то в файл-источник данных следует прописать:

SET NAMES 'utf8' COLLATE 'utf8_general_ci'; -- Для utf8

или

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci'; -- Для utf8mb4

2. Кодировка и формат сопоставления данных в таблице.

Вероятнее всего вы используете utf8 (он же utf8mb3) или utf8mb4 для хранения данных в MySql. Поэтому при создании таблиц желательно напрямую указывать это, т.к. не всегда общие настройки БД разрешается менять.

Для utf8:

CREATE TABLE `example_table`
(
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    example_field VARCHAR(255) NOT NULL
) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB;

Для utf8mb4:

CREATE TABLE `example_table`
(
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    example_field VARCHAR(255) NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;

3. Импорт данных из файла-источника.

При импорте тоже желательно указывать кодировку.

Для utf8:

mysql -u USER_NAME -p --default-character-set=utf8 DB_NAME < SOURCE_FILE.sql

Для utf8mb4:

mysql -u USER_NAME -p --default-character-set=utf8mb4 DB_NAME < SOURCE_FILE.sql

4. Веб-интерфейсы.

Иногда требуется заполнять БД из веб-интерфейсов и в этом случае стоит проверять отправляемую кодировку с сервера в заголовках и HTML-коде.

HTML:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

PHP:

header('Content-Type: text/html; charset=utf-8');

Полезные команды

Отображение форматов сопоставления данных в MySql:

SHOW VARIABLES LIKE 'collation%';

Отображение настроек кодировок:

SHOW VARIABLES LIKE 'character%';