SQL Server Management Studio - Create queries, Export, Import

Question

Hello,
What is the best way to create a SQL query with the studio? Is there a wizard similar to Office Access?

I have a data model with primary key, foreign key. Can I have the complete database model displayed visually? If yes how?

I have to fill the database from C#. How do I get the primary key so that I can put it in the subtable as a foreign key?

How do I best add this under C#.
Do I read a text file for the INSERT and replace the variables or do I create the complete INSERT string under C#. Which variant is better, what do the experts recommend?

I add something and later notice that another function caused an error. Is there a rollback and how do I access it correctly?

Thanks for the answers and sample code.

---

How can I export a database of data? How can I import this with data to enable testing? How does it work under the studio?

Which menu items do I have to click?

Regards Markus

Answer 1

Attached is some test code that I had around and which demonstrates using output parameters. I have already pointed you to an article where I show how to use table-valued parameters.

I am very much deliberately given you incoherent pieces of examples. There are two reasons for this: 1) There is a limited amount to time I am willing to give free training to an individual. 2) I think you learn better by getting examples that you can explore on your own than if you are spoonfed code.

You will need to rename the attached file to have a .cs extension; the forum only accepts a limited set of file types.

160482-transactionscope.txt

Answer 2

SQL Server Management Studio (SSMS) has visual tools for building queries.

SQL Server Management Studio Query Designer

.NET contains data access libraries for SQL. This subject is vast but thoroughly covered in any beginning level C# data tutorial.

Quickstart: Use .NET and C# in Visual Studio to connect to and query a database

SSMS has import and export tools. I recommend installing SSMS and reading the official docs to learn the basics.

Import Flat File to SQL Wizard

Answer 3

What is the best way to create a SQL query with the studio?

You type them.

Is there a wizard similar to Office Access?

There is. Ctrl-Shift-Q or Query->Design Query in Editor. It is a piece of junk in my opinion. Unless they have improved (which I doubt), there are quite a few things you can do in a query which the Query Designer does not recognize.

I have a data model with primary key, foreign key. Can I have the complete database model displayed visually? If yes how?

Open the node for a database in Object Explorer, and the top node is called Database Diagrams. Start with right-clicking to install diagram support.

However, it has been kind of flaky lately. When SSMS 18 first came out, Microsoft had actually taken it out, but they put it back on popular demand. However, it often seems to cause crashes. If you bet hard on diagrams, you may prefer to install SSMS 17 for that purpose. (You can have SSMS 17 and 18 installed in parallel.)

I have to fill the database from C#. How do I get the primary key so that I can put it in the subtable as a foreign key?

The easiest would be to provide the PK value from the C# program... Although it depends on what your scenario is. Are you talking about an initial fill? (Please bear in mind that people in forums are not mind readers.)

Do I read a text file for the INSERT and replace the variables or do I create the complete INSERT string under C#. Which variant is better, what do the experts recommend?

You do definitely not form an INSERT statement with the values generated into the string. There are tons of problems with this, including that it is difficult to get right.

Not sure what you mean with text file. But if you are going to install some initial data, it may be better to reading them from a file, rather than having them coded into the program, since then you can change the data without having to recompile the program. But that is up to you.

The most efficient way to send the data is through a table-valued parameter. Single INSERT statements may be OK if it is only a handful of rows, but not if its thousands of them. I have an article on my web site about using table-valued parameters: https://www.sommarskog.se/arrays-in-sql-2008.html.

I add something and later notice that another function caused an error. Is there a rollback and how do I access it correctly?

If you have a transaction, and you have proper error handling, the transaction can be rolled back. Once the transaction has been committed, there is no built-in undo, if that is what you are asking.

How can I export a database of data? How can I import this with data to enable testing? How does it work under the studio?
Which menu items do I have to click?

There are quite a few options, too many one may argue. If you right-click a database in SSMS and select Tasks, you will find two import alternatives at the bottom and one export alternative. For more advanced operations you can use SSIS (SQL Server Integration Services), which I don't master myself. You can also use the command-line tool BCP for export and import. The advantage with the latter is that it is easier to automate, whereas the wizards may be simpler for one-off operations. (Unless you already have learn to master BCP and the related BULK INSERT command. Then you will always prefer them over the wizards.)

Answer 4

Hi @Markus Freitag ,

What is the best way to create a SQL query with the studio?

please refer to this doc: open an editor,
The simplest method generally used:

Can I have the complete database model displayed visually?

choose the database you want in Object Explorer

The Add Table dialog box appears, and then select the required tables in the Tables list and click Add.

I add something and later notice that another function caused an error. Is there a rollback and how do I access it correctly?

If you are afraid of error, recommend that backup your database

How can I export a database of data? How can I import this with data to enable testing?

choose the database you want in Object Explorer

import and export wizard will appear, you can refer to this article

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Without IDENTITY, how do I get the OrderID, ProductID?

@OrderID is an OUTPUT parameter of the procedure. The product ID is of course an input parameter - products are not created when you register an order.

I can't produce any screenshot - this was basically made-up code. But you can test the procedure with something like this:

DECLARE @products dbo.ProductsEntry_type 
INSERT @products(ProductID, Quantiy, Price)
   VALUES(1, 10, 7.20),
       (2, 20, 9.45)

DECLARE @OrderID int

EXEC dbo.AddOrder 18, 12, 1.20, @products, @OrderID OUTPUT

SELECT @OrderID