Create proc spgeninsertsql @ tablename as varchar (100) as -- declare @ tablename varchar (100) -- set @ tablename = 'Orders '-- set @ tablename = 'education' declare xcursor cursor for select name, xusertype from syscolumns where (ID = object_id (@ tablename )) declare @ F1 varchar (100) Declare @ F2 integer declare @ SQL varchar (8000) set @ SQL = 'select' insert into '+ @ tablename + 'values (''' open xcursor fetch xcursor into @ F1, @ F2 while @ fetch_status = 0 begin set @ SQL = @ SQL ++ case when @ F2 in (99,167,175,231,239, 61) then' + case when' + @ F1 + 'is null then''' else ''' end + 'else' + 'end + 'replace (isnull (cast ('+ @ F1 +' As varchar (8000 )), ''null''), ''' + case when @ F2 in, 99,167,175,231,239, 61) then' + case when' + @ F1 + 'is null then''' else ''' end + 'else' + 'end + char (13) + ''', ''' fetch next from xcursor into @ F1, @ F2 end close xcursor deallocate xcursor set @ SQL = left (@ SQL, Len (@ SQL)-5) + '+ '') ''from' + @ tablename exec (@ SQL) Go
The above code is run in the query analyzer.
Run again
Exec spgeninsertsql tablename -- Name of the table to be converted