Generate SQL Insert Statements with Indentation


Howdy Everyone.

Generating SQL insert statements for a given table is always a tedious task in SQL Server, Mostly the automated generated scripts are not indented with spaces, so that it will be easier to read and most of all it will be easy to change a column values or remove column values.

The below code will give you the insert statements along with the indentation of the code.

 

<pre>IF EXISTS ( SELECT 1 FROM sys.procedures WHERE name = 'InsertGenerator')
    DROP PROCEDURE InsertGenerator

GO

CREATE PROCEDURE InsertGenerator
(
    @TableName  VARCHAR(100)
)
AS
BEGIN
    DECLARE @IndentationTable   TABLE
    (
        ColumnName      VARCHAR(500)
        ,MaxLength      BIGINT
    )

    DECLARE @SQL        NVARCHAR(MAX)
    DECLARE @Insert     NVARCHAR(MAX)
    DECLARE @SQ         VARCHAR(4)
    DECLARE @SSQ        VARCHAR(4)
    DECLARE @ColumnName VARCHAR(300)
    DECLARE @DataType   VARCHAR(50)
    DECLARE @MaxLength  BIGINT

    SET @SQ     = ''''
    SET @SSQ    = ''''''''''

    BEGIN TRY
        
        DECLARE maxLength CURSOR
        FOR
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 

        DECLARE Cols CURSOR
        FOR
            SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK) WHERE TABLE_NAME=@TableName 


        OPEN maxLength
        FETCH NEXT FROM maxLength INTO @ColumnName
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            SET @SQL = 'SELECT ' + @SQ +  @ColumnName + @SQ + ' ColumnName, MAX(LEN('+@ColumnName+')) MaxLength FROM '  + @TableName  + ' '
            INSERT INTO @IndentationTable (ColumnName,MaxLength)
            EXEC sp_executeSQL @SQL
            FETCH NEXT FROM maxLength INTO @ColumnName
        END
        CLOSE maxLength
        DEALLOCATE maxLength

        UPDATE @IndentationTable         
        SET MaxLength = MaxLength + 8

        SELECT @Insert =  'SELECT ' + @SQ + 'INSERT INTO ' + @TableName + '(' + (SELECT STUFF( (SELECT ',' + ColumnName FROM @IndentationTable FOR XML PATH ('')),1,1,'')) + ' ) VALUES ( ' + @SQ + '+'
        SET @SQL = @Insert
        OPEN Cols
        FETCH NEXT FROM Cols INTO @ColumnName,@DataType
        WHILE (@@FETCH_STATUS = 0)
        BEGIN


            SELECT  @MaxLength = MaxLength 
            FROM    @IndentationTable
            WHERE   ColumnName = @ColumnName
            
            
                        
            SELECT @SQL = @SQL + CASE  
                                    WHEN @DataType IN ('BIGINT','BIT','DECIMAL','INT','MONEY','NUMERIC','SMALLINT','SMALLMONEY','TINYINT','FLOAT','REAL')   THEN   ' CAST( ' + @ColumnName + ' AS VARCHAR) '
                                    WHEN @DataType IN ('DATE','DATETIME2','DATETIME','DATETIMEOFFSET','SMALLDATETIME','TIME') THEN @SQ + 'CAST( ' + @SQ + '+' + @SSQ + '+' + 'CAST( ' + @ColumnName + ' AS VARCHAR ) ' + '+' + @SSQ + '+' + @SQ + ' AS ' + @DataType + ' ) ' + @SQ
                                    WHEN @DataType IN ('CHAR','VARCHAR','TEXT') THEN @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    WHEN @DataType IN ('NCHAR','NVARCHAR','NTEXT') THEN 'N' + @SSQ + '+' + @ColumnName + '+' + @SSQ
                                    ELSE   CAST(@ColumnName AS NVARCHAR)
                                  END  +  '+' + 'REPLICATE( ' + @SQ+ ' ' +@SQ + ','+CAST(ISNULL(@MaxLength,20) AS VARCHAR) + '-LEN(' + @ColumnName + '))' +     '+'  + @SQ +',' + @SQ + '+'

            FETCH NEXT FROM Cols INTO @ColumnName,@DataType    
        END
        CLOSE Cols
        DEALLOCATE Cols

        SET @SQL = LEFT(@SQL,LEN(@SQL)-LEN('+'  + @SQ +',' + @SQ +  '+'))
        SELECT @SQL = @SQL + '+' + @SQ + ')' + @SQ +  ' FROM ' +  @TableName
        select @SQL 
        EXEC sp_executeSQL @SQL

    END TRY
    BEGIN CATCH
        SELECT CAST(ERROR_LINE() AS VARCHAR) + '    ' + ERROR_MESSAGE() + '   AT ' + CAST(ERROR_LINE() AS varchar)
    END CATCH
END

GO

To execute this code you have to give the stored proc name followed by the table name.

Example:
EXEC InsertGenerator <table_name>

Hope this helps you guys in generating a better and readable code.

TSQL — Find position of a repeated character in the string


Howdy Everyone,

Today I got hit by a simple problem by my friend.. He wanted to find out the position of  a character within in a string when it came for the 4th time within that string… For example if the string is “abc;a123;afgh;ayui” and he wants to know the position of 4th a in the string above.

I suggested him to write a UDF like below which will do the trick.

The while loop can also be replaced with a CTE, but I put down the WHILE loop here for historical reasons.

IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'charNumIndex' and xtype = 'FN')
	drop function dbo.charNumIndex
GO
CREATE function dbo.charNumIndex
(
	@string			varchar(4000)
	,@lookupChar	varchar(5)
	,@repeatedTime	INT
)
RETURNS INT
BEGIN
	declare @charPosition	INT
	DECLARE @loop			INT
	DECLARE @rtPosition		INT
	set @charPosition = -1
	SET @loop = 1
	WHILE (CHARINDEX(@lookupChar,@string,(@charPosition+1)) <> 0) AND (@loop <= @repeatedTime)
	BEGIN
		SET @charPosition = CHARINDEX(@lookupChar,@string,(@charPosition+1))
		SET @loop = @loop + 1
	END
	IF (@loop > 2)
		SET @rtPosition =  @charPosition
	ELSE
		SET @rtPosition =  0
	RETURN @rtPosition
END

EXAMPLE:
select left(‘http://abcd/abc/abcd&#8217;,dbo.charNumIndex(‘http://abcd/abc/abcd&#8217;,’/’,4))

TSQL — Backup all your databases or selected databases


Howdy Everyone,

Today one of my friend asked me to take a look into his backup script that he is using, to take backup of  all databases in his testing environment.The script is hard coded with the database names (50 of them) and the poor fellow needs to maintain the script as well  add  / delete databases on the request of his team.

I advised my friend to change the script and use the dynamic SQL for this purpose and re-wrote the script for him.

I thought it might be helpful to some one like my dear friend. So I pasted the same in here as my new blog post.

How it Works?

There are few choices I have given to the users for this script

  1. The user can restrict the databases that are needed to be taken a backup
  2. The user should give the backup location in which the backup files needs to be placed
  3. The user can choose the backup file trailer incase if he wishes to add something there & if it is left blank current date in the format YYYYMMDD will be added to the end of the databasename.
What if my friend X above want to take a backup of only certain databases like 10 out of his 50 right now… I thought of that a wrote a lookup table for this (databases_To_be_backedUp).  Insert the database names (10 in my case) that needs to be backed up into the lookup table, so that it will take care of the backup part for you. (Follow Example 4)
All the backup files will be created with the extension of “BAK”
IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'databases_To_be_backedUp' and xtype = 'U')
	DROP TABLE databases_To_be_backedUp
CREATE TABLE databases_To_be_backedUp
(
	ID				INT		Identity(1,1)
	,database_name	varchar(200)
)
GO

IF EXISTS ( SELECT 1 FROM SYSOBJECTS where name = 'backup_all_databases' and xtype = 'P')
	DROP PROCEDURE backup_all_databases
CREATE procedure backup_all_databases
(
	@backupName_trailer	varchar(20) = '' --- If the parameter is passed the backup names will have the trailer with this
										  -- else by default the proc will add the YYYYMMDD trailer.
	,@backupLocation	varchar(100)
)
AS
BEGIN
	declare @databaseName	Varchar(200)
			,@lookUpCount	int
			,@sql			varchar(1000)

	SELECT @lookUpCount = count(1)
	FROM databases_To_be_backedUp

	IF (@lookUpCount > 0) -- Some databases are inserted into the databases_To_be_backedUp, that means only these databases are needed to be backed up.
	BEGIN
		DECLARE database_cursor CURSOR
		FOR SELECT database_name FROM databases_To_be_backedUp ORDER BY ID
	END
	ELSE
	BEGIN
						  -- The databases_To_be_backedUp is empty so all the databases are needed to backed up.. apart from the system databases
						  -- In this script we are considering the MASTER, MODEL,TEMPDB, MSDB as system databases..
						  -- Incase if you want to change this change the not in parameter for the below select statement.
		DECLARE database_cursor CURSOR
		FOR select name from sys.databases where name not in('master','model','msdb', 'tempdb')
	END
	OPEN database_cursor
	FETCH database_cursor into @databaseName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		Select @sql = 'BACKUP DATABASE ' + @databaseName + ' TO DISK = ' + '''' + @backupLocation + @databaseName
										+ CASE @backupName_trailer
												WHEN '' THEN convert(varchar(10),getdate(),112)
												ELSE @backupName_trailer
										  END
										+ '.bak'			-- extension  for the backup file
										+ ''''
										+ ' WITH INIT,SKIP'
		print @sql
		exec(@sql)
		FETCH database_cursor into @databaseName
	END
	CLOSE database_cursor
	DEALLOCATE database_cursor
END
GO

-- examples
-- 1 backup all your databases to d:\
exec backup_all_databases @backupLocation = 'D:\'

-- 2 backup with backup as a trailer in the file name
exec backup_all_databases @backupName_trailer = 'backup',@backupLocation = 'D:\'

-- 3 if you want to take backup of only choosen databases rather than every thing
create database a -- creates a temp database a
create database b -- creates a temp database b

insert into databases_To_be_backedUp(database_name) values ('a');
insert into databases_To_be_backedUp(database_name) values ('b');

exec backup_all_databases @backupLocation = 'D:\'

Find MAX number without using MAX,Where Clause, Order by


How can some one retrieve the max of the number from a table using TSQL where you are not allowed to use the MAX function or the order by clause or the Where clause.

I have found out a solution below, although the algorithm can be changed by using different sort techniques. I found this is simple to understand so the users might build upon this

Method 1

create table salaries (empid int identity(1,1), salary int)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (3000)
go
insert into salaries (salary) values (2400)
go
insert into salaries (salary) values (100)
go
insert into salaries (salary) values (200)
go
insert into salaries (salary) values (1050)
go
insert into salaries (salary) values (8000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go

select top 1 s1.salary from salaries s1
      inner join salaries s2 on s1.salary> s2.salary
      and s1.empid not in (select s2.empid from salaries s1
      inner join salaries s2 on s1.salary> s2.salary)

Method 2

create table #table
(
	num int
)

insert into #table values (1)
insert into #table values (2)
insert into #table values (3)
insert into #table values (4)
insert into #table values (10)
insert into #table values (6)
insert into #table values (7)
insert into #table values (8)
insert into #table values (9)

declare @max_number int
declare @present_num int
declare @previous_num int

SET @present_num = 0
SET @previous_num = 0
SET @max_number = 0
update t
SET	@present_num = t.num
    ,@max_number = case when @present_num > @max_number Then @present_num  else @max_number end
    ,@previous_num = t.num
FROM #table t

select @present_num ,@max_number,@previous_num

UDF to Split a delimited string and return it as a table


Howdy Every One

Each and everyday we might find out that we need a split a string and return it as a table with rows in SQL. There are many programming languages in which there are built in functions which will do this for you (javascript split). Now if you want the same functionality in SQL, the only choice left is to write your own UDF (as of SQL Server 2008 R2 version).

Below is code of the UDF that might help you out

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))

 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END

TSQL — Delete duplicates in your table


Howdy Everyone,
It will be quite problematic to delete the duplicates in your table, if they are inserted because of an improper insert or some other means (may be bcp was run twice ….. )
In this case the below stored procedure can work, People who want to create can simply create the function out of it , I assume or else just let me know, I will post that too….

IF EXISTS ( SELECT 1 FROM SYSOBJECTS WHERE name = 'deleteDuplicates' and xtype = 'P' )
	DROP PROCEDURE deleteDuplicates
GO

CREATE PROCEDURE dbo.deleteDuplicates
(
	@tableName	sysname
   ,@check		int = 0	--- 0 just to see what are duplicates, 1 delete the duplicates.
)
AS
BEGIN
	DECLARE @columnsDelimited	NVARCHAR(max)
			,@dySql				NVARCHAR(max)
			,@tran				int
			,@tempTable			sysname
	SET @tran = 0
	SET @columnsDelimited = ''
	SET @tempTable = 'delteDuplicates' + CAST (@@SPID AS VARCHAR)
	BEGIN TRY

		IF NOT EXISTS ( SELECT	1
						FROM	sys.tables
						WHERE	name	= @tableName
					  )
		BEGIN
			PRINT @tableName + ' not exists in the current database'
			RETURN -1
		END
		SELECT @columnsDelimited = @columnsDelimited
									+ CASE LEN(@columnsDelimited)
										WHEN 0 THEN ''
											ELSE ','
										END
									+ QUOTENAME(c.name)
		FROM sys.columns C
		JOIN sys.tables  T
		ON	 C.object_id	= T.object_id
		AND  c.is_identity  = 0
		AND  T.name			= @tableName

		IF OBJECT_ID(@tempTable) IS NOT NULL
		BEGIN
			SET @dySql = 'DROP TABLE ' + @tempTable
			exec (@dySql)
		END

		SET @dySql	= 'SELECT ROW_NUMBER() OVER(PARTITION BY ' + @columnsDelimited  + ' ORDER BY ' +@columnsDelimited + ' ) as row_number_delete, * into '  + @tempTable
						+ ' FROM ' + @tableName
		exec sp_executeSQl @dySql
		SELECT @dySql

		IF (@check = 0)
		BEGIN
			SET @dySql = 'SELECT * FROM ' + @tempTable + ' WHERE row_number_delete > 1 '
			EXEC (@dySql)
		END

		IF (@check = 1)
		BEGIN
			SET @dySql = 'DELETE ' + @tempTable + ' WHERE row_number_delete > 1'
			EXEC (@dySql)

			BEGIN TRAN
				SET @tran = 1
				SET @dySql	= 'TRUNCATE TABLE ' +@tableName
				EXEC ( @dySql)

				SET @dySql	= 'INSERT INTO ' + @tableName + ' (' +@columnsDelimited + ') ' + 'SELECT ' + @columnsDelimited + ' FROM ' + @tempTable
				EXEC ( @dySql)

			COMMIT TRAN
		END

	END TRY
	BEGIN CATCH
		IF (@tran = 1)
			ROLLBACK;
		SELECT 'errored_line '+ CAST (ERROR_LINE() AS VARCHAR) + ' error_msg ' + ERROR_MESSAGE() + ' error_number ' + ERROR_NUMBER()
	END CATCH;
END

GO

--- TEST DATA
drop table abcd
create table abcd
(
	a int
	,b int
	,c int
)

insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (1,2,3)
insert into abcd values (2,2,3)
insert into abcd values (2,2,3)
insert into abcd values (2,2,3)
insert into abcd values (3,0,0)

exec deleteDuplicates 'abcd',1