|
|
/*—————————————————————————————————————————————————————————————————————————————
AUTHOR KARTHIK
DESCRIPTION BUILDS THE INSERT QUERY FOR ANY GIVEN TABLE
—————————————————————————————————————————————————————————————————————————————*/
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 + @COLUMNSEPARATOR
END
ELSE /*If the column is a number dont prefix/postfix quotes */
BEGIN
SET @QRY2 = @QRY2 + ”” + ‘+ISNULL(CAST(‘ + @COLNAME + ‘ AS VARCHAR),’ + “‘NULL'” + ‘)’
SET @QRY2 = @QRY2 + ‘+’ + @CHARPOSTFIX + @COLUMNSEPARATOR
END
–PRINT @QRY2
SET @PRVCOLDATATYPE = @COLDATATYPE
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
END
CLOSE CUR_METADATA
DEALLOCATE CUR_METADATA
SET @QRY1 = SUBSTRING ( @QRY1 , 1 , LEN ( @QRY1 ) – 1 )
SET @QRY1 = @QRY1 + ‘)VALUES(‘ –+ “‘” + “‘” +”‘”
SET @QRY2 = SUBSTRING ( @QRY2 , 1 , LEN ( @QRY2 ) – 1 )
SET @QRY2 = @QRY2 + ‘)’ + @CHARPOSTFIX
SET @QRY2 = @QRY2 + ‘ FROM ‘ + @TABLENAME
/* Create a temporary table to hold the result of the query – i.e. it will have the INSERT statements */
CREATE TABLE #SQLTRACE ( SQL VARCHAR ( 8000 ) )
–print @QRY1 + @QRY2
SET @QRY2 = ‘ INSERT INTO #SQLTRACE ‘ + @QRY1 + @QRY2
IF LEN ( @QRY2 ) < 8000
BEGIN
SET NOCOUNT ON
–PRINT @QRY2
EXEC ( @QRY2 )
–PRINT ‘ INSERT INTO #SQLTRACE ‘ + @QRY1 + @QRY2
–EXEC ( ‘ INSERT INTO #SQLTRACE ‘ + @QRY1 + @QRY2 )
UPDATE #SQLTRACE SET SQL = REPLACE ( SQL , “‘NULL'” , @NULLCHAR )
SELECT * FROM #SQLTRACE
END
ELSE
BEGIN
PRINT ‘UNABLE TO GENERATE INSERT STATEMENTS FOR TABLE :’ +@TABLENAME
END
SET NOCOUNT OFF
END /* END OF FILE */
