SELECT column_name FROM information_schema.columns WHERE table_name IN ('tbl_EAI_InboundFileFormats','tbl_EAI_InboundFileFormatFields') AND COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 /* note: ignore dynamically generated identity columns; they have no meaning across different datasets */ SELECT column_name FROM information_schema.columns WITH (nolock) WHERE table_name = 'tbl_EAI_InboundFileFormats' AND column_name NOT IN () SELECT column_name FROM information_schema.columns WITH (nolock) WHERE table_name = 'tbl_EAI_InboundFileFormatFields' AND column_name NOT IN () SELECT #ValueList(qmd_formatColumns.column_name)# FROM tbl_EAI_InboundFileFormats WITH (nolock) WHERE 1=1 AND formatId IN () AND formatName IN () WITH formBaseKey AS ( SELECT formatId,ownerType,ownerId,formatName FROM tbl_EAI_InboundFileFormats WITH (nolock) WHERE 1=1 AND formatId IN () AND formatName IN () ) SELECT /*translate formatId to (ownerType,ownerId,formatName); formatId is not an interdatabase reliable key*/ formBaseKey.ownerType ,formBaseKey.ownerId ,formBaseKey.formatName ,#ValueList(qmd_formatFieldsColumns.column_name)# FROM tbl_EAI_InboundFileFormatFields AS fields WITH (nolock) INNER JOIN formBaseKey ON formBaseKey.formatId = fields.formatId 
/* *** SQL START *** */

	BEGIN TRANSACTION;
	
	DECLARE @formatId INT;
	DECLARE @fieldId INT;

	/*COULD NOT FIND ANY FORMATS TO EXPORT*/

	
	
	/* FORMAT: #qmd_formats.formatName# (#qmd_formats.ownerType##qmd_formats.ownerId#) */
	SET @formatId = (SELECT formatId FROM tbl_EAI_InboundFileFormats WHERE formatName='#escapeSQLStr(qmd_formats.formatName)#' AND ownerType='#escapeSQLStr(qmd_formats.ownerType)#' AND ownerId=#qmd_formats.ownerId#);
	IF (@formatId IS NULL) BEGIN 
		INSERT INTO tbl_EAI_InboundFileFormats (
			#ValueList(qmd_formatColumns.column_name)#
		)
		VALUES (
			, '#escapeSQLStr(qmd_formats[qmd_formatColumns.column_name])#'
		);
		SET @formatId = @@identity;
		PRINT 'INSERTED FORMAT #qmd_formats.formatName# (new formatId=' + CAST(@formatId as varchar(max)) + ', #qmd_formats.ownerType##qmd_formats.ownerId#)';
	END ELSE BEGIN
		UPDATE tbl_EAI_InboundFileFormats SET
			
				,#qmd_formatColumns.column_name# = '#escapeSQLStr(qmd_formats[qmd_formatColumns.column_name])#'
			
		WHERE formatId = @formatId;
		PRINT 'UPDATED FORMAT #qmd_formats.formatName# (formatId=' + CAST(@formatId as varchar(max)) + ', #qmd_formats.ownerType##qmd_formats.ownerId#)';
	END
	
	
		SELECT * FROM qmd_formatFields 
		WHERE OWNERTYPE = 
			AND OWNERID = 
			AND FORMATNAME = 
	
		/*Delete Removed Fields From #qmd_fieldsForThisFormat.formatName# (#qmd_fieldsForThisFormat.ownerType##qmd_fieldsForThisFormat.ownerId#) */
		DELETE FROM tbl_EAI_InboundFileFormatFields WHERE formatId = @formatId AND fieldName NOT IN (, '#escapeSQLStr(qmd_fieldsForThisFormat.fieldName)#');
		PRINT 'DELETED FIELDS FROM FORMAT  (formatId=' + CAST(@formatId as varchar(max)) + ', #qmd_formats.ownerType##qmd_formats.ownerId#)';
	
		/*COULD NOT FIND NEW FIELDS CORRESPONDING THIS FORMAT: #qmd_formats.formatName# (#qmd_formats.ownerType##qmd_formats.ownerId#) */
	
		
		/*Insert/Update Fields: ********************************************************************************************** */
		
		/* FIELD: #qmd_fieldsForThisFormat.formatName#.#qmd_fieldsForThisFormat.fieldName# (#qmd_fieldsForThisFormat.ownerType##qmd_fieldsForThisFormat.ownerId#) */
		SET @fieldId = (SELECT fieldId FROM tbl_EAI_InboundFileFormatFields WHERE formatId = @formatId AND fieldName = '#escapeSQLStr(qmd_fieldsForThisFormat.fieldName)#');
		IF (@fieldId IS NULL) BEGIN
			INSERT INTO tbl_EAI_InboundFileFormatFields (
				formatId,#ValueList(qmd_formatFieldsColumns.column_name)#
			)
			VALUES (
				@formatId,, '#escapeSQLStr(qmd_fieldsForThisFormat[qmd_formatFieldsColumns.column_name])#'
			);
			SET @fieldId = @@identity;
			PRINT 'INSERTED FIELD #qmd_fieldsForThisFormat.formatName#.#qmd_fieldsForThisFormat.fieldName# (formatId=' + CAST(@formatId as varchar(max)) + ', new fieldId=' + CAST(@fieldId as varchar(max)) + ', #qmd_formats.ownerType##qmd_formats.ownerId#)';
		END ELSE BEGIN
			UPDATE tbl_EAI_InboundFileFormatFields SET
				
					, #qmd_formatFieldsColumns.column_name# = '#escapeSQLStr(qmd_fieldsForThisFormat[qmd_formatFieldsColumns.column_name])#'
					
			WHERE formatId = @formatId
				AND fieldName = '#escapeSQLStr(qmd_fieldsForThisFormat.fieldName)#';
			PRINT 'UPDATED FIELD #qmd_fieldsForThisFormat.formatName#.#qmd_fieldsForThisFormat.fieldName# (formatId=' + CAST(@formatId as varchar(max)) + ', fieldId=' + CAST(@fieldId as varchar(max)) + ', #qmd_formats.ownerType##qmd_formats.ownerId#)';
		END
		
	
	
	
	
	COMMIT TRANSACTION;

/* *** SQL END *** */
#queryStringHTML#