Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto generate insert Procedure Expand / Collapse
Author
Message
Posted Tuesday, August 17, 2010 10:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 17, 2010 11:23 AM
Points: 1, Visits: 2
CRATE procedure [dbo].[Auto_Create_Procedure]
@table_name varchar(200)
AS

DECLARE @column_name varchar(30)
DECLARE @column_length int
--DECLARE @table_name varchar(30)
DECLARE @column_string varchar(4000)
DECLARE @value_string varchar(4000)
DECLARE @ROW_COUNT int
DECLARE @data_type varchar(30)

set @ROW_COUNT = 0
set @column_string = ''
set @value_string = ''

--set @table_name ='tblBook'

DECLARE column_cursor CURSOR FOR
select b.name column_name, b.length, c.name data_type
from sysobjects a, syscolumns b, systypes c
where a.id = b.id and b.xtype = c.xtype and a.name = @table_name and c.name <> 'sysname'
order by colorder

-- Print PROCEDURE command

PRINT 'CREATE PROCEDURE Prc_Insert_' + @table_name + '('

OPEN column_cursor

FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type


WHILE @@FETCH_STATUS = 0
BEGIN
set @ROW_COUNT = @ROW_COUNT + 1
if(@ROW_COUNT <> @@CURSOR_ROWS)
-- IN TUNG THAM SO RA MAN HINH
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length) + ', '
else
PRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length)

--
set @column_string = @column_string + @column_name + ', '
--
set @value_string = @value_string + '@' + @column_name + ', '
FETCH NEXT FROM column_cursor
INTO @column_name, @column_length, @data_type
END

CLOSE column_cursor;
DEALLOCATE column_cursor;

PRINT ')'
PRINT 'AS'
PRINT 'BEGIN'

-- Print Insert command
print ' INSERT INTO ' + @table_name
print ' (' + SUBSTRING(@column_string, 1, LEN(@column_string) - 1 ) + ') '
print ' VALUES'
print ' (' + SUBSTRING(@value_string, 1, LEN(@value_string) - 1 ) + ')'
PRINT 'END'
PRINT 'GO'

Visit this site to reference SQL command
Post #970596
Posted Wednesday, August 25, 2010 7:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:08 AM
Points: 1,221, Visits: 1,280
Not sure what you question is or if you are just providing a script. The script doesn't work. You need to change 'CRATE' to 'CREATE'. Also, dbo.fnc_get_datatype does not exist

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #974819
Posted Tuesday, May 15, 2012 1:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 8:07 AM
Points: 1, Visits: 18
Been around for a long time and well used by a lot of people.

http://vyaskn.tripod.com/code/generate_inserts.txt


Post #1300065
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse