Insert Script for MS SQL Server

Browsing the net i found this great script it creates a procedure with takes table name as input and returns a insert script for table works for both SQL Server 2000 and 2005. Great to use and no additional server requirement or installs required.

/***** Object: StoredProcedure [dbo].[sp_CreateDataLoadScript] Script
******/

SET ANSI_NULLS ONGOSETQUOTED_IDENTIFIER ON

GO

Create Procedure[dbo].[sp_CreateDataLoadScript]@TblName
varchar(128)

as

/*execsp_CreateDataLoadScript 'MyTable'*/

create table #a (id intidentity (1,1), ColType int, ColName varchar(128))

insert #a
(ColType,ColName) select case when DATA_TYPE like '%char%' then 1 else 0 end
,COLUMN_NAME from information_schema.columns where TABLE_NAME =@TblNameorder by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,@maxid int,@cmd1 varchar(7000) ,@cmd2 varchar(7000)

select @id = 0 ,@maxid =max(id)from
#a

select @cmd1 = 'select '' insert ' + @TblName + ' ('select @cmd2 = ' + ''
select '' + 'while @id < @maxid begin select@id = min(id) from #a where id > @idselect

@cmd1 = @cmd1 + ColName +',' from #a where id = @idselect

@cmd2 = @cmd2+ ' casewhen ' + ColName + ' is null '+ ' then ''null'' '+ ' else '+
case when ColType = 1 then ''''''''' + ' + ColName + ' + '''''''''
else 'convert(varchar(20),' + ColName + ')' end+ ' end + '','' + 'from #a
where id = @idendselect

@cmd1 = left(@cmd1,len(@cmd1)-1) +' ) '' '

select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblNameselect '

/*' + @cmd1 + @cmd2 +'*/'

exec (@cmd1 + @cmd2)

droptable #a

Note: you will have to turn of identity column if you want to insert the primary key as well.

Back button with Ajax in ASP.Net

I am sure anyone who is worked on AJAX grids has come accross a QA or client who wants the grid pager to go to previous page when they hit browser back button. well asp.net 3.5 Sp1 has the perfect answer, it has a history control which allows you to add history on your ajax enabled pages. it is defined in five simple steps on the blog link mentioned below.

http://geekswithblogs.net/ranganh/archive/2008/11/17/enabling-the-browserrsquos-back-button-for-grid-view-asp.net-ajax.aspx

basic logic here is that you are able to create history points to you browser on AJAX postback.

SQL Server - Tips and more

I have been a great fan of MS SQL Server, I just wanted to share some of the tips and tricks i picked up that can be quite helpful.

1. Changing owner of tables/ procedure or Function.

For Tables

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SELECT
@old =
'oldOwner_CHANGE_THIS'
, @new = 'dbo'
, @sql = '
IF EXISTS (SELECT
NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND
TABLE_SCHEMA = ''' + @old + '''
)
EXECUTE sp_changeobjectowner ''?'',
''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

For Procedures

DECLARE @oldOwner sysname, @newOwner sysname

SELECT
@oldOwner =
'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'

select 'EXECUTE
sp_changeobjectowner
'''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE =
'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)),
'IsMSShipped') = 0

For Functions

DECLARE @oldOwner sysname, @newOwner sysname

SELECT
@oldOwner =
'oldOwner_CHANGE_THIS'
, @newOwner = 'dbo'

select 'EXECUTE
sp_changeobjectowner
'''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE =
'Function'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)),
'IsMSShipped') = 0

2. Insert Script for SQL Server

Browsing the net i found this great script it creates a procedure with takes table name as input and returns a insert script for table.

****** Object: StoredProcedure [dbo].[sp_CreateDataLoadScript] Script Date:
09/19/2008 20:53:38 ******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO

Create Procedure
[dbo].[sp_CreateDataLoadScript]
@TblName varchar(128)
as
/*
exec
sp_CreateDataLoadScript 'MyTable'
*/


create table #a (id int
identity (1,1), ColType int, ColName varchar(128))

insert #a (ColType,
ColName)
select case when DATA_TYPE like '%char%' then 1 else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME =
@TblName
order by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,
@maxid int ,
@cmd1
varchar(7000) ,
@cmd2 varchar(7000)

select @id = 0 ,
@maxid =
max(id)
from #a

select @cmd1 = 'select '' insert ' + @TblName + ' (
'
select @cmd2 = ' + '' select '' + '
while @id < @maxid begin select
@id = min(id) from #a where id > @id

select @cmd1 = @cmd1 + ColName +
','
from #a
where id = @id

select @cmd2 = @cmd2
+ ' case
when ' + ColName + ' is null '
+ ' then ''null'' '
+ ' else '
+ case
when ColType = 1 then ''''''''' + ' + ColName + ' + ''''''''' else
'convert(varchar(20),' + ColName + ')' end
+ ' end + '','' + '
from #a
where id = @id
end


select @cmd1 = left(@cmd1,len(@cmd1)-1) +
' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName

select '/*' + @cmd1 + @cmd2 + '*/'

exec (@cmd1 + @cmd2)
drop
table #a

Note: you will have to turn of identity column if you want to insert the primary key as well.