Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
Thanks to

Andraax
Aged Yak Warrior

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23133

 

Generate insert SQL for a table - Insert this stored proc:

 

create

proc [dbo].[generate_inserts] @table varchar(50)

--Generate inserts for table @table

AS

declare

@cols varchar(1000)

declare

@col varchar(50)

/*

generate_inserts 'PlanLevel1ControlResponsibility'

*/

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)

GO

 

 

 

 

Posted on Thursday, January 12, 2012 9:17 AM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: Generate insert SQL for a table

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net