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!
