SqlServer Lessons

INSERT INTO VALUES

The syntax for inserting data into a table one row at a time is as follows:

INSERT INTO “table_name” (“column1”, “column2”, …)
VALUES (“value1”, “value2”, …)

Assuming that we have a table that has the following structure,

Table Store_Information
Column Name Data Type
store_name char(50)
Sales float
Date datetime
and now we wish to insert one additional row into the table representing the sales data for Los Angeles on January 10, 1999. On that day, this store had $900 in sales. We will hence use the following SQL script:

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES (‘Los Angeles’, 900, ‘Jan-10-1999′)
INSERT INTO SELECT

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO “table1” (“column1”, “column2”, …)
SELECT “column3”, “column4”, …
FROM “table2”

Note that this is the simplest form. The entire statement can easily contain WHERE, GROUP BY, and HAVING clauses, as well as table joins and aliases.

So for example, if we wish to have a table, Store_Information, that collects the sales information for year 1998, and you already know that the source data resides in the Sales_Information table, we’ll type in:

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

Here I have used the SQL Server syntax to extract the year information out of a date. Other relational databases will have different syntax. For example, in Oracle, you will use to_char(date,’yyyy’)=1998.

 ——————

SQL Server: INSERT INTO with SubQuery
October 11, 2008 by decoding

Introduction

This is a short tutorial on how to insert data with a subquery on SQL Server databases.
The reason I am writing about it is that the required syntax is not that obvious!

Sample tables and data

Let’s create two simple tables for this tutorial with the names ‘Customers’ and ‘Orders’.
view sourceprint?
1 CREATE TABLE [dbo].[Customers]( 

2     [id] [int] IDENTITY(1,1) NOT NULL, 

3     [name] [varchar](100) NOT NULL, 

4     [username] [varchar](20) NOT NULL

5 )
view sourceprint?
1 CREATE TABLE [dbo].[Orders]( 

2     [order_id] [int] IDENTITY(1,1) NOT NULL, 

3     [customer_id] [int] NOT NULL, 

4     [order_date] [datetime] NOT NULL

5 )

We are also going to insert a new row on table ‘Customers’:
view sourceprint?
1 INSERT INTO Customers 

2 VALUES (‘John Smith’,’john.smith’)

Inserting data with a subquery

The obvious syntax to insert data into the orders table with a subquery would be:
view sourceprint?
1 INSERT INTO Orders 

2 VALUES ((SELECT id FROM Customers WHERE username = ‘john.smith’), GETDATE())

However if you try the above query, you would end up with the following message:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

The correct syntax to accomplish our task is:
view sourceprint?
1 INSERT INTO Orders 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

or if you would like to specify the columns:
view sourceprint?1 INSERT INTO Orders (customer_id, order_date) 

2 SELECT id, GETDATE() FROM Customers 

3 WHERE username = ‘john.smith’

————————————

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
—-Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
—-INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

———————————

sqlserver replace character in position :::::

Using REPLACE in an UPDATE statement

By Bill Graziano on 31 March 2010 | 12 Comments | Tags: UPDATE, Functions


This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.

Replace searches for certain characters in a string and replaces them with other characters. So this statement:

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'Rolls')

will return

SQLTeam.com Rolls!

REPLACE searches the the first string for any occurance of the the second string and replaces it with the third string. You can also do replacements of different sizes. For example,

 

SELECT Replace('SQLTeam.com Rocks!', 'Rocks', 'is cool')

gives us

SQLTeam.com is cool!

I replaced a five character string with a seven character string with no problem. If the string isn’t found, no changes will be made.

 

SELECT Replace('SQLTeam.com Rocks!', 'Yak', 'Tibetan bison')

returns exactly what we started with which is

SQLTeam.com Rocks!

If it doesn’t find anything to change it just returns the string unchanged. You can use REPLACE in an UPDATE statement. Using the pubs database we could write:

 

Update dbo.authors
Set    city = replace(city, 'Salt', 'Olympic');

There were two authors that had “Salt Lake City” in the CITY field. Now that field holds “Olympic Lake City” for those two authors. The CITY field is unchanged for all the other authors.

A more common approach is to use this in conjuntion with a WHERE clause like this:

 

UPDATE dbo.authors
SET    city = replace(city, 'Salt', 'Olympic')
WHERE  city LIKE 'Salt%';

This only affects the rows that start with ‘Salt’

—————————

  1. CREATE FUNCTION fnReplaceCharAtPos(@Str varchar(8000),@Pos int, @Chr char(1))
  2. RETURNS varchar(8000) AS  
  3. BEGIN 
  4. declare @Res varchar(8000)
  5. set @Res=left(@Str,@Pos-1) + @Chr  + right(@Str,len(@Str)-@Pos)
  6. return @Res
  7. END
  8.  

and call it in a query with

Expand|Select|Wrap|Line Numbers
  1. select dbo.fnReplaceCharAtPos(str,8,’Z’)
  2. from YourTable

—————————-

SQL Functions – STUFF() and REPLACE()

STUFF() can be used to stuff a string into another string. It inserts the string at a given position, and deletes the number of characters specified from the original string.

– © 2011 – Vishal (http://SqlAndMe.com)

DECLARE @string1 VARCHAR(20) = ‘Microsoft Server’

DECLARE @string2 VARCHAR(20) = ‘SQL Server 2005’

SELECT      @string1 + ‘ -> ‘ + STUFF(@string1, 11, 0, ‘SQL ‘)

            AS ‘String 1’,

            @string2 + ‘ -> ‘ + STUFF(@string2, 15, 1, ‘8 R2’)

            AS ‘String 2’

Result Set:

String 1                                 String 2

—————————————- ————————————-

Microsoft Server -> Microsoft SQL Server SQL Server 2005 -> SQL Server 2008 R2

(1 row(s) affected)

In the first string it inserts ‘SQL ‘ at specified position – 11, the third argument 0 indicated the number of characters to be deleted before inserting the new string.

For second string, we have deleted one (1) character starting from position 15, which deletes ‘5’, and then it inserts the new string at position 15 – ‘8 R2’.

REPLACE():

REPLACE() replaces all the specified characters with new characters.

DECLARE @string3 VARCHAR(35) = ‘sql 2005, sql 2008, sql 2008 r2′

SELECT @string3, REPLACE(@string3,’sql’,’SQL’)

Result Set:

———————————–      ———————————–

sql 2005, sql 2008, sql 2008 r2   SQL 2005, SQL 2008, SQL 2008 r2

(1 row(s) affected)

However, it is not limited to same number of characters:

DECLARE @string3 VARCHAR(35) = ‘2008 R2′

SELECT @string3, REPLACE(@string3,’20’,’SQL Server 2′)

Result Set:

————–       ————————

2008 R2              SQL Server 208 R2

(1 row(s) affected)

Hope This Helps! Cheers!

Reference : Vishal (http://SqlAndMe.com)

———————-

 

Create User defined function and how to call UDF
CREATE FUNCTION fx_SumTwoValues
( @Val1 int, @Val2 int )
RETURNS int
AS
BEGIN
  RETURN (@Val1+@Val2)
ENDThe structure of the CREATE FUNCTION statement is fairly straightforward. You provide an object name (fx_SumTwoValues), input parameters (@Val1 and @Val2), the type of data the function will return () and the statement(s) the function executes are located between the BEGIN…END block. The following SELECT statement calls the function. Note that the two-part name (owner.object_name) is required when calling this function.
SELECT dbo.fx_SumTwoValues(1,2) AS SumOfTwoValues

SumOfTwoValues
————–
3

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: