Sql-server – How to upload multiple csv files to sql server without constrains

constraintcsvsql server

I backed up an old SQL Server DB with .CSV files for each table. The old DB is now gone; the .CSV files are all I have.

Now I want to create new DB and upload all those csv file (my old db removed) .

I looking for a way to upload multiple csv files via the sqlServer gui and without any constrain .

I want to disable the constrain because if not the sql will tell that I a problem with the FK

I thought to running this command EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" but I think it not help me because I need to running it before I upload all the csv files , and before I upload them I not have any table in my new DB
thank you all.

Best Answer

Using SSMS, you can run the "Import Data" command (right click DBv2, go to "Tasks", and select "Import Data...").

This will allow you to import one of your .CSV files into a new table. The column names, datatypes, and sizes will be set by default from what's in the .CSV file; you can view and edit the actual CREATE TABLE command before you run the import. This would let you set up IDENTITY columns and primary keys immediately. If you're using an identity column, do make sure you tell it to keep the existing values.

Once all tables are loaded, you can go back through and set up the foreign key relationships.

Alternately, if you're careful about the order in which you load the tables, you could even set the foreign key constraints up in the CREATE TABLE statement, as long as the referenced tables have already been loaded.

Note that this will be a somewhat tedious process, if you have a large number of tables to load. You would need to set each one up individually, manually.

Most of the ways I can think of to automate this would require that the data already be in a database, instead of in a number of .CSV files. Of course, you've said you want to use the GUI tools anyway.

Alternative

An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.

CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:

INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;

MySQL output file location – accessing server files

If this is a one-time thing, you can do the following to export the result into csv file using MySQL workbench:

SELECT * FROM mytable

then the result will appear in the result table (area) all you need to do use export button in that area:

enter image description here

Otherwise, if you want to create output file you can do it by using a shared folder:

if its over a network //my_pc/folder/test.csv that both machines have read/write access
and if its under the same server use for example d:/test.csv

and save the file into any location you want.

Best Answer

Related Solutions

Postgresql – Getting error while importing large data through csv

Alternative

MySQL output file location – accessing server files

Related Question