question

SQLArnold avatar image
SQLArnold asked

Dynamically create insert statement for a table by table name and trying to handle NULL values by replacing string 'NULL' with NULL

SET NOCOUNT ON;

/******************************************************************

* need to figure out how to replace 'NULL' with NULL and not have the vairable value be NULL.

* Set variables below

******************************************************************/

DECLARE

@SourceDatabaseName sysname = 'database'

, @SourceSchemaName sysname = 'schema'

, @SourceTableName sysname = 'table'

, @ColumnPredicate NVARCHAR(100) = 'column'

, @ColumnPredicateValue NVARCHAR(100) = 'column value'

;

/******************************************************************

* celare variable for creating statements.

******************************************************************/

DECLARE

@ColumnDataList NVARCHAR(MAX) = ''

, @ColumnUpdate NVARCHAR(MAX) = ''

, @ColumnList NVARCHAR(MAX) = ''

, @PkColumnName NVARCHAR(256) = ''

, @StringColumnUpdateList NVARCHAR(MAX) = ''

, @StringColumnUpdatePredicateList NVARCHAR(MAX) = ''

, @Identity BIT = 0

, @SQL VARCHAR(8000) = ''


DECLARE

@DatabaseSchemaTableName NVARCHAR(256) = @SourceDatabaseName + '.' + @SourceSchemaName + '.' + @SourceTableName;


/******************************************************************

*

******************************************************************/

IF OBJECT_ID('tempdb.dbo.#InsertRecords')IS NOT NULL

DROP TABLE #InsertRecords;


CREATE TABLE #InsertRecords

(

Script VARCHAR(MAX)

, Id INT IDENTITY(1,1) PRIMARY KEY

);

/******************************************************************

*

******************************************************************/

SELECT

TOP(1)

@PkColumnName = col.COLUMN_NAME

, @Identity = so.is_identity

FROM

INFORMATION_SCHEMA.COLUMNS AS col

LEFT OUTER JOIN(

SELECT DISTINCT

c.name

, c.is_identity

FROM sys.columns AS c

) AS so

ON col.COLUMN_NAME = so.name

AND so.is_identity = 1

WHERE

col.TABLE_CATALOG = @SourceDatabaseName

AND col.TABLE_SCHEMA = @SourceSchemaName

AND col.TABLE_NAME = @SourceTablename

ORDER BY

so.is_identity DESC;


/******************************************************************

*

******************************************************************/

SELECT

@PkColumnName = col.COLUMN_NAME

, @ColumnList = COALESCE(@ColumnList + ', ' ,'') + col.COLUMN_NAME

, @ColumnDataList = @ColumnDataList + CHAR(9) +

CASE

WHEN UPPER(col.DATA_TYPE) IN ('VARCHAR','VARCHAR','TEXT','NTEXT')

THEN

CASE /*STRINGS*/

WHEN col.IS_NULLABLE = 'NO'

THEN '''''' + ''' + ' + col.COLUMN_NAME + ' + ' + ''''''

ELSE '''''' + ''' + CASE WHEN [' + col.COLUMN_NAME + N'] IS NULL THEN ''NULL'' ELSE [' + col.COLUMN_NAME + N'] END + ' + ''''''

END + ''',' + CHAR(13)

WHEN UPPER(col.DATA_TYPE) LIKE '%DATE%'

THEN

CASE /*DATES*/

WHEN col.IS_NULLABLE = 'NO'

THEN '''''' + ''' + CAST(' + col.COLUMN_NAME + ' AS VARCHAR) + ' + ''''''

ELSE '''''' + ''' + CASE WHEN CAST([' + col.COLUMN_NAME + N'] AS VARCHAR) IS NULL THEN ''NULL'' ELSE CAST([' + col.COLUMN_NAME + N'] AS VARCHAR) END + ' + ''''''

END + ''',' + CHAR(13)

WHEN UPPER(col.DATA_TYPE) IN ('INT','BIT','DECIMAL','FLOAT','NUMERIC','MONEY')

THEN

CASE/*INTEGERS*/

WHEN col.IS_NULLABLE = 'NO'

THEN 'CAST(' + col.COLUMN_NAME + ' AS VARCHAR) + '

ELSE ''' + CASE WHEN CAST([' + col.COLUMN_NAME + N'] AS VARCHAR) IS NULL THEN ''NULL'' ELSE CAST([' + col.COLUMN_NAME + N'] AS VARCHAR) END + '

END + ''',' + CHAR(13)

END

FROM

INFORMATION_SCHEMA.COLUMNS AS col

LEFT OUTER JOIN(

SELECT DISTINCT

c.name

, c.is_identity

FROM sys.columns AS c

) AS so

ON col.COLUMN_NAME = so.name

AND so.is_identity = 1

WHERE

col.TABLE_CATALOG = @SourceDatabaseName

AND col.TABLE_SCHEMA = @SourceSchemaName

AND col.TABLE_NAME = @SourceTablename;


--SELECT @ColumnDataList


/******************************************************************

* Remove first comma from columnlist

******************************************************************/

SET @ColumnList = STUFF(@ColumnList,1,1, '')

/******************************************************************

* remove ending comma from list and add values for insert

******************************************************************/

SET @ColumnDataList = REVERSE(STUFF(REVERSE(@ColumnDataList),1,3,''))

SET @ColumnDataList = '''VALUES('' + ' + @ColumnDataList + ' + '''''')''';


