sql

CSV (or sheet in XLS) to SQL create (and insert) statements with .Net?

Does anyone have a technique for generating SQL table create (and data insert) commands pragmatically from a CSV (or sheet in a .xls) file? I've got a third party database system which I'd like to populate with data from a csv file (or sheet in a xls file) but the importer supplied can't create the table structure automatically as it ...

is it better to structure an SQL table to have a match, or return no result

I've got an interesting design question. I'm designing the security side of our project, to allow us to have different versions of the program for different costs and also to allow Manager-type users to grant or deny access to parts of the program to other users. Its going to web-based and hosted on our servers. I'm using a simple All...

SQL Pronunciation

I have a question about the pronunciation of the SQL word. In my native language (French) we used to say it like spell each letters. I've been listening to the stackoverflow podcast today. And I noticed the usage of the word sequel to describe SQL. My question is what is the common or correct pronunciation of SQL in english. Is it a ma...

Why are relational set-based queries better than cursors?

When writing database queries in something like TSQL or PLSQL, we often have a choice of iterating over rows with a cursor to accomplish the task, or crafting a single SQL statement that does the same job all at once. Also, we have the choice of simply pulling a large set of data back into our application and then processing it row by r...

What's the fastest way to bulk insert a lot of data in SQL Server (C# client)

I am hitting some performance bottlenecks with my C# client inserting bulk data into a SQL Server 2005 database and I'm looking for ways in which to speed up the process. I am already using the SqlClient.SqlBulkCopy (which is based on TDS) to speed up the data transfer across the wire which helped a lot, but I'm still looking for more. ...

(N)Hibernate Auto-Join

I'm developing a web- application using NHibernate. Can you tell me how to write a NHibernate Query for the following SQL query: select v1.Id from View v1 left join View v2 on v1.SourceView = v2.Id order by v1.Position It's basically a auto-join but I don't know how to write this in Nhibernate. Lets say the property names are the same ...

SQL many-to-many matching

I'm implementing a tagging system for a website. There are multiple tags per object and multiple objects per tag. This is accomplished by maintaining a table with two values per record, one for the ids of the object and the tag. I'm looking to write a query to find the objects that match a given set of tags. Suppose I had the following ...

Difference between EXISTS and IN in SQL?

I would like to know the difference between the EXIST and IN clause in SQL? When should we use EXIST and when IN should be used? Thank you. ...

What's optimal? UNION vs WHERE IN (str1, str2, str3)

I'm writing a program that sends an email out at a client's specific local time. I have a .NET method that takes a timezone & time and destination timezone and returns the time in that timezone. So my method is to select every distinct timezone in the database, check if it is the correct time using the method, then select every client ou...

Asynchronous Stored Procedure Calls

Is it possible to call a stored prodcedure from another stored procedure asynchronously? Edit: Specifically I'm working with a DB2 database. ...

SQL Insert into ... values ( SELECT ... FROM ... )

I am trying to insert into a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL-Engine of the day (MySQL, Oracle, SQLServer, Informix, DB2). I've been wondering if there is a silver-bullet syntax coming from an SQL S...

MS SQL Server 2008 "linked server" to Oracle : schema not showing

I have a Windows 2008 Server (x64) running Microsoft SQL 2008 (x64) and I'm creating a Linked Server connection to an Oracle server. I'm able to make the connection, but I cannot see any information regarding which schema a table belongs to. In SQL 2005, my linked servers show the schema information as I would expect. Does anyone know...

What are the benefits of using partitions with the Enterprise edition of SQL 2005

I'm comparing between two techniques to create partitioned tables in SQL 2005. Use partitioned views with a standard version of SQL 2005 (described here) Use the built in partition in the Enterprise edition of SQL 2005 (described here) Given that the enterprise edition is much more expensive, I would like to know what are the main be...

Is there a Box Plot graph avalible for Reporting Services 2005?

Is there a Box Plot graph, or box and whisker graph available for Reporting Services 2005? From the looks of the documentation there doesn't seem to be one out of the box; so I am wondering if there is a third party that has the graph, or a way to build my own? ...

SQL Group By with an Order By

I have a table of tags and want to get the highest count tags from the list. Sample data looks like this id (1) tag ('night') id (2) tag ('awesome') id (3) tag ('night') using SELECT COUNT(*), `Tag` from `images-tags` GROUP BY `Tag` gets me back the data I'm looking for perfectly. However, I would like to organize it, so that the ...

Can I maintain state between calls to a SQL Server UDF?

I have a SQL script that inserts data (via INSERT statements currently numbering in the thousands) One of the columns contains a unique identifier (though not an IDENTITY type, just a plain ol' int) that's actually unique across a few different tables. I'd like to add a scalar function to my script that gets the next available ID (i.e....

SQLServer DB Results not what expected

I have a sql server database that I am querying and I only want to get the information when a specific row is null. I used a where statement such as: WHERE database.foobar = NULL and it does not return anything. However, I know that there is at least one result because I created an instance in the database where 'foobar' is equal to nu...

Linq To SQL: Can I eager load only one field in a joined table?

I have one table "orders" with a foreing key "ProductID". I want to show the orders in a grid with the product name, without LazyLoad for better performance, but I if use DataLoadOptions it retrieves all Product fields, which seams like a overkill. Is there a way to retrieve only the Product name in the first query? Can I set some attr...

How do I deal with quotes ' in SQL

I have a database with names in it such as John Doe etc. Unfortunately some of these names contain quotes like Keiran O'Keefe. Now when I try and search for such names as follows: SELECT * FROM PEOPLE WHERE SURNAME='O'Keefe' I (understandably) get an error. How do I prevent this error from occurring. I am using Oracle and PLSQL. ...

How to select an SQL database?

We're living in a golden age of databases, with numerous high quality commercial and free databases. This is great, but the downside is there's not a simple obvious choice for someone who needs a database for his next project. What are the constraints/criteria you use for selecting a database? How well do the various databases you've ...