Thursday 23 February 2012

SELECT INTO TABLE – INSERT INTO SELECT Inserting data from from one table to another

 

There are two different methods to insert data from one table to another. Although its really useful but there are few limitation in both of below methods. Don't forget to read list of limitations and restrictions below.

1. SELECT INTO

Below method will create table when data is inserted from one table to another table. Its useful when you need exactly same datatype as source table.

 

Use AdventureWorks2008R2;
Go
---Insert data using SELECT INTO
SELECT AddressLine1, City
INTO BothellAddresses
FROM Person.Address
where City = 'Bothell';
GO
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses
GO

 

2. INSERT INTO SELECT


Below method will need table to be created prior to inserting data. Its really useful when table is already created and you want insert data from another table.



Use AdventureWorks2008R2;
Go
---Create Table
CREATE TABLE BothellAddresses (AddressLine1 NVARCHAR(60), City NVARCHAR(30))
---Insert into above table using SELECT
INSERT INTO BothellAddresses(AddressLine1, City)
SELECT AddressLine1, City
FROM Person.Address
where City = 'Bothell';
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses
GO


    *The following limitations and restrictions apply to the INTO clause:



    1. You cannot specify a table variable or table-valued parameter as the new table.
    2. You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
    3. When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
    4. SELECT...INTO cannot be used with COMPUTE.
    5. The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.
    6. Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you must create them after executing the SELECT...INTO statement.
    7. Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.

    * Source Microsoft


    Wednesday 22 February 2012

    How to get the stored procedure or views containing particular text from original SQL definition statements?


    How to get the stored procedure or views containing particular text from original SQL definition statements?
    Here we using following views.


    sys.syscomments for SQL 2000 Compatibility -- Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. For more information on sys.syscomments please go to http://msdn.microsoft.com/en-us/library/ms186293%28v=sql.100%29.aspx

    Note:Microsoft recommend for any furture development do not use sys.syscomments view instead use sys.sql_modules.


    sys.sql_modules For SQL 2005/SQL 2008.-- Returns a row for each object that is an SQL language-defined module.Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objectscatalog view for more information on sys.sql_modules please go to http://msdn.microsoft.com/en-us/library/ms175081%28v=sql.100%29.aspx


    sys.sysobjects: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. For information on sys.sysobjects please go to http://msdn.microsoft.com/en-us/library/ms177596.aspx

    SQL 2000
    SQL 2005/SQL 2008
    --to get the stored procedures containing Product in code

    USE AdventureWorks;
    GO
    select--top 10 *
          name As Procedurename ,textAS ProcedureCode
    from
          sys.syscomments sc
    inner join
          sys.sysobjects so
                on sc.id = so.id
    where
          textlike'%Product%'
    and so.xtype ='P';
    --to get the stored procedures containing Product in code

    USE AdventureWorks;
    GO
    select--top 10 *
          name As Procedurename , sc.[definition] AS ProcedureCode
    from
          sys.sql_modules sc
    inner join
          sys.sysobjects so
                on sc.object_id= so.id
    where
          sc.[definition] like'%Product%'
    and so.xtype ='P';
    --to get the Views containing Address in code
    USE AdventureWorks;
    GO
    select--top 10 *
          name As Procedurename ,textAS ProcedureCode
    from
          sys.syscomments sc
    inner join
          sys.sysobjects so
                on sc.id = so.id
    where
          text like'%Address%'
    and so.xtype ='V';
    --to get the Views containing Address in code
    USE AdventureWorks;
    GO
    select--top 10 *
          name As Procedurename ,sc.[definition] AS ProcedureCode
    from
          sys.sql_modules sc
    inner join
          sys.sysobjects so
                on sc.object_id= so.id
    where
          sc.[definition] like'%Address%'
    and so.xtype ='V';

    Monday 20 February 2012

    P2V using SCVMM error's at select host screen because of disk space requirement



    Have you ever seen below error while converting P2V using SCVMM?


    There is not enough disk space on the available volumes.
    Maximum disk space on the most appropriate volume, accounting for the host resource utilization  (in megabytes): 60766
    The virtual machine requirement (in megabytes): 162836
    The maximum available space at the time of evaluation, after considering the virtual machine requirements (in megabytes): 139290

    ID: 3501

    I was bit worried after seeing error because currently I am having cluster server with more than 10 gigs of free space and it’s always fine while creating new VM. You don’t need to worry about above error because it simple to convert P2V if you are definitely sure about free space on host. Go to below screen and press next after selecting system drive only.


    After selecting above continue with wizard by pressing next till you reach last second tab \ option in wizard “Conversation option”. Once you are there go back to above screen and select all drives that you want to be in virtualised machine and then press “Conversion Information” in the left side of the wizard and finally create to finish wizard. That’s all you have to do if you get above error while converting Physical to Virtual using SCVMM or Microsoft System Centre Virtual Machine Manager 2012.

    Tuesday 14 February 2012

    Update query with JOIN


    One of my colleague just asked me about update query with JOIN but as he is new to SQL he tried to do exactly same as Select query and it was failing to execute. There is little difference when you do UPDATE query with JOIN. He was trying to execute below query and was not able to understand correct syntax of the query.


    UPDATE Sales.SalesPerson AS sp
            INNER JOIN  Sales.SalesOrderHeader AS so
        ON sp.BusinessEntityID = so.SalesPersonID
           SET SalesYTD = SalesYTD + SubTotal
    Where sp.SalesPersonID > 1


    Below is correct syntax for running UPDATE query with JOIN:
    UPDATE Sales.SalesPerson
            SET SalesYTD = SalesYTD + SubTotal
    FROM Sales.SalesPerson AS sp
    JOIN Sales.SalesOrderHeader AS so
        ON sp.SalesPersonID = so.SalesPersonID
    Where sp.SalesPersonID > 1