generating insert scripts

»Dotnet Ads
»Message Boards
Message Boards
Dotnet Books

»Member Details
Register
Login
LogOut
Submit Code
Submit Jobs
Submit Projects

»Competition
Community
Winners
Prizes
Write For Us
Members

»Other Resources
Links
Dotnet Resources

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

Have a Question and dont know the answer post it below and get answers in minutes

Due to spam this feature is disabled
To get answers fast , make sure you enter a detailed subject for example: "DataGrid issues need answer" not "DataGrid"

Subject:

Catjegory Name:

Message: