October 8, 2013 at 6:45 am
I am still using sp_generate_inserts :
(Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose:To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.
Written by:Narayana Vyas Kondreddi
http://vyaskn.tripod.com[/code%5D
This code has not changed for a number of years, it is still functioning, but is there a alternative for this within the SQL-server product ?
I am aware of the 'database task generate scripts'.
Thanks for your time and attention,
Ben
October 8, 2013 at 6:51 am
Sorry additional,
I did write:
I am aware of the 'database task generate scripts'.
But I do not know how to do this with a scripted command.
(As with sp_generate_inserts, example:
sp_generate_inserts My_Table
)
sorry was not clear on this,
Ben
October 8, 2013 at 7:19 am
I am not really sure what you are trying to do or what the question is.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 8, 2013 at 9:26 am
Don't know anything about sp_GenerateInserts but are you just trying to script the data in a table as Insert Statements?
October 14, 2013 at 8:33 am
Sean Lange (10/8/2013)
I am not really sure what you are trying to do or what the question is.
Sp_generate_Inserts, generates insert statements from an existing table. For example to create (part) of a script to transport the data to another database.
I have been using and am still using this stored procedure for all kind of things. But because the script is not changed (not maintained ?) I am wondering if there is something which accomplishes the same or better results but is 'build in'.
So often when I want to move some data or deploy some data I use this stored procedure. The 'receiver' just has to run the script with all the inserts in it.
The routine is not perfect but is very usefull. I am wondering if there is an even more perfect method/routine/.....
Ben
October 14, 2013 at 10:26 am
Is Powershell an option?
Quick and dirty output to screen (or remove the # to output to a file) for one table:
$server = New-Object ('Microsoft.SqlServer.Management.Smo.Server')
$scripter = New-Object ('Microsoft.SqlServer.Management.SMO.Scripter') $server
$scripter.Options.ScriptSchema = $false;
$scripter.Options.ScriptData = $true;
$tbl = $server.Databases['dbname'].Tables['tablename']
$scripter.EnumScript($tbl) #| Out-File 'filepath'
May 20, 2015 at 10:25 am
I just make an script called: SP GENERATE UPDATES, it would seemed to sp_generate_inserts that i've been using a long time ago. It will generate updates statements from a sql server table.
-- SP_GENERATE_UPDATES USAGE:
--EXEC sp_generate_updates 'Table_for_updates', ''
-- Note: If the id_list variable is empty, it will generate a update statement for all rows
--EXEC sp_generate_updates 'Table_for_updates', '123,124,125'
-- NOTE: This example generate 3 update querys for this id of the primary key column
-- ADVISE: it's Only admit one primary key in the table
-- By MJM
FYI:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_generate_updates] (@table_name varchar(256), @id_list varchar(max))
as
begin
-- SP_GENERATE_UPDATES USAGE:
--EXEC sp_generate_updates 'Table_for_updates', ''
-- Note: If the id_list variable is empty, it will generate a update statement for all rows
--EXEC sp_generate_updates 'Table_for_updates', '123,124,125'
-- NOTE: This example generate 3 update querys for this id of the primary key column
-- ADVISE: it's Only admit one primary key in the table
-- By MJM
declare @PK_COLUMN_NAME varchar(256)
declare @PK_COLUMN_VALUE int
declare @COLUMN_NAME varchar(512)
declare @GET_PK_COLUMN cursor
declare @GET_COLUMNS cursor
declare @QUERY nvarchar(500)
declare @QUERY2 nvarchar(500)
declare @GET_ROWS_CURSOR CURSOR
declare @sep varchar(3)
declare @var varchar(max)
declare @UPDATE_QUERY varchar(max)
select @PK_COLUMN_NAME=column_name from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_name = @table_name
print 'COLUMN NAME PK: ' + @PK_COLUMN_NAME
if (@PK_COLUMN_NAME IS NULL)
BEGIN
print 'You must define a primary key for ' + @table_name
END
if (@id_list <> '')
begin
set @QUERY = 'select ' + @PK_COLUMN_NAME + ' from ' + @table_name + ' where ' + @PK_COLUMN_NAME + ' in (' + @id_list + ')'
end
else
begin
set @QUERY = 'select ' + @PK_COLUMN_NAME + ' from ' + @table_name
end
set @QUERY = 'set @GET_ROWS_CURSOR = CURSOR FOR ' + @QUERY + '; OPEN @GET_ROWS_CURSOR'
exec sp_executesql@QUERY, N'@GET_ROWS_CURSOR CURSOR OUTPUT', @GET_ROWS_CURSOR OUTPUT
FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
WHILE @@FETCH_STATUS = 0
BEGIN
set @GET_COLUMNS = CURSOR
FOR select column_name from information_schema.columns where table_name = @table_name
open @GET_COLUMNS
fetch next from @GET_COLUMNS into @COLUMN_NAME
set @sep = ''
set @UPDATE_QUERY = ''
WHILE @@FETCH_STATUS = 0
BEGIN
if (@COLUMN_NAME <> @PK_COLUMN_NAME)
BEGIN
set @QUERY2 = 'select @var=cast(' + @COLUMN_NAME + ' as varchar(max)) from '+@table_name+' where '+ @PK_COLUMN_NAME + ' = ' + CAST(@PK_COLUMN_VALUE as varchar(9))
exec sp_executesql
@QUERY2,
N'@var varchar(max) OUTPUT',
@var OUTPUT
if (@var IS NOT NULL)
begin
set @UPDATE_QUERY = @UPDATE_QUERY + @sep + @COLUMN_NAME + '=''' + replace(@var, '''', '''''') + ''''
set @sep = ', '
end
END
fetch next from @GET_COLUMNS into @COLUMN_NAME
END
CLOSE @GET_COLUMNS
DEALLOCATE @GET_COLUMNS
print 'update ' + @table_name + ' set ' + @UPDATE_QUERY + ' where ' + @PK_COLUMN_NAME + ' = ' + CAST(@PK_COLUMN_VALUE as varchar(9))
FETCH NEXT FROM @GET_ROWS_CURSOR INTO @PK_COLUMN_VALUE
END
CLOSE @GET_ROWS_CURSOR
DEALLOCATE @GET_ROWS_CURSOR
end
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply