SQL Server basics

I have a Dataset containing a DataTable (say, 'dt1') that has all the fields that are contained in a SQL Server database's table. In addition, it contains a field (let's call it 'HasItems') of type boolean. I want to populate 'dt1' with all the records in the SQL Server table, and populate the 'HasItems' field with True if matching records exist in another SQL Server table (actually a view), and False if no matching records exist. I could do this easily in Access, but I'm new to SQL Server, and need to know the best way to go about it. (The way I'm doing it now has me thinking, "First the earth cooled, then the dinosaurs came...").

Specific help would be appreciated, but also a reference I could use to ramp up my SQL Server skills would be nice too.

Thanks.

Mary
Tue Jan 20 17:18:58 CST 2004

There isn't all that much difference between the way you do things
using Jet and SQL Server if you use SQL statements. Access syntax
varies only for things like crosstab queries, not so much for basic
SELECT, INSERT, UPDATE and DELETE statements. If you want to operate
on the rows in the dataset one at a time, you have to walk through
them, apply your logic, then execute an update or whatever to the SQL
Server table. One way to do this is to write a stored procedure and
pass values derived from the dataset in as parameters and execute the
stored procedure once for every row. You don't say where the data in
the dataset came from or if it's bound to the same SQLS database,
which also makes a difference.

Before you proceed much further, you should probably do a little
research on ADO.NET (David Sceppa's ADO.NET is a good reference) and
also get a good book on using SQL Server that focuses on the
programming aspects (there's a gazillion of them out there -- search
amazon.com or your local bookstore). Also don't forget SQL BooksOnline
or the Microsoft SQLS home page at www.microsoft.com/sql.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 20 Jan 2004 11:31:12 -0800, "pmcguire"
<anonymous@discussions.microsoft.com> wrote:

>I have a Dataset containing a DataTable (say, 'dt1') that has all the fields that are contained in a SQL Server database's table. In addition, it contains a field (let's call it 'HasItems') of type boolean. I want to populate 'dt1' with all the records in the SQL Server table, and populate the 'HasItems' field with True if matching records exist in another SQL Server table (actually a view), and False if no matching records exist. I could do this easily in Access, but I'm new to SQL Server, and need to know the best way to go about it. (The way I'm doing it now has me thinking, "First the earth cooled, then the dinosaurs came...").
>
>Specific help would be appreciated, but also a reference I could use to ramp up my SQL Server skills would be nice too.
>
>Thanks.

anonymous
Wed Jan 21 07:41:05 CST 2004

Hi Mary,

----- Mary Chipman wrote: -----

"You don't say where the data in
the dataset came from or if it's bound to the same SQLS database,
which also makes a difference."

The data all come from the same SQLS database. What I've done is made a SQLDataAdapter whose select statement is a UNION between a query selecting all the records WITH matching records (and a calculated field -1 AS 'HasItems'), and a query selecting all records WITHOUT matching records (and a calculated field 0 AS 'HasItems'). But, As I said, this takes a long time to run, and I wonder if this is a bad way of going about things.

And thanks for the references.

Mary
Wed Jan 21 13:27:52 CST 2004

Union queries are never efficient, and the results are always
read-only. UNION ALL is a better choice because the server doesn't
have to make another pass through the data to eliminate duplicates.
It's hard to say whether there's a better choice (not knowing your
schema), but if you are unioning two large tables you have to expect
that it's going to take a while.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Wed, 21 Jan 2004 05:41:05 -0800, "pmcguire"
<anonymous@discussions.microsoft.com> wrote:

>Hi Mary,
>
> ----- Mary Chipman wrote: -----
>
> "You don't say where the data in
> the dataset came from or if it's bound to the same SQLS database,
> which also makes a difference."
>
>The data all come from the same SQLS database. What I've done is made a SQLDataAdapter whose select statement is a UNION between a query selecting all the records WITH matching records (and a calculated field -1 AS 'HasItems'), and a query selecting all records WITHOUT matching records (and a calculated field 0 AS 'HasItems'). But, As I said, this takes a long time to run, and I wonder if this is a bad way of going about things.
>
>And thanks for the references.
>

Mary
Thu Jan 22 15:20:50 CST 2004

In T-SQL, you'd use CASE for Iif, which is documented in SQL
BooksOnline. Here's an example of using it like you would Iif in
Access. The output would display either "above average" or "below
average". This code ain't perfect, but it gives you the basic syntax.

DECLARE @Avg money
SELECT @Avg = AVG(Price) FROM Products
SELECT Product, Price, Ranking =
CASE
WHEN Price > @Avg
THEN 'above average'
ELSE 'below average'
END
FROM Products
GROUP BY Product, Price

-- Mary
MCW Technologies
http://www.mcwtech.com

On Wed, 21 Jan 2004 12:26:07 -0800, "pmcguire"
<anonymous@discussions.microsoft.com> wrote:

>Hi Mary,
>
>Hmm. OK, well I found a better way, but not terribly ... elegant. I did an outer join, and turned 'HasItems' into a field of type string. Then I test for null or non-null values in 'HasItems' in the dataset using code. What I would LIKE to do, is have the null/non-null test be included into the original query to give me the boolean value for the column. Is this possible? Something like:
>
>SELECT iif(tbl2.ID IS NULL,FALSE,TRUE) AS 'HasItems' ...
>
>I haven't been able to get anything like this to work.
>
>Thanks for the help.
>
>Pat