generating insert scripts
This code generates a insert statement in SQl server
CREATE PROCEDURE SP_GEN_INSERT_SQL
@TABLENAME VARCHAR ( 2000 )
AS
BEGIN
DECLARE CUR_METADATA CURSOR FOR
SELECT NAME , XTYPE
FROM SYSCOLUMNS
WHERE ID IN ( SELECT ID FROM SYSOBJECTS WHERE NAME = @TABLENAME )
/* DECLARE VARIABLES*/
DECLARE @QRY1 VARCHAR (8000 ) /* Has column names */
DECLARE @QRY2 VARCHAR ( 8000 ) /* Has column values */
DECLARE @COLNAME VARCHAR ( 8000)
DECLARE @COLDATATYPE TINYINT
DECLARE @PRVCOLDATATYPE TINYINT
DECLARE @CHARPREFIX VARCHAR ( 5 )
DECLARE @CHARPOSTFIX VARCHAR ( 5 )
DECLARE @COLUMNSEPARATOR CHAR ( 1 )
DECLARE @NULLCHAR CHAR ( 4 )
SET @CHARPREFIX = ''''
SET @CHARPOSTFIX = ''''
SET @COLUMNSEPARATOR = ','
SET @NULLCHAR = 'NULL'
/* Start building the query */
SET @QRY1 = "SELECT '
INSERT INTO " + @TABLENAME + "(" --+ " VALUES ( "
SET @QRY2 = ''
OPEN CUR_METADATA
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add the Column names to the query */
SET @QRY1 = @QRY1 + @COLNAME + ','
/* Add Column values - Prefix & postfix the column values with quotes if they belong to any of the following datatypes */
/* TEXT - 35 | SMALLDATETIME - 58 | DATETIME - 61 | NTEXT - 99 | VARCHAR - 167 | CHAR - 175 | NVARCHAR - 231 | NCHAR - 239 */
IF ( @COLDATATYPE = 35 ) OR ( @COLDATATYPE = 58 ) OR ( @COLDATATYPE = 61 ) OR ( @COLDATATYPE = 99 ) OR
( @COLDATATYPE = 167 ) OR ( @COLDATATYPE = 175 ) OR ( @COLDATATYPE = 231 ) OR ( @COLDATATYPE = 239 )
BEGIN
IF @QRY2 = ''
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + "'NULL'" + ')' --+ "'')"
END
ELSE
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + "'NULL'" + ')' --+ "'')"
END
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @CHARPOSTFIX + @CHARPOSTFIX + @COLU
|
© 2008 dotnetwatch.com -- Privacy policy
Website Design & Internet Marketing by Shivam