SQL Select Into & Insert Into Select

Here are two useful T-SQL query syntax for inserting select statements into tables:


SELECT INTO
SELECT table1.column1, table1.column2, table1.column3
INTO new_table
FROM table1


INSERT INTO SELECT
INSERT INTO table1
SELECT table2.column1, table2.column2, table2.column3
FROM table2


Both of these syntax inserts query results into a table, the only difference is that we use SELECT INTO to insert values from an existing table to a new one (a new table that is not yet present in the database).


And we use INSERT INTO SELECT to insert values from an existing table to another existing one. Of course be reminded that the number of select columns from the source table should match the destination table's columns. Also note that if you use SELECT INTO to copy values from an existing table to another one, you'll get an error like this: Msg 2714, Level 16, State 6 - There is already an object named 'table_name' in the database. So you really should use INSERT INTO SELECT in those cases.

Creating Scalar-valued Functions in SQL Server Management Studio

We use functions in T-SQL to return a value based on passed parameters just like we do on any other language. But in SQL one use of functions is to provide in-line data processing in our queries like you will see in our example today.

Begin by expanding the database in your SQL Management Studio, then Programmability, Functions, and right-clicking on Scalar-valued Functions and then select "New Scalar-valued Function...".

Notice Table-valued functions? We won't cover it for now as those kind of functions return a whole table like Stored Procedures. Maybe we'll do that in another lesson. But right now let's focus on Scalar-valued functions which will return a single value.

From the template, you may delete the comments if you want.

Our sample function will be returning a string value (varchar in SQL) of the month depending on the passed integer, which can range from 1-12. Let's call our function GetMonthName.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[GetMonthName]
(
    -- Add the parameters for the function here
    @month int
)

RETURNS varchar(10)
AS
BEGIN
    DECLARE @month_name varchar(10)

    IF @month = 1
    BEGIN
        SET @month_name = 'January';
    END
    ELSE IF @month = 2
    BEGIN
        SET @month_name = 'February';
    END
    ELSE IF @month = 3
    BEGIN
        SET @month_name = 'March';
    END
    ELSE IF @month = 4
    BEGIN
        SET @month_name = 'April';
    END
    ELSE IF @month = 5
    BEGIN
        SET @month_name = 'May';
    END
    ELSE IF @month = 6
    BEGIN
        SET @month_name = 'June';
    END
    ELSE IF @month = 7
    BEGIN
        SET @month_name = 'July';
    END
    ELSE IF @month = 8
    BEGIN
        SET @month_name = 'August';
    END
    ELSE IF @month = 9
    BEGIN
        SET @month_name = 'September';
    END
    ELSE IF @month = 10
    BEGIN
        SET @month_name = 'October';
    END
    ELSE IF @month = 11
    BEGIN
        SET @month_name = 'November';
    END
    ELSE IF @month = 12
    BEGIN
        SET @month_name = 'December';
    END
    ELSE
    BEGIN
        SET @month_name = 'Invalid month number!';
    END


    RETURN @month_name;
END

So to explain, we use the CREATE FUNCTION keyword to create our function followed by our desired name then the parameters (name and type) enclosed in parentheses. Then the RETURNS keyword followed by its type which in our case is a varchar. It's just to say that our function must return a varchar. After that follows our logic which is just a basic if..else.. that will return the corresponding month name or invalid if it is not between 1 to 12. Then of course return that string for our varchar return value.

Here's sample output from our scalar-valued function:

In our 4th example we use the built-in SQL function getdate() to get the current system date then enclose it with another built-in function month() which accepts datetime as parameter and returns the month in integer form, which our function accepts as parameter and finally returns the name of the month.

'Til our next lesson... I hoped you learned/remembered something today.

-k