How to upload multiple csv files to sql server without constrains

Posted on

Question :

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.

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.

Leave a Reply

Your email address will not be published.