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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy