Welcome to Comunidade Bloggers |create|it| Sign in | Join | Help

Raúl Ribeiro

Windows SharePoint Services 3.0 , .net, Microsoft Office SharePoint 2007, Office 2007 and 2010

News

Generate INSERT scripts for any table

Retirado de : DotnetSpider
 
 
 
 
/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 */
Posted: Thursday, July 14, 2005 8:07 PM by rrr
Filed under:

Comments

No Comments

Anonymous comments are disabled