SQL Training

January 24, 2013 by Joey Blue Leave a Comment

Simple Trick to Copy and Paste Web Data into SQL Server

When answering questions on forums or blogs, I often like to put the data into SQL Server where I can make sure my SQL works before I answer a question.

As you can imagine, I don’t want to spend a lot of time importing that data.

So, here is a little trick that you can use to copy and paste and HTML table into a SQL table without having to write insert statements or use the Import/Export wizard.

Video: SQL Server Import Data with Copy and Paste

How to Import Web Data into SQL Server with a simple copy and paste. No special import and export tools needed.

Youtube Video Location: Import HTML Table Data To SQL Server with Copy Paste

Here are a few other posts you might enjoy:

SQL Microsoft Excel to Windows Azure SQL in 10 minutes | Troubleshooting a SQL Join | How to Remove Duplicate Rows in SQL Server

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

January 18, 2013 by Joey Blue 1 Comment

SQL Not Like with Multiple Values

I came across a forum post where someone wanted to use SQL NOT LIKE with multiple values.

They were trying to exclude multiple values from the SQL query, but they were needing to use wildcards.

If you wanted to just filter values without wildcards, you would use the following query.

select *
from table1
where column1 not in ('value1','value2','value3');

The only problem was that they needed to compare using the LIKE operator.

It would be nice if you could just stick some wildcards in the in clause like this:

where column1 not in ('%value1%','%value2%','%value3%')

But, you can’t stick a wildcard inside of the IN clause. So, here is the easiest solution.

select *
from table1
where column1 not like '%value1%'
and column1 not like '%value2%'
and column1 not like'%value3%';

If you want to play around with the Boolean logic, you rearrange the query like this.

select *
from table1
where not (column1 like '%value1%'
or column1 like '%value2%'
or column1 like'%value3%');

SQL Not Like with Multiple Values

What happens if you don’t put those parenthesis in?

Here are a few other posts you might enjoy:

SQL Wildcards | SQL Multiple Filter | How to use the SQL In Statement with Subquery

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

January 10, 2013 by Joey Blue Leave a Comment

How to test numeric in SQL Server

Sometimes you will run into a database table that is storing numeric data inside of a varchar or nvarchar column.

This is not recommended, but it does happen. IsNumeric SQL Server

Normally you would store numeric data in a numeric or decimal column and the database will make sure the data has only numbers.

If the data is in a nvarchar column, then the database won’t care if the data is a number or a date or strings/characters.

In this case you will need to check manually to see if the column only has numeric data.

IsNumeric is a function in SQL Server that you can use.

Here’s a script to show you how to use it.

create table products
(
item nvarchar(100),
price nvarchar(100)
)
go
insert into products
select 'product1','-100'
union
select 'product2','25.42'
union
select 'product3','test'
union
select 'product4','0.01'
go

select item,price,isnumeric(price) numeric_check
from products;

Isnumeric function results

select item,price,isnumeric(price) numeric_check
from products
where isnumeric(price) = 0;

Isnumeric function where filter

Note that there are a few problems with the IsNumeric Function:

http://www.sql-server-performance.com/forum/threads/problem-with-isnumeric.11181/

http://www.simple-talk.com/blogs/2011/01/13/isnumeric-broken-only-up-to-a-point/

Here are a few other posts you might enjoy:

How to Filter for SQL Null or Empty String | SQL Functions | SQL Where

December 10, 2012 by Joey Blue Leave a Comment

Where are Statistics Used?

Video: Statistics, Big Data, and the Year 2013

How do statistics and big data effect everything you touch and consume? This video is a good overview of where companies and government use statistics.

[sharebox4 sharetext=”Share This Page”] [/sharebox4]

Big Data has been in the limelight for a while now. Venture capitalist are throwing their money at it. Large corporations are rolling it into their marketing mix.

But, statistical analysis is the vehicle that turns all of that data into information.

Where are statistics used?

All over the world statistics people use to squeeze out efficiencies and to discover new markets and products.

Manufacturing
Keep Children Healthy
Optimize Communication Networks
Reduce Customer Churn
Reduce Emissions
Product Delivery
Sports Statistics
Government Agencies
Election Forecasting

This video produced by SAS for the 2013 International Year of Statistics has a good overview of how statistics have an effect on our everyday lives.

November 15, 2012 by Joey Blue Leave a Comment

Microsoft Excel to Windows Azure SQL in 10 minutes

Video: Microsoft Excel to Windows Azure SQL in 10 minutes – SQL Training Online
In this video, I walk you through setting up a Windows Azure SQL Database and then importing some Microsoft Excel data. I show you a trick to get the SQL Server Management Studio Import/Export Wizard to work for a Windows Azure connection.

How do you import data into Windows Azure?

There is a pretty easy way to import data into Windows Azure using the SQL Server Management Studio Import/Export wizard. But, you have to use a little trick I came up with to make it work.

I found a sample project over at CodePlex by Derrick VanArnam that demonstrates how to import Microsoft Excel data into Windows Azure.

It has a sample Microsoft Excel file and a Microsoft Word document that walk you through using SQL Server Integration Services (SSIS) for importing the Excel file into Azure.

In the video, I cover the following.

• Login to Windows Azure

• Create a SQL Server

• Create a Database

• Connect with Management Studio

• Show you a trick for Import/Export Wizard

• Move Data from Excel to Windows Azure

After you create a Windows Azure database, you will have to allow your IPAddress access through the Azure firewall.

Then, you open SQL Server Management Studio and connect to your database.

Now, create a new Azure database with the following script.

CREATE DATABASE [Olympics]
GO

It’s now time for the trick I mention.

By default, you won’t see the Import/Export wizard on the menus in the Azure connection.

You have to open up another connection in your Object Explorer in Management Studio. You should make this new connection a non-Azure database.

Then, you will see the Import/Export wizard.

Next, you will need to connect to the Excel File and the Azure Database.

One last trick, is to make sure your new tables have a clustered index.

Insert the following into each of your excel create table scripts as the first column.

id int identity primary key,

Finish the wizard and watch the data go into the tables.

That’s it. That is how you import data from Microsoft Excel to Windows Azure SQL database.

*Let me know what you think by commenting or sharing on twitter, Facebook, google+, etc.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline