1

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.

Improve this question
6
  • 1
    Is there a specific reason not to use a SQL backup of the database? This would let you create an exact copy of the database, with all data and constraints intact. That said, if you're loading each of the .CSV files into new tables, the tables won't have constraints until you add them; just don't add them until all the data is in place (if you know the existing data will all line up correctly, with no missing parent values in the foreign key data).
    – RDFozz
    Jul 17, 2018 at 16:08
  • Why not back up your SQL Server with the backup / restore process? Your code is trying to alter tables removing all of your constraints which could be potentially really bad. You could just create a new DB and then create new tables by uploading the files, what your code is describing is changing the current tables.
    – Shaulinator
    Jul 17, 2018 at 16:09
  • @RDFozz , I already back all table, each table as csv file and my old DB delete , so I can't use now the original backup/restore
    – ffdebugger
    Jul 17, 2018 at 16:15
  • @ffdebugger - I've edited your question to clarify that the .CSV files are the only remaining record of the old DB.
    – RDFozz
    Jul 17, 2018 at 16:25
  • @ffdebugger - I mean that you said the old DB has been deleted, so anything options to move the data using DBv1 are gone - all you've got is the .CSV files. Did I misunderstand your message? If so, you'll want to roll back my edit.
    – RDFozz
    Jul 17, 2018 at 16:38

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • "Import Data" basically creates an SSIS package to load the .CSV file. You could modify that SSIS package, adding the remaining .CSV files as additional data sources, and setting each one up to load into a new table in the database. That would let you perform the load as a single operation. You still have to set everything up one by one, though.
    – RDFozz
    Jul 17, 2018 at 16:46
  • Solid suggestion that would make it easy for OP, albeit tedious. I found a dynamic bulk insert script that would possibly also help OP depending on their comfort with scripting knowledge.
    – Shaulinator
    Jul 17, 2018 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.