Storing and retrieving data from sql server express through sql developer

Question

Hello Expert,
I need to upload data from excel sheets to database relational tables and need to perform various sql queries. Also need to export data into excel sheets from sql resultsets.
Can I install sql server express and can I access this relational db through sql developer?

Thanks,
Rajneesh

Answer 1

Hi,

I need to upload data from excel sheets to database relational tables and need to perform various sql queries.

Why not perform the queries directly in excel? You can use Power Query for the task

Are you familiar with the phrase XY problem?

https://en.wikipedia.org/wiki/XY_problem

The XY problem is a communication problem where the question is about an end user's attempted solution (Y) rather than the root problem itself (X).

I think that the reason that The question is not very clear, which I agree with Erland, is related to the fact that you already thinking about specific solution while the source needs is not fully clear.

We can present you multiple solution to do this task, but it will be better if you can explain your exact source needs. What do you have now and what is your final goal

need to export data into excel sheets from sql resultsets.

So, is this the same data which you get after execute queries on the source data which come from the excel? Do you mean that you move to the data from excel only to get it back to excel after some parsing?

There are several direct answers to this step "export data into excel sheets from sql result sets"

Option 1: create a view in the sql server with the requested result set -> use SQL Server Management Studio Export tool to export the data in the view into excel as explained in this post.

Option 2: Export the data to csv file from SQL Server using bcp and open the csv file using excel

Option 3: [probably best option] Instead of export the data from SQL Server, import the data from the excel.

Select the Data tab on Excel's Ribbon, then within the "Get External Data" choose the "From other Sources" drop-down. Then Choose "From Microsoft Query"

Within "Choose Data Source" pop-up box, select "new data source" and click "OK"

Fill the form to use SQL Server

fill the connection information like in SSMS

click OK on all windows until you back the "choose data source" window

When you click OK you will be prompt to use the wizard -> close the window and select to use query

Here you can use any query you want and get any result set directly to the excel

Answer 2

The question is not very clear. Why would you install SQL Server Express and then retrieve the data from SQL Server Developer Edition? Or do you have something else in mind when you say "sql developer"?

But let's make one thing clear: SQL Server Express is licensed for production use, Developer Edition is not. So if you are planning to import these Excel files to actually use the data for something, Developer Edition is not really appropriate.

As for importing the Excel files, there are several options. The easiest to use may be the Import/Export wizard, but it is maybe less suitable for automation.

Answer 3

Microsoft SQL Server is indeed a relational database. Express is just one of the editions of SQL Server. As for whether you can query SQL Server databases from Oracle tools, is a question that is better asked in an Oracle forum. But I doubt that any of the tools from Oracle knows how to connect to SQL Server. The Oracle engine probably has support for connecting to other data sources.

I believe that Toad supports access to SQL Server, but I have never used it. Again, that may be a question for Quest forum.

The two most popular tools for accessing SQL Server databases is SQL Server Management Studio and Azure Data Studio. The former is Windows only, the latter also runs on Linux and Mac. They are both free downloads.