duh

gistfile1.txt

--BD tables need to exist on the destination. Run EntWin/website against it first

DECLARE @source_prefix nvarchar(4000) = 'Data_BDMaster.dbo.'
DECLARE @dest_prefix nvarchar(4000) = 'Data_001.dbo.'

declare @tables table(table_name nvarchar(4000))

insert into @tables values 
('BDFieldValues'),
('BDCountriesAudit'),
('BDProjects'),
('BDCreditCardAccount'),
('BDDocumentPresets'),
('BDCouponItems'),
('BDItemLocationSupport'),
('BDNodeDocument'),
('BDProvincesAudit'),
('BDKeyValues'),
('BDServicesTracking'),
('BDLogging'),
('BDWorkTableWebItemTree'),
('BDOrderRules'),
('BDOrderSupport'),
('BDEntityForms'),
('BDFreightCosts'),
('BDSyncLocations'),
('BDDocumentLinks'),
('BDPOOrderSupport'),
('BDQueries'),
('BDCartPayments'),
('BDCreditCardInfo'),
('BDEntityFormFields'),
('BDEntityTypes'),
('BDFreightInvoiceDetails'),
('BDFreightTypes'),
('BDShipViaMapping'),
('BDServiceTrackedKeys'),
('BDDocumentTypes'),
('BDAccessControlCodeMultiScan'),
('BDEntities'),
('BDReturnLocations'),
('BDRelationShipTypes'),
('BDSyncTargets'),
('BDUpdates'),
('BDShipInterface'),
('BDNodeLinks'),
('BDRelationShips'),
('BDOrderShipmentPackageDetails'),
('BDSyncTables'),
('BDCreditCardTrxs'),
('BDOESetup'),
('BDCustomerSupport'),
('BDPunchOutSetupRequests'),
('BDRoles'),
('BDScheduledJobs'),
('BDWorkflows'),
('BDAccessControl'),
('BDUOMs'),
('BDDBRevLevel'),
('BDSalesPersonSupport'),
('BDAccessControlTrx'),
('BDKeyTable'),
('BDAccessControlCode'),
('BDWebCategories'),
('BDWorkflowSteps'),
('BDTaxServiceAccounts'),
('BDOrderRuleTracking'),
('BDUpdates2'),
('BDProjectStatuses'),
('BDPOSDrawers'),
('BDSecPermissions'),
('BDMfgInformation'),
('BDEWRIncidents'),
('BDUserCustomers'),
('BDMapping'),
('BDTrailerMakeLinks'),
('BDShipViaFreightAccounts'),
('BDUserWorkflows'),
('BDScheduledEvents'),
('BDPOSCountdowns'),
('BDSecRoles'),
('BDSites'),
('BDBOLs'),
('BDCustomerItemUOMs'),
('BDSiteTransfer'),
('BDBOLDetails'),
('BDInvoiceSupport'),
('BDUserWorkflowSteps'),
('BDEmailTemplates'),
('BDServicesAudit'),
('BDEventRegistrations'),
('BDItemUOMs'),
('BDCustomerItemSupport'),
('BDInventoryTransactionTypes'),
('BDCommandPermissions'),
('BDItemWebCategories'),
('BDDocumentLayouts'),
('BDCarts'),
('BDOrderCampaigns'),
('BDBillingTypes'),
('BDScheduledEventRegistrationItems'),
('BDVendorItemSupport'),
('BDCampaigns'),
('BDItemCrossReferences'),
('BDRequsitionSupport'),
('BDBONotifications'),
('BDCampaignUsers'),
('BDSetup25'),
('BDOrderFreightPOs'),
('BDRequestForQuoteSupport'),
('BDShipmentQuotes'),
('BDAudienceUsers'),
('BDEventItems'),
('BDTasksAudit'),
('BDIncidentsAudit'),
('BDUsers'),
('BDAudienceEntities'),
('BDCompetitors'),
('BDTransitions'),
('BDEntitySupport'),
('BDScheduledEventMaximumCharges'),
('BDFileData'),
('BDCompetitorItems'),
('BDZones'),
('BDMembershipTracking'),
('BDOrderLineSupport'),
('BDGroupRules'),
('BDIncidents'),
('BDCompetitorItemPrices'),
('BDShipFrequencies'),
('BDTasks'),
('BDCampaignGroups'),
('BDJobGroups'),
('BDTenderTypes'),
('BDCartLineSerialLotTrxs'),
('BDUserFavorite'),
('BDCartLineItemNotes'),
('BDScheduledEventRegistrationTypes'),
('BDKeyPairs'),
('BDFiles'),
('BDConnections'),
('BDItems25'),
('BDEventAttendees'),
('BDRelationshipTitles'),
('BDCartLines'),
('BDRelationshipTitlesAudit'),
('BDUNSPSCs'),
('BDSalesTaxCerts'),
('BDUserPhones'),
('BDShipViaConversion'),
('BDConnectorLinkages'),
('BDUserNotes'),
('BDAddresses'),
('BDConnectorAccounts'),
('BDItemAvailabilities'),
('BDAudience'),
('BDGroups'),
('BDWebCategoriesauditold20131213'),
('BDWebCategoriesAudit'),
('BDIPLocations'),
('BDUserGroups'),
('BDItemUpsellLinks'),
('BDVendorItems'),
('BDContentCodes'),
('BDSyncActionTable'),
('BDTimeSlips'),
('BDNotes'),
('BDItemXref'),
('BDItemAudience'),
('BDVendorSupport'),
('BDTaskCategories'),
('BDRoleUsers'),
('BDDistForm'),
('BDOrderCancellationReasons'),
('BDVendorAddressSupport'),
('BDRolePermissions'),
('BDOrderShipments'),
('BDDeliveryAreas'),
('BDReports'),
('BDFreightPayCodes'),
('BDBrainMail'),
('BDOrderShipmentPackages'),
('BDCountries'),
('BDFieldDefinitions'),
('BDOrderCancellations'),
('BDProjectTypes'),
('BDProvinces'),
('BDFields'),
('BDShipViaCodeConversion'),
('BDDeliveryAreaDetails'),
('BDServices'),
('BDShipToSupport'),
('BDPhysicalAddresses'),
('BDUsersAudit'),
('BDGiftCardIncentivebak'),
('BDItemFeatureSupport'),
('BDImageMaps'),
('BDFreightExceptions'),
('BDGiftCardIncentive'),
('BDDocument'),
('BDFreightPrices'),
('BDDocumentLinkKeys'),
('BDGiftCardInfobak'),
('BDImageMapHotSpots'),
('BDPageHits'),
('BDGiftCardInfo'),
('BDShipViaBestWayConversionExceptions'),
('BDOrderRuleResults'),
('BDCartLineComponents'),
('BDGiftCardTrxbak'),
('BDOrderRuleTriggers'),
('BDFreightDimensions'),
('BDGiftCardTrx'),
('BDSettings'),
('BDImageMapDocuments'),
('BDNodes'),
('BDDocumentLinkGroups'),
('BDCountriesAlternateNames'),
('BDFreightAccounts'),
('BDLocationSupport'),
('BDGLImportRecords'),
('BDCustomerFreightAccounts'),
('BDItemUpsellLinksAudit')

declare @table_name nvarchar(4000)
declare cur CURSOR LOCAL for
    select table_name from @tables

open cur

fetch next from cur into @table_name

while @@FETCH_STATUS = 0 BEGIN

	DECLARE @source nvarchar(4000) = @source_prefix + '[' + @table_name + ']'
	DECLARE @dest nvarchar(4000) = @dest_prefix + '[' + @table_name + ']'

	DECLARE @columnList nvarchar(max) 

	SET @columnList = (SELECT STUFF(
		(SELECT ', ' + v
		 FROM (select '['+ column_name + ']' FROM INFORMATION_SCHEMA.COLUMNS 
			   where table_name = @table_name
					 and COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME),COLUMN_NAME,'IsComputed') = '0') AS v (v)
		 FOR XML PATH (''), TYPE
		).value('.[1]', 'varchar(max)'), 1, 2, ''))
	
	if(@columnList is not null)
	exec ('if (select count(*) from ' + @dest + ') = 0 insert into ' + @dest + '(' + @columnList + ') select ' + @columnList + ' from '+ @source +' ')
	else select @dest
	
	fetch next from cur into @table_name
END

close cur
deallocate cur