creating the script to add a record to a table

You can generated INSERT script with the help of some custom query.
Check this link - http://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript.html

Raj

This is the script that I have. Not sure whether it is the same as in the link above.

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)

Select allOpen in new window

Well, that is a partial solution.  

Checked out http://www.nigelrivett.net/SQLTsql/sp_CreateDataLoadScript.html

this does not handle text or datetime properly.  For text it does not surround it with quotes ' .  It also does not put quotes around datetimes.

The one you use does not delimit nVarChar and text

other then that it was what I was looking for

By modifying my query, you can fix it.
Let me know, if you don't know how to do.
I'll try tomorrow

Good night

Peter - I made some corrections. Now the Stored Procedure supports 'nvarchar', 'text', 'nchar', 'ntext', 'smalldatetime' datatypes as well.

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', 'nvarchar', 'text', 'nchar', 'ntext', 'smalldatetime')
select @sql=@sql + ''''''


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

if @coltype in ('varchar', 'char', 'datetime', 'nvarchar', 'text', 'nchar', 'ntext', 'smalldatetime')
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)

Select allOpen in new window

When I ran this on my table I got this error.

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ','.
Msg 174, Level 15, State 1, Line 1
The replace function requires 3 argument(s).

the problem is the insert string is larger then 4000 characters.
I changed it to the maximum of 8000 for a varchar but it will still not long enough.

Any ideas?

(btw, other then this small issue it seems to be bang on what I was looking for.)

That worked.  Thanks.