October 13, 2015 at 12:55 pm
Folks
There is a valuable script out there that will take the rows from a table and display INSERT STATEMENTS.
Good thing is this script converts the data to HEXADECIMAL ( or some other ) and we don't have to worry about dealing with apostrophies embedded in varchar fields.
Hey, Anyway,
Can some one point me to where the script is.
October 13, 2015 at 2:40 pm
Never heard of it. Be interested in seeing it when you find it.
October 14, 2015 at 1:01 am
SSMS (Tasks : Generate scripts) will generate INSERT statements for data. It doesn't do the HEX thing, but it doubles up embedded single quotes. Would that do instead of a script?
October 14, 2015 at 5:17 am
Yep this works, I used this already.
But yes, that script ( Actually I had someone from this forum send it to me sometime in the past ) does the job beautifully.
You pass the name of the table and it beautifully displays all the insert statements. The beauty is alll those values in every field will be converted to Octal or Hexadecimal .
More later...
October 14, 2015 at 5:22 am
Ahhh....
Found it ....
http://www.sqlservercentral.com/articles/T-SQL/66987/
I looked in my past posts and a person by the name of LOWELL had sent me a reply.
October 14, 2015 at 5:24 am
if exists
(
select *
from sys.objects
where
[object_id] = object_id(N'[dbo].[usp_generate_inserts]')
and type in (N'P', N'PC')
)
drop proc [dbo].[usp_generate_inserts];
go
create proc dbo.usp_generate_inserts
(
@table nvarchar(255)
)
as
begin
set nocount on
declare @is_identity bit;
declare @columns nvarchar(max);
declare @values nvarchar(max);
declare @script nvarchar(max);
if isnull(charindex('.', @table), 0) = 0
begin
print 'Procedure dbo.usp_generate_inserts expects a table_name parameter in the form of schema_name.table_name';
end
else
begin
-- initialize variables as otherwise the padding will fail (return nulls for nvarchar(max) types)
set @is_identity = 0;
set @columns = '';
set @values = '';
set @script = '';
/*
The following select makes an assumption that the identity column should be included in
the insert statements. Such inserts still work when coupled with identity_insert toggle,
which is typically used when there is a need to "plug the holes" in the identity values.
Please note the special handling of the text data type. The type should never be present
in SQL Server 2005 tables because it will not be supported in future versions, but there
are unfortunately plenty of tables with text columns out there, patiently waiting for
someone to upgrade them to varchar(max).
*/
select
@is_identity = @is_identity | columnproperty(object_id(@table), column_name, 'IsIdentity'),
@columns = @columns + ', ' + '['+ column_name + ']',
@values =
@values + ' + '', '' + isnull(master.dbo.fn_varbintohexstr(cast(' +
case data_type
when 'text' then 'cast([' + column_name + '] as varchar(max))'
else '[' + column_name + ']'
end + ' as varbinary(max))), ''null'')'
from
information_schema.columns
where
table_name = substring(@table, charindex('.', @table) + 1, len(@table))
and data_type != 'timestamp'
order by ordinal_position;
set @script =
'select ''insert into ' + @table + ' (' + substring(@columns, 3, len(@columns)) +
') values ('' + ' + substring(@values, 11, len(@values)) + ' + '');'' from ' + @table + ';';
if @is_identity = 1
print ('set identity_insert ' + @table + ' on');
/*
generate insert statements. If the results to text option is set and the query results are
completely fit then the prints are a part of the batch, but if the results to grid is set
then the prints (identity insert related) can be gathered from the messages window.
*/
exec sp_executesql @script;
if @is_identity = 1
print ('set identity_insert ' + @table + ' off');
end
set nocount off
end
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply