Monthly Archives: March 2009

SQL – Generate INSERT statements

Here is a nice piece of stored procedure I found in www.sqlteam.com which can be used to generate INSERT statements for a table which has data. This is very helpful while distributing the application DB which requires some default information.

This script uses curser, so performance might be an issue in case of table with very large amount of data. There are a lot of commercial tools available to generate INSERT scripts. But those who don’t want to install some third party application for this task, this script will be an easy alternate option.


create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)

set @cols=''

declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table

open colcur

fetch next from colcur into @col

while @@fetch_status=0
begin
select @cols = @cols + ‘, ‘ + @col

fetch next from colcur into @col
end

close colcur
deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

–select @cols

declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)

select @sql = ‘select replace(”insert ‘ + @table + ‘ (‘ + @cols + ‘) ‘

select @sql = @sql + ‘values (”’

declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0
begin
if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””

select @sql=@sql + ‘ + coalesce(convert(varchar, ‘ + @colname + ‘), ”null”) + ‘

if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””
select @sql = @sql + ”’, ”’

fetch from ccur into @colname, @coltype
end

close ccur
deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ‘)”, ”””null”””, ”null”) from ‘ + @table

 

exec (@sql)

Advertisements

LinkedIn – Boon in down time

Linkedin people

Well known LinkedIn is a business-oriented social networking site mainly used for professional networking. At this time of recession and job market crunch, LinkedIn is proving to a best place to market once self.
But do we know what are the best ways through which we can get benefited out of LinkedIn to the maximum extent. For this here are the links with good set of ideas and tricks.

http://blog.linkedin.com/2007/07/25/ten-ways-to-use

http://blog.guykawasaki.com/2007/01/ten_ways_to_use.html