--SELECT @ColumnList, @ColumnDataList

/******************************************************************

* Get varchar columns to create update statement to replace 'NULL'

******************************************************************/

SELECT

@StringColumnUpdateList = COALESCE(@StringColumnUpdateList + ' ' ,'') + CHAR(9) + 'UPDATE ' + @DatabaseSchemaTableName + CHAR(13) + CHAR(9) + CHAR(9) + 'SET ' + col.COLUMN_NAME + ' = NULL ' + CHAR(13) + CHAR(10) + CHAR(9) + 'WHERE ' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) + col.COLUMN_NAME + ' = ''NULL'';' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

FROM

INFORMATION_SCHEMA.COLUMNS AS col

LEFT OUTER JOIN(

SELECT DISTINCT

c.name

, c.is_identity

FROM sys.columns AS c

) AS so

ON col.COLUMN_NAME = so.name

AND so.is_identity = 1

WHERE

col.TABLE_CATALOG = @SourceDatabaseName

AND col.TABLE_SCHEMA = @SourceSchemaName

AND col.TABLE_NAME = @SourceTablename

AND UPPER(col.DATA_TYPE) IN ('VARCHAR','VARCHAR','TEXT','NTEXT')

AND col.IS_NULLABLE = 'YES';


/******************************************************************

* Create sql, to create insert data list.

******************************************************************/

SET @SQL = '

SELECT

' + @ColumnDataList + '

FROM

' + @DatabaseSchemaTableName + '

WHERE

' + @ColumnPredicate + ' = ''' + @ColumnPredicateValue + ''';

';

--PRINT @SQL;

/******************************************************************

* execute sql to create insert data list

******************************************************************/

INSERT INTO #InsertRecords(Script)

EXECUTE(@SQL);

/******************************************************************

* Set @ColumnDataList

******************************************************************/

SELECT

TOP(1)

@ColumnDataList = CHAR(9) + Script

FROM

#InsertRecords;


/******************************************************************

* Create check to see if data exists before inserting.

******************************************************************/

SET @SQL = 'USE ' + @SourceDatabaseName + ';

IF EXISTS(SELECT * FROM ' + @DatabaseSchemaTableName + ' WHERE ' + @ColumnPredicate + ' = ''' + @ColumnPredicateValue + ''')

BEGIN

DECLARE @Message NVARCHAR(MAX) = ''Record already exists in table ' + @DatabaseSchemaTableName + '!'';

RAISERROR (@Message, 0, 1) WITH NOWAIT;

END

ELSE

BEGIN

BEGIN TRY' + CHAR(13) + CHAR(10) + CHAR(9) + '

BEGIN TRANSACTION

'

PRINT @SQL;

/******************************************************************

* Add insert identity if on table has identity column.

******************************************************************/

IF @Identity = 1

BEGIN

SET @SQL = CHAR(9) + 'SET IDENTITY_INSERT ' + @DatabaseSchemaTableName + ' ON;';

PRINT @SQL;

END;

/******************************************************************

* Create and Print sql

******************************************************************/

SET @SQL = CHAR(9) + '

/*****************************************************************************

* Insert record into table ' + @DatabaseSchemaTableName + '

*****************************************************************************/

INSERT INTO ' + @DatabaseSchemaTableName + CHAR(13) + CHAR(10) + CHAR(9) + '(' + CHAR(13) + CHAR(10) + CHAR(9) + @ColumnList + CHAR(13) + CHAR(10) + CHAR(9) + ')';

PRINT @SQL;

/******************************************************************

* Set @ColumnDataList

******************************************************************/

PRINT @ColumnDataList;

/******************************************************************

* Add insert identity off if table has identity column.

******************************************************************/

IF @Identity = 1

BEGIN

SET @SQL = CHAR(13) + CHAR(10) + CHAR(9) + 'SET IDENTITY_INSERT ' + @DatabaseSchemaTableName + ' OFF;';

PRINT @SQL;

END;


/******************************************************************

* Create update statement

******************************************************************/

PRINT '

/******************************************************************

* Update columns with ''NULL'' to NULL

******************************************************************/

'

PRINT @StringColumnUpdateList;


SET @SQL = '

IF @@TRANCOUNT > 0 AND (XACT_STATE()) = 1

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0 OR (XACT_STATE()) = -1

ROLLBACK TRANSACTION;


THROW;


END CATCH


END;

/***************************************************************************************************

* Verfiy insert record was inserted

***************************************************************************************************/

SELECT *

FROM

' + @DatabaseSchemaTableName + ' WITH (NOLOCK)

WHERE

' + @ColumnPredicate + ' = ''' + @ColumnPredicateValue + ''';

'

PRINT @SQL;

-- Section I am trying to correct with example columns and data.

INSERT INTO DATABASE.SCHEMA.table

(

Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, COLUMN,

Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, COLUMN,

Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column, Column

)

VALUES(1,

'name',

'number',

'data',

'fileExtenetion',

'folderlocation',

'folderlocation',

'folderlocation',

'folderlocation',

'data',

'data',

'datac',

'data',

1,

NULL,

'NULL',

'NULL',

'Jan 24 2022 2:59PM',

'data\data',

'NULL',

'data',

'NULL',

2,

'packagename',

'package',

'server',

'data',

'NULL',

'NULL',

'NULL',

'NULL',

'NULL',

'NULL',

NULL,

NULL,

NULL,

NULL,

'NULL',

'NULL')

CHEERS!

nullnullvalues
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.