Auto generate insert Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Auto generate insert Procedure

Auto generate insert Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

vietcode

Posted Tuesday, August 17, 2010 10:31 AM

Forum 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

Mike01

Posted Wednesday, August 25, 2010 7:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 9:20 AM
Points: 1,191, Visits: 1,251

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

Edward.Powell

Posted Tuesday, May 15, 2012 1:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 18, 2012 9:41 PM
Points: 1, Visits: 16

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 »

Permissions