SQL Server Magazine

SQL Server 2005 Express has several methods that you can use to import or export data. Take a look at the options and see how to use the easiest method, the SQL Server Import and Export Wizard. Plus, learn to use inner and outer joins and check out a link that shows how to use SMO to back up SQL Server Express.

Subscribe to SQL Server Magazine and make sure you add sql_express@lists.sqlmag.com to your list of allowed senders and contacts.

• Protect SQL Server with Microsoft’s Data Protection Manager
• Free Brief: Personal HP Workstations = Higher ROI?
• Hosted Security for Small or Medium-Sized Businesses

Contents:

April 2, 2007

Commentary

• Importing and Exporting Data

Features

• Jump Start: Inner and Outer Joins
• Check It Out: Backup and Restore with SMO

Featured White Paper

• Ensure That Your Organization Complies with Regulations

Exclusive Email Offers

• Introducing a Unique Security Resource
• Grab Your Share of the Spotlight!

Protect SQL Server with Microsoft’s Data Protection Manager

Learn how you can use Microsoft System Center Data Protection Manager (DPM) v2 to extend the native SQL Server feature set to provide continuous data protection with byte-level replication and integrity checking. From business case to recovery scenarios, this whitepaper demonstrates how DPM v2 will integrate with your SQL Server infrastructure.

Download your version of the white paper now.

Importing and Exporting Data
by Michael Otey, mikeo@windowsitpro.com

Although some simple databases can start off completely empty, in most cases you need to preload tables by using external data from flat files or other databases. Likewise, once your database has been in use for a while, you’ll probably need to transfer data to Microsoft Excel or some other program. SQL Server 2005 Express has several methods that you can use to import or export data. The primary tools that ship with SQL Server Express are the bcp utility, the T-SQL BULK INSERT statement, and the T-SQL OPENROWSET statement. However, for most SQL Server Express users, the best tool for the job is the SQL Server Import and Export Wizard.

Read the full article (free to registered users) at:
http://www.sqlmag.com/Article/ArticleID/95654/sql_server_95654.html

Free Brief: Personal HP Workstations = Higher ROI?

Discover why financial services executives get a LOT more out of their IT investments by investing in HP Personal Workstation Technology. Quickly learn how workstations ensure accuracy and security while driving down short and long term operating costs. This quick- read guide is a must read today.

Jump Start: Inner and Outer Joins
by Michael Otey, mikeo@windowsitpro.com

In the last Jump Start column, “SELECT and JOIN,” you saw how to use a SELECT statement to retrieve just the specific columns that your application needs. In addition, you saw how you can use the SELECT statement with the JOIN keyword to join the contents of two tables together in a single result set.

An INNER JOIN statement basically joins two tables based on the contents of the values of a common column. To review, let’s take quick look at the example INNER JOIN from the last column:

SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate

 FROM Customers c INNER JOIN Orders o

 ON c.customerID = o.CustomerID WHERE c.customerID = 'ANTON'

The SELECT statement returns the CustomerID, CompanyName, OrderID, and OrderDate columns only when the CustomerID is equal to ANTON. In this statement, the two tables are joined on the CustomerID column that’s common to both tables. Because this example is an INNER JOIN, it takes all the records from the customers table and joins them to the matching records in the orders table. If no match is found--in other words, if there’s a customer with no associated orders--the customer data isn’t included in the results. If a customer has multiple orders, the statement will return one row for each order and repeat the customer data for each row.

As you might expect from the name, LEFT OUTER JOIN works differently than an INNER JOIN. Instead of returning results in which there are rows in both tables as the INNER JOIN does, an OUTER JOIN retrieves all of the records from one of the tables. There are three types of OUTER JOINS: LEFT OUTER, RIGHT OUTER, and FULL OUTER. Let’s look in more detail at the LEFT OUTER JOIN.

The LEFT OUTER JOIN limits its results to the rows in the "left" table. If a row in the left table has no matching row in the right table, the statement will still return a row in the result set. That returned row will have NULL values for the included columns from the right table. You can see an example of the LEFT OUTER JOIN in the following example.

SELECT c.CustomerID, c.CompanyName, o.OrderID

 FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID

Check It Out: Backup and Restore with SMO
You probably know that you can back up SQL Server 2005 Express databases by using SQL Server Management Studio Express (SSMSE). You might also know that you can back up databases by using the T-SQL BACKUP and RESTORE statements. However, you might not realize that you can also back up databases from your .NET applications by taking advantage of SQL Server Express’ SQL Management Objects (SMO) API. To find out more about using SMO to back up your databases and get a couple of useful code snippets, you should check out http://blogs.msdn.com/sqlexpress.

Hosted Security for Small or Medium Size Businesses

Is effective security out of reach for your small or medium-sized business? Imagine having a team of IT experts who only focus on security as part of your staff. Download this free must-have white paper today and find out how you can eliminate your company’s security risks.

Do you have visibility and control over your software licenses? Most organizations face serious challenges, including complex and confusing vendor licensing models, cost overruns, missed deadlines and business opportunities, and lost user productivity. Learn to address these challenges and prepare for audits. Register for this free on-demand Web seminar, available now!

You know you need to manage your email data, but how do you do it? What steps do you need to take? What additional measures should you enact? What shouldn't you do? Get answers to these and other questions and get control of your vital messaging data. Download this free eBook today!

Having customers depend on your IT services in order to communicate, purchase, or manage orders is great for your business. But what happens when your applications or Web sites become unavailable? Download this free white paper and learn how to eliminate application downtime disruptions and ensure the continuity of your business.

Are all your malware definitions completely up-to-date? If they are, then you're halfway home to total malware protection. Windows Vista might be the most secure Microsoft OS ever released, but malware is constantly evolving, and sometimes out-of-the-box security just isn't enough. In this exclusive podcast, Windows IT Pro Editorial and Strategy Director Karen Forster interviews Microsoft Product Manager Josue Fontanez about Forefront Client Security, Microsoft's unified malware protection package.
http://www.windowsitpro.com/go/podcast/coresecurity/forefrontclient/?code=0402e&r

How do compliance regulations really affect your IT infrastructure? You need to design your retention, retrieval, privacy, and security policies to ensure that your organization is compliant. Download this free eBook today and make certain that your organization complies with regulations!

Introducing a Unique Security Resource
Security Pro VIP is an online information center that delivers new articles every week on topics such as perimeter security, authentication, and system patches. Subscribers also receive tips, cautionary advice, direct access to our editors, and a host of other benefits! Order now at an exclusive charter rate and save up to $50!

Grab Your Share of the Spotlight!
Nominate yourself or a peer to become IT Pro of the Month. This is your chance to get the recognition you deserve! Winners will receive over $600 in IT resources and be featured in Windows IT Pro. It's easy to enter--we're accepting May nominations now, but only for a limited time! Submit your nomination today.