USE Tracker_Data
GO
SET ANSI_PADDING OFF
GO
USE Privacy_Tracker_Data
GO

isp_bcp_in_database
	  @dbName		= 'Tracker_Data'
	, @fp			= 'D:\data\Tracker_Data\Scramble'
	, @Formatpath		= 'D:\data\Tracker_Data\FORMATIN'
	, @RESTART		= 'T00000001'


USE Tracker_Data
GO
SET ANSI_PADDING ON
GO
USE Privacy_Tracker_Data
GO

UPDATE U
SET MyCol = U.MyCol
FROM inserted AS I
     ON dboMyTable AS U
         ON U.MyPK = I.MyPK

SELECT SQL FROM (
      SELECT DISTINCT 'PRINT ' + '''' + 'UPDATING ' + p.TABLE_NAME + ''''+CHAR(13)+CHAR(10)+'BEGIN TRAN' AS SQL
, p.TABLE_NAME, 1 AS SQLGROUP, 0 AS COLNO
	FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN Privacy_Tracker_Data..PRIVACY_COLUMN p
	  ON p.TABLE_CATALOG = c.TABLE_CATALOG
	 AND p.TABLE_SCHEMA = c.TABLE_SCHEMA
	 AND p.TABLE_NAME = c.TABLE_NAME
	 AND DATA_TYPE IN ('nchar','nvarchar','char','varchar')
UNION ALL
      SELECT DISTINCT 'UPDATE ' + p.TABLE_NAME + ' SET ' AS SQL
, p.TABLE_NAME, 1 AS SQLGROUP, 1 AS COLNO
	FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN Privacy_Tracker_Data..PRIVACY_COLUMN p
	  ON p.TABLE_CATALOG = c.TABLE_CATALOG
	 AND p.TABLE_SCHEMA = c.TABLE_SCHEMA
	 AND p.TABLE_NAME = c.TABLE_NAME
	 AND DATA_TYPE IN ('nchar','nvarchar','char','varchar')
UNION ALL
      SELECT '  ' + c.COLUMN_NAME + ' = ' + 'RTRIM('+c.COLUMN_NAME+')' AS SQL
, c.TABLE_NAME, 2 AS SQLGROUP, ORDINAL_POSITION AS COLNO
	FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN Privacy_Tracker_Data..DSINFO p
	  ON p.TABLESCHEMA = c.TABLE_SCHEMA
	 AND p.TABLENAME = c.TABLE_NAME
	 AND DATA_TYPE IN ('nchar','nvarchar','char','varchar')
	 AND ORDINAL_POSITION = 1
UNION ALL
      SELECT ', ' + c.COLUMN_NAME + ' = ' + 'RTRIM('+c.COLUMN_NAME+')' AS SQL
, c.TABLE_NAME, 3 AS SQLGROUP, ORDINAL_POSITION AS COLNO
	FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN Privacy_Tracker_Data..DSINFO p
	  ON p.TABLESCHEMA = c.TABLE_SCHEMA
	 AND p.TABLENAME = c.TABLE_NAME
	 AND DATA_TYPE IN ('nchar','nvarchar','char','varchar')
	 AND ORDINAL_POSITION <> 1
UNION ALL
      SELECT DISTINCT 'COMMIT TRAN' + CHAR(13) + CHAR(10) + 'GO' 
	+ CHAR(13) + CHAR(10) + 'BACKUP LOG Tracker_Data WITH TRUNCATE_ONLY' 
	+ CHAR(13) + CHAR(10) + 'GO'AS SQL
, p.TABLE_NAME, 4 AS SQLGROUP, 1 AS COLNO
	FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN Privacy_Tracker_Data..PRIVACY_COLUMN p
	  ON p.TABLE_CATALOG = c.TABLE_CATALOG
	 AND p.TABLE_SCHEMA = c.TABLE_SCHEMA
	 AND p.TABLE_NAME = c.TABLE_NAME
	 AND DATA_TYPE IN ('nchar','nvarchar','char','varchar')
) AS XXX
ORDER BY TABLE_NAME, SQLGROUP, COLNO

SET MyCol = NullIf(MyCol, ' ')

quote:

---

Originally posted by pootle_flump

I bcp in fixed width all the time. This is hardly ground breaking but I just dump it all into unconstrained staging tables, cleanse, dedupe etc and load into the live tables. I imagine you have discounted something like this already though....

---

UPDATING TRK_ASI_C_Detail

(1193 row(s) affected)

Server: Msg 3021, Level 16, State 1, Line 1
Cannot perform a backup or restore operation within a transaction.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

PRINT 'UPDATING TRK_ASI_C_Detail'
BEGIN TRAN
UPDATE TRK_ASI_C_Detail SET 
  PropIDNumber = RTRIM(PropIDNumber)
, PropIDCode = RTRIM(PropIDCode)
COMMIT TRAN
GO
BACKUP LOG Tracker_Data WITH TRUNCATE_ONLY
GO