Re: FN-FORUM: SQL Server problem

date posted 30th January 2004 14:22

> This didnt work as I do not have bulk insert permissions, I need a way to
> export the contents of a table as insert statements anybody got any ideas

Hi John

it's a bit cheesy, but you could write a little program to flob them all out
to the screen, then copy and paste them from your browser into a text file.

I just cobbled the below together without testing it, but something like
that should work OK - you might have to do a test on the field.type number
to see whether you should add '' around the values, but you might find it
works OK with '' around every value, string or numeric.

strSQL="select * from mytable"
set ors=oconn.execute(strSQL)

strOutput="";
do while not ors.eof
strInserts=""
strValues=""
for each field in ors.fields
strInserts=strLine&field.name&", "
strValues="'"&ors(field.name)&"', "
next
strInserts=left(strInserts,len(strInserts)-2)
strValues=left(strValues,len(strValues)-2)
response.write("INSERT INTO mytable (" & strInserts & ") values (" &
strValue & ")")
ors.movenext
loop


HTH,
Dan