Feeds:
Posts
Comments

Archive for December, 2011

I have been using IN clause with multiple values from a long time. But I was always using it with string concatenation along with single quotes to make it string and then process it.
Recently, I found another solution , it works without string concatenation.

Let me explain this issue demonstrating simple example.

CREATE TABLE [dbo].[Student](
      [Student ID]   [varchar] (6) Not NULL ,
      [Student Name] [varchar](50) NOT NULL)
GO

Insert into dbo.[Student] values ('STD001','Bob')
Insert into dbo.[Student] values ('STD002','Alexander')
Insert into dbo.[Student] values ('STD003','Hosanna')
Insert into dbo.[Student] values ('STD004','William')
Insert into dbo.[Student] values ('STD005','Hulda')
Insert into dbo.[Student] values ('STD006','Jacoba')

Old Approach :

Declare  @SQL         VARCHAR(MAX)
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'

SET @WhereClause=REPLACE(@WhereClause,',',''',''')
Set @SQL='Select * 
from dbo.[Student]
Where  [Student Name] In (''' + @WhereClause + ''')'

EXEC (@SQL)

New Approach :

Declare  @Xml AS XML
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'
SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select * 
from dbo.[Student]
Where  [Student Name] In (
SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
)

Explanation :

In the new approach, I need to explain these two important lines of code.

SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select @Xml 

This code snippet will convert string into XML, replacing the delimiter with start and end XML tag.

(Here, It is used as <A></A>).

Output :

SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

Once the string is converted into XML,By using this code snippet, we can easily query it via xquery.
Output :

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Advertisements

Read Full Post »

Sometimes, we need to create insert into statements from a table (SQL Server) data for support ,testing or updating multiple instances etc. Given below stored procedure can generate the data from a table.
In this stored procedure, we will not create a traditional insert into … Values ().Instead of this statement we will use Insert into … select * …. The advantage is we can select the records before insertion to see what we will insert into the table.
(Note: This script can generate “insert into statement” for any table (SQL Server 2005 and above) having 296 or less columns & compatible with CS collation.
Given below are the datatype supported by this stored procedure).

  • tinyint
  • smallint
  • int
  • bigint
  • bit
  • char
  • varchar
  • nchar
  • nvarchar
  • datetime
  • smalldatetime
  • money
  • smallmoney
  • decimal
  • numeric
  • real
  • float
  • binary
  • varbinary
  • uniqueidentifier
  • This procedure will create different “insert into statement” for different scenarios.

    -- For less than 500 records. We will use this statement.(If we use this statement for more than 500 records, It might reduce the performance.)
    Insert into ... 
    Select .... Union ALL
    Select .... Union ALL
    

    OR

    -- For more than 500 records seperate queries for seperate select statements.
    Insert into ...
    Select ....
    

    Other features of this scripts are :

    • You can use a where clause to generate only filtered data.
    • You can provide a top Number to filter a top number of records.

    Let me explain this issue demonstrating simple example.

    CREATE TABLE [dbo].[Student](
          [Sno] [int] NOT NULL,
          [Student ID] nvarchar(6) Not NULL ,
          [Student name] [varchar](50) NOT NULL,
          [Date of Birth]  datetime not null,
          [Weight] [int] NULL)
    GO
    
    Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',70)
    Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',80)
    Insert into dbo.[Student] values (3,'STD003','Hosanna','2005-10-05',82)
    Insert into dbo.[Student] values (4,'STD004','William','2006-09-03',55)
    Insert into dbo.[Student] values (5,'STD005','Hulda','2007-08-01',35)
    Insert into dbo.[Student] values (6,'STD006','Jacoba','2008-07-18',30)
    
    

    Now, you need to create this procedure to generate “insert into statement” from table data.

    CREATE PROCEDURE SAMPLE_DATA_PROC
    @SchemaName VARCHAR(MAX),
    @TableName VARCHAR(Max),
    @WhereClause NVARCHAR(Max),
    @TopNo INT
    AS
    
    DECLARE @SQL VARCHAR(max)
    DECLARE @nSQL NVARCHAR(Max)
    DECLARE @RecordCount INT 
    DECLARE @FirstColumn NVARCHAR(Max)
    
    --SET @TableName=QUOTENAME(@TableName) -- Quote the table name
    SET @TableName =@SchemaName + '.' + QUOTENAME(@TableName)
    Print @TableName
    
    /*We need to find the record count in order to remove Union ALL from the last row*/
    SET @nSQL=''
    SET @nSQL= N'SELECT @RecordCount=COUNT(*) FROM ' + @TableName + (CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' Where ' + @Whereclause ELSE '' END)
    Print @nSQL
    EXEC sp_executesql @query = @nSQL, @params = N'@RecordCount INT OUTPUT', @RecordCount = @RecordCount OUTPUT 
    /*****************************************************************/
    
    /*Need to check either top No of record is less than record count 
    in order to remove union all from the last row */
    IF @TopNo IS NOT NULL And @TopNo<@RecordCount
    	BEGIN
    		SET @RecordCount=@TopNo
    	END
    /***************************************************************/
    /*** Here we need to find the first column name to generate a serial number and insert an "Insert into statement" in the first row ***/
    SET @nSQL=''
    SET @nSQL= N'SELECT @FirstColumn=[name] FROM sys.columns WHERE [Column_id]=1 And object_ID=object_ID('''+ @TableName + ''')' 
    Print @nSQL
    EXEC sp_executesql @query = @nSQL, @params = N'@FirstColumn nvarchar(Max) OUTPUT', @FirstColumn = @FirstColumn OUTPUT 
    /**************************************************************/
    
    DECLARE @FieldName VARCHAR(max) 
    SET @FieldName=''
    
    If (Select Count(*) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') And is_identity<>0)=1
    
    BEGIN
    	SET @FieldName = STUFF(
    	(
    	SELECT ',' + QUOTENAME([Name]) FROM sys.columns WHERE object_id=object_id('' + @TableName + '') Order By [column_id] 
    	FOR XML PATH('')), 1, 1, '')
    	Set @FieldName ='(' + @FieldName + ')'
    	Print @FieldName
        Print len(@FieldName)
    END
    
    /*******Create list of comma seperated columns *******/
    SET @SQL= (SELECT STUFF((SELECT(CASE
    WHEN system_type_id In (167,175,189) THEN + ' Cast(ISNULL(LTRIM(RTRIM(''N''''''+Replace(' + QUOTENAME([Name])+ ','''''''','''''''''''')+'''''''''+ ')),''NULL'') as varchar(max)) + '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
    WHEN system_type_id In (231,239) THEN + ' Cast(ISNULL(LTRIM(RTRIM(''N''''''+Replace(' + QUOTENAME([Name])+ ','''''''','''''''''''')+'''''''''+ ')),''NULL'') as nvarchar(max)) + '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
    WHEN system_type_id In (58,61,36) THEN + ' ISNULL(LTRIM(RTRIM(''N'''''' + Cast(' + QUOTENAME([Name])+ ' as varchar(max))+''''''''' + ')),''NULL'') + '' AS ' + QUOTENAME([Name]) + '''+ '' ,'''+' + '
    WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN + ' ISNULL(Cast(' + QUOTENAME([Name])+ ' as varchar(max)),''NULL'')+ '' AS ' + QUOTENAME([Name]) + ''' + '' ,'''+'+ '
    END
    )
    FROM
    sys.columns WHERE object_ID=object_ID(''+ @TableName + '') FOR XML PATH('')),1,1,' '))
    
    /*******************************************************/
    
    /* Here 500 means if the record count is 500 or top no 500 then it will generate "Insert into select ..Union All "
    Because more than 500 might reduce its performance. */
    IF @TopNo <500 or @RecordCount<500 
    	BEGIN
    		IF @TopNo IS NULL
    			BEGIN
                	SET @SQl='SELECT (Case When ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' ''' + ' ELSE '''' END) + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') <>' + CONVERT(VARCHAR(10),@RecordCount) + ' THEN '' UNION ALL'' ELSE '''' END) AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
                END
    	ELSE
    			BEGIN
                    SET @SQl= 'SELECT TOP ' + CONVERT(VARCHAR(10),@TopNo) + ' (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') =1 THEN '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + '''' + ' ELSE '''' END) + ''SELECT ''+' + LEFT(@SQL,LEN(@SQL)-8) + ' + (CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@FirstColumn) + ') <>' + CONVERT(VARCHAR(10),@RecordCount) + ' THEN '' Union All'' ELSE '''' END) AS [DATA]' + ' FROM ' + @TableName + (CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
    			END
    	END
    ELSE
    --- Greator then 500 will generate "insert into select *" ... for each record.
    	BEGIN
    		IF @TopNo IS NULL
    			BEGIN
                	  SET @SQl='SELECT '' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' '' + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
                END
    	ELSE
    			BEGIN
                      SET @SQl='SELECT TOP ' + CONVERT(VARCHAR(10),@TopNo) + ''' INSERT INTO ' + @TableName + ' ' + ' '+ @FieldName + ' '' + ''SELECT ''+' + Left(@SQL,Len(@SQL)-8) + ' AS [DATA] ' + ' FROM ' + @TableName +(CASE WHEN ISNULL(@Whereclause,'') <>'' THEN ' WHERE ' + @Whereclause ELSE '' END)
    			END
    	END
    EXEC (@SQL)
    GO
    

    How to use this stored procedure

    
    --Example 1 : When you need to generate "insert into statement" from all records within a table.
      SAMPLE_DATA_PROC 'dbo','Student',NULL,NULL
      GO
    --Example 2 : When you need to generate "insert into statement" from top 5 records within a table.
      SAMPLE_DATA_PROC 'dbo','Student',NULL,3
      GO
    --Example 3 : When you need to generate "insert into statement" with a specific filter (Single filter) within a table.
      SAMPLE_DATA_PROC 'dbo','Student','[SNO]=1',NULL
      GO
    --Example 4 : When you need to generate "insert into statement" with a specific filter (Multiple filters) within a table.
      SAMPLE_DATA_PROC 'dbo','Student','[SNO]=1  And [Student Name]=''Bob''',NULL
      GO
    
    

    If you execute example 1 : you will get all the date in the specified table like this

    Now, you can comment “INSERT INTO [Student]” to view the data and uncomment it to insert data into the table.

    -- INSERT INTO [Student]   
    SELECT 1 AS [Sno] ,N'STD001' AS [Student ID] ,N'Bob' AS [Student name] ,N'Dec 31 2003 12:00AM' AS [Date of Birth] ,70 AS [Weight] UNION ALL
    SELECT 2 AS [Sno] ,N'STD002' AS [Student ID] ,N'Alexander' AS [Student name] ,N'Nov 15 2004 12:00AM' AS [Date of Birth] ,80 AS [Weight] UNION ALL
    SELECT 3 AS [Sno] ,N'STD003' AS [Student ID] ,N'Hosanna' AS [Student name] ,N'Oct  5 2005 12:00AM' AS [Date of Birth] ,82 AS [Weight] UNION ALL
    SELECT 4 AS [Sno] ,N'STD004' AS [Student ID] ,N'William' AS [Student name] ,N'Sep  3 2006 12:00AM' AS [Date of Birth] ,55 AS [Weight] UNION ALL
    SELECT 5 AS [Sno] ,N'STD005' AS [Student ID] ,N'Hulda' AS [Student name] ,N'Aug  1 2007 12:00AM' AS [Date of Birth] ,35 AS [Weight] UNION ALL
    SELECT 6 AS [Sno] ,N'STD006' AS [Student ID] ,N'Jacoba' AS [Student name] ,N'Jul 18 2008 12:00AM' AS [Date of Birth] ,30 AS [Weight]
    

    If you have an identity column in the table, you need to add these two lines.

    SET IDENTITY_INSERT Student ON  --(Before insert into statement)
    GO
    SET IDENTITY_INSERT Student OFF --(In the end of the statement)
    GO
    

    I’d really appreciate your comments on my posts, whether you agree or not, do comment.

    Read Full Post »