April 15, 2015 at 8:38 am
Hi all
We've had a new server set up with SQL 2012 and I'm in the process of moving data to it from a 2008 (SP2) server.
Details are as follows:-
2012 instance:- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
2008 instance:- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (VM)
I don't want to do a backup/restore routine as there are collation conflicts on the 2008 server.
I've created the database and tables on the 2012 instance and now I want to transfer the data from the 2008 instance to the 2012 one.
The 2012 instance has a linked server to the 2008 instance.
I was trying to use sp_MSForEachTable (I know, it's old and will probably disappear shortly) but that doesn't seem to work properly because some of the columns have an Identity field set up.
Some of the tables have upwards of 10 million records in them and are quite sizeable.
Does anyone have any ideas on how I can achieve the transfer without a back-up/restore?
Hopefully all the above makes sense, but feel free to ask for more info.
April 15, 2015 at 8:47 am
BCP data in and out. You can script the whole thing using sys.tables.
-- Gianluca Sartori
April 15, 2015 at 8:50 am
The database is currently sat at around 130GB and I can't get to a network share with that much available space.
I was also hoping to do this via the linked servers rather than exporting all the data and importing it all.
April 15, 2015 at 8:56 am
BCP in and out one table at a time.
LInked servers are not meant for heavy data transfer. The process would end up being unacceptably slow.
-- Gianluca Sartori
April 15, 2015 at 9:27 am
To be honest, speed isn't of the essence. I've pre-warned our IT dept that I'll be moving gigabytes of data around the network and they're quite happy.
I'm looking for a solution I can set off and ignore for as long as it takes.
However, if BCP is the only solution, I'll do some research on it (never used it before) and see how far I get.
April 15, 2015 at 11:44 am
Third party tool like SQL Data Compare [/url]is a great way to get something like this done.
DISCLAIMER: I work for those guys.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2015 at 1:42 am
Thanks Grant, I'll have a look at it now.
April 16, 2015 at 2:26 am
Hi Grant
I've compared the databases using SQL data Compare and it says "195 tables or views that could not be compared".
I've transferred the database from server A to server B by scripting it out from A (using generate scripts on tables and procedures) and running the script on B.
I did create the dfatabase first as I wanted to ensure there were no collation conflicts.
Any ideas?
::edit::
Could it be because the source fields are nvarchar and the destination fields are varchar?
April 16, 2015 at 4:48 am
No, that would show up as differences. Have the table names and or schema's been changed? If they're named differently, our tool can't figure out that TableA ought to be TableB.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2015 at 4:58 am
I've just checked the schemas in both databases and there is only the standard dbo.
The table names are identical as I scripted them from the old databases as CREATES using the Generate Scripts option and just ran that against the new database (same name) on the new server.
April 16, 2015 at 5:39 am
Then I'm not sure I understand what's happening. sorry. Can you post a screen shot or two with the list? Hide the names if you need to. Or, you might want to post this on the support site.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2015 at 6:00 am
Thanks Grant.
I'll drop this onto the support site as SQL DAta Compare says the tables are mapped down to field level.
Regards
Richard
April 16, 2015 at 8:28 am
Using your original idea, just add a bit of code to detect if there's an identity column or not. If there is, then use the SET IDENTITY_INSERT command to allow you to insert the data and use it again to turn it back off when the table data copy is complete for the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2015 at 9:01 am
Hi Jeff
I did think of that, but it doesn't seem to persist.
This what I had 10 minutes ago:-
USE SystmOneData
EXEC sys.sp_MSforeachtable '
truncate table ?
begin try
set identity_insert ? on
end try
begin catch
end catch
'
insert into wtblSRTTOMedication([Continuation],[DateEvent],[DateEventRecorded],[DateFirstAdmin],[DateMedicationEnd],[DateSupplyUntil],[IDDoneBy],[IDMedicationApprovedBy],[IDMedicationEndedBy],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationApprovedBy],[IDProfileMedicationEndedBy],[IDReferralIn],[InstalmentDailyDose],[InstalmentSupervisedDose],[MedicationApproved],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SourceOfSupply],[StandardIssueDose],[StandardIssueQuantity],[StandardOrInstalment],[TextualEventDoneBy])
select [Continuation],[DateEvent],[DateEventRecorded],[DateFirstAdmin],[DateMedicationEnd],[DateSupplyUntil],[IDDoneBy],[IDMedicationApprovedBy],[IDMedicationEndedBy],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationApprovedBy],[IDProfileMedicationEndedBy],[IDReferralIn],[InstalmentDailyDose],[InstalmentSupervisedDose],[MedicationApproved],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SourceOfSupply],[StandardIssueDose],[StandardIssueQuantity],[StandardOrInstalment],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.wtblSRTTOMedication
insert into tblSRPatientContactDetails([ContactNumber],[ContactType],[DateEvent],[DateEventRecorded],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [ContactNumber],[ContactType],[DateEvent],[DateEventRecorded],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPatientContactDetails
insert into wtblSRVariableDoseCDMedication([DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationEndedBy],[IDReferralIn],[MedicationCourseLength],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SupervisedDose],[TextualEventDoneBy])
select [DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationEndedBy],[IDReferralIn],[MedicationCourseLength],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SupervisedDose],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.wtblSRVariableDoseCDMedication
insert into tblSRPatientGroups([DateEvent],[DateEventRecorded],[DateRemoved],[GroupType],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileRemoved],[IDStaffRemoved],[Members],[Name],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DateEvent],[DateEventRecorded],[DateRemoved],[GroupType],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileRemoved],[IDStaffRemoved],[Members],[Name],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRPatientGroups
insert into wtblSRVisit([CurrentStatus],[DateBooked],[DateEventRecorded],[DateRequested],[FollowUpDetails],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileAssigned],[IDProfileEnteredBy],[IDProfileRequested],[IDReferralIn],[RemovedData],[RowIdentifier])
select [CurrentStatus],[DateBooked],[DateEventRecorded],[DateRequested],[FollowUpDetails],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileAssigned],[IDProfileEnteredBy],[IDProfileRequested],[IDReferralIn],[RemovedData],[RowIdentifier] from [vm-dwdb].systmonedata.dbo.wtblSRVisit
insert into tblSRPatientInformation([BirthPlace],[DateBirth],[DateDeath],[DateDeRegistration],[DateRegistration],[EmailAddress],[FirstName],[Gender],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[MiddleNames],[NHSNumber],[PreferredPharmacy],[PreviousSurname],[RegistrationStatus],[RemovedData],[RowIdentifier],[SpeaksEnglish],[SSRef],[Surname],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TestPatient],[Title])
select [BirthPlace],[DateBirth],[DateDeath],[DateDeRegistration],[DateRegistration],[EmailAddress],[FirstName],[Gender],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[MiddleNames],[NHSNumber],[PreferredPharmacy],[PreviousSurname],[RegistrationStatus],[RemovedData],[RowIdentifier],[SpeaksEnglish],[SSRef],[Surname],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TestPatient],[Title] from [vm-dwdb].systmonedata.dbo.tblSRPatientInformation
insert into wtblSRWaitingList([DateAdded],[DateAppointmentEnd],[DateAppointmentStart],[DateDeletedFromWaitingList],[DateDue],[DateWaitCompleted],[IDClinician],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileClinician],[IDReferralIn],[Notes],[Priority],[RemovedData],[RestrictedToCaseload],[RotaType],[RowIdentifier],[TargetMaximumWait],[WaitingListName])
select [DateAdded],[DateAppointmentEnd],[DateAppointmentStart],[DateDeletedFromWaitingList],[DateDue],[DateWaitCompleted],[IDClinician],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileClinician],[IDReferralIn],[Notes],[Priority],[RemovedData],[RestrictedToCaseload],[RotaType],[RowIdentifier],[TargetMaximumWait],[WaitingListName] from [vm-dwdb].systmonedata.dbo.wtblSRWaitingList
insert into tblSRPatientRegistration([DateDeRegistration],[DateEventRecorded],[DateRegistration],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[PreferredPharmacy],[RegistrationStatus],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DateDeRegistration],[DateEventRecorded],[DateRegistration],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[PreferredPharmacy],[RegistrationStatus],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRPatientRegistration
insert into SystmOneDownloadSpecification([ColumnDataType],[ColumnDescription],[ColumnLength],[ColumnName],[ColumnOrdinal],[LinkedColumn1],[LinkedColumn2],[LinkedTable],[NewDataType],[TableDescription],[TableName])
select [ColumnDataType],[ColumnDescription],[ColumnLength],[ColumnName],[ColumnOrdinal],[LinkedColumn1],[LinkedColumn2],[LinkedTable],[NewDataType],[TableDescription],[TableName] from [vm-dwdb].systmonedata.dbo.SystmOneDownloadSpecification
insert into tblSRPatientRelationship([CallCentreCallBackConsent],[CaresForPatient],[CodeRelationshipWithUser],[CommunicationFormat],[ContactMethod],[ContactOrder],[CopyCorrespondence],[DateEnded],[DateEvent],[DateEventRecorded],[FinancialRepresentative],[HasParentalResponsibility],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDPatientRelationshipWith],[IDProfileEnteredBy],[IDRelationshipWithPatient],[InterpreterRequired],[KeyHolder],[NextOfKin],[PersonalGuardianOrProxy],[PrincipalCarerForPatient],[RelationshipType],[RelationshipWithCounty],[RelationshipWithDateOfBirth],[RelationshipWithEmailAddress],[RelationshipWithFax],[RelationshipWithHouseName],[RelationshipWithHouseNumber],[RelationshipWithLocality],[RelationshipWithMobileTelephone],[RelationshipWithName],[RelationshipWithOrganisation],[RelationshipWithPostCode],[RelationshipWithPostTown],[RelationshipWithRoad],[RelationshipWithSex],[RelationshipWithSpokenLanguage],[RelationshipWithTelephone],[RelationshipWithWorkTelephone],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [CallCentreCallBackConsent],[CaresForPatient],[CodeRelationshipWithUser],[CommunicationFormat],[ContactMethod],[ContactOrder],[CopyCorrespondence],[DateEnded],[DateEvent],[DateEventRecorded],[FinancialRepresentative],[HasParentalResponsibility],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDPatientRelationshipWith],[IDProfileEnteredBy],[IDRelationshipWithPatient],[InterpreterRequired],[KeyHolder],[NextOfKin],[PersonalGuardianOrProxy],[PrincipalCarerForPatient],[RelationshipType],[RelationshipWithCounty],[RelationshipWithDateOfBirth],[RelationshipWithEmailAddress],[RelationshipWithFax],[RelationshipWithHouseName],[RelationshipWithHouseNumber],[RelationshipWithLocality],[RelationshipWithMobileTelephone],[RelationshipWithName],[RelationshipWithOrganisation],[RelationshipWithPostCode],[RelationshipWithPostTown],[RelationshipWithRoad],[RelationshipWithSex],[RelationshipWithSpokenLanguage],[RelationshipWithTelephone],[RelationshipWithWorkTelephone],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPatientRelationship
insert into tblSRPrimaryCareMedication([DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDReferralIn],[IsDentalMedication],[IsHospitalMedication],[IsOtherMedication],[IsRepeatMedication],[MedicationDosage],[MedicationQuantity],[NameOfMedication],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDReferralIn],[IsDentalMedication],[IsHospitalMedication],[IsOtherMedication],[IsRepeatMedication],[MedicationDosage],[MedicationQuantity],[NameOfMedication],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPrimaryCareMedication
insert into tblSRQuestionnaire([DtPublished],[IDOrganisation],[IDOrganisationVisibleTo],[IDProfilePublishedBy],[QuestionnaireName],[QuestionnaireVersion],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DtPublished],[IDOrganisation],[IDOrganisationVisibleTo],[IDProfilePublishedBy],[QuestionnaireName],[QuestionnaireVersion],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRQuestionnaire
There's a lot more tables than that but you get the idea.
Running the code gives me a message :-
"Msg 544, Level 16, State 1, Line 12
Cannot insert explicit value for identity column in table 'tblSRPatientContactDetails' when IDENTITY_INSERT is set to OFF."
I suppose I could take out the relevant column and let the system generate new ID's (won't cause me any issues to do that) unless anyone has any other ideas?
April 16, 2015 at 7:11 pm
richardmgreen1 (4/16/2015)
Hi JeffI did think of that, but it doesn't seem to persist.
This what I had 10 minutes ago:-
USE SystmOneData
EXEC sys.sp_MSforeachtable '
truncate table ?
begin try
set identity_insert ? on
end try
begin catch
end catch
'
insert into wtblSRTTOMedication([Continuation],[DateEvent],[DateEventRecorded],[DateFirstAdmin],[DateMedicationEnd],[DateSupplyUntil],[IDDoneBy],[IDMedicationApprovedBy],[IDMedicationEndedBy],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationApprovedBy],[IDProfileMedicationEndedBy],[IDReferralIn],[InstalmentDailyDose],[InstalmentSupervisedDose],[MedicationApproved],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SourceOfSupply],[StandardIssueDose],[StandardIssueQuantity],[StandardOrInstalment],[TextualEventDoneBy])
select [Continuation],[DateEvent],[DateEventRecorded],[DateFirstAdmin],[DateMedicationEnd],[DateSupplyUntil],[IDDoneBy],[IDMedicationApprovedBy],[IDMedicationEndedBy],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationApprovedBy],[IDProfileMedicationEndedBy],[IDReferralIn],[InstalmentDailyDose],[InstalmentSupervisedDose],[MedicationApproved],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SourceOfSupply],[StandardIssueDose],[StandardIssueQuantity],[StandardOrInstalment],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.wtblSRTTOMedication
insert into tblSRPatientContactDetails([ContactNumber],[ContactType],[DateEvent],[DateEventRecorded],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [ContactNumber],[ContactType],[DateEvent],[DateEventRecorded],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPatientContactDetails
insert into wtblSRVariableDoseCDMedication([DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationEndedBy],[IDReferralIn],[MedicationCourseLength],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SupervisedDose],[TextualEventDoneBy])
select [DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileMedicationEndedBy],[IDReferralIn],[MedicationCourseLength],[MedicationEndReason],[NameOfMedication],[RemovedData],[RowIdentifier],[SupervisedDose],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.wtblSRVariableDoseCDMedication
insert into tblSRPatientGroups([DateEvent],[DateEventRecorded],[DateRemoved],[GroupType],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileRemoved],[IDStaffRemoved],[Members],[Name],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DateEvent],[DateEventRecorded],[DateRemoved],[GroupType],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDProfileRemoved],[IDStaffRemoved],[Members],[Name],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRPatientGroups
insert into wtblSRVisit([CurrentStatus],[DateBooked],[DateEventRecorded],[DateRequested],[FollowUpDetails],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileAssigned],[IDProfileEnteredBy],[IDProfileRequested],[IDReferralIn],[RemovedData],[RowIdentifier])
select [CurrentStatus],[DateBooked],[DateEventRecorded],[DateRequested],[FollowUpDetails],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileAssigned],[IDProfileEnteredBy],[IDProfileRequested],[IDReferralIn],[RemovedData],[RowIdentifier] from [vm-dwdb].systmonedata.dbo.wtblSRVisit
insert into tblSRPatientInformation([BirthPlace],[DateBirth],[DateDeath],[DateDeRegistration],[DateRegistration],[EmailAddress],[FirstName],[Gender],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[MiddleNames],[NHSNumber],[PreferredPharmacy],[PreviousSurname],[RegistrationStatus],[RemovedData],[RowIdentifier],[SpeaksEnglish],[SSRef],[Surname],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TestPatient],[Title])
select [BirthPlace],[DateBirth],[DateDeath],[DateDeRegistration],[DateRegistration],[EmailAddress],[FirstName],[Gender],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[MiddleNames],[NHSNumber],[PreferredPharmacy],[PreviousSurname],[RegistrationStatus],[RemovedData],[RowIdentifier],[SpeaksEnglish],[SSRef],[Surname],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TestPatient],[Title] from [vm-dwdb].systmonedata.dbo.tblSRPatientInformation
insert into wtblSRWaitingList([DateAdded],[DateAppointmentEnd],[DateAppointmentStart],[DateDeletedFromWaitingList],[DateDue],[DateWaitCompleted],[IDClinician],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileClinician],[IDReferralIn],[Notes],[Priority],[RemovedData],[RestrictedToCaseload],[RotaType],[RowIdentifier],[TargetMaximumWait],[WaitingListName])
select [DateAdded],[DateAppointmentEnd],[DateAppointmentStart],[DateDeletedFromWaitingList],[DateDue],[DateWaitCompleted],[IDClinician],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileClinician],[IDReferralIn],[Notes],[Priority],[RemovedData],[RestrictedToCaseload],[RotaType],[RowIdentifier],[TargetMaximumWait],[WaitingListName] from [vm-dwdb].systmonedata.dbo.wtblSRWaitingList
insert into tblSRPatientRegistration([DateDeRegistration],[DateEventRecorded],[DateRegistration],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[PreferredPharmacy],[RegistrationStatus],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DateDeRegistration],[DateEventRecorded],[DateRegistration],[IDOrganisation],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[PreferredPharmacy],[RegistrationStatus],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRPatientRegistration
insert into SystmOneDownloadSpecification([ColumnDataType],[ColumnDescription],[ColumnLength],[ColumnName],[ColumnOrdinal],[LinkedColumn1],[LinkedColumn2],[LinkedTable],[NewDataType],[TableDescription],[TableName])
select [ColumnDataType],[ColumnDescription],[ColumnLength],[ColumnName],[ColumnOrdinal],[LinkedColumn1],[LinkedColumn2],[LinkedTable],[NewDataType],[TableDescription],[TableName] from [vm-dwdb].systmonedata.dbo.SystmOneDownloadSpecification
insert into tblSRPatientRelationship([CallCentreCallBackConsent],[CaresForPatient],[CodeRelationshipWithUser],[CommunicationFormat],[ContactMethod],[ContactOrder],[CopyCorrespondence],[DateEnded],[DateEvent],[DateEventRecorded],[FinancialRepresentative],[HasParentalResponsibility],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDPatientRelationshipWith],[IDProfileEnteredBy],[IDRelationshipWithPatient],[InterpreterRequired],[KeyHolder],[NextOfKin],[PersonalGuardianOrProxy],[PrincipalCarerForPatient],[RelationshipType],[RelationshipWithCounty],[RelationshipWithDateOfBirth],[RelationshipWithEmailAddress],[RelationshipWithFax],[RelationshipWithHouseName],[RelationshipWithHouseNumber],[RelationshipWithLocality],[RelationshipWithMobileTelephone],[RelationshipWithName],[RelationshipWithOrganisation],[RelationshipWithPostCode],[RelationshipWithPostTown],[RelationshipWithRoad],[RelationshipWithSex],[RelationshipWithSpokenLanguage],[RelationshipWithTelephone],[RelationshipWithWorkTelephone],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [CallCentreCallBackConsent],[CaresForPatient],[CodeRelationshipWithUser],[CommunicationFormat],[ContactMethod],[ContactOrder],[CopyCorrespondence],[DateEnded],[DateEvent],[DateEventRecorded],[FinancialRepresentative],[HasParentalResponsibility],[IDDoneBy],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDPatientRelationshipWith],[IDProfileEnteredBy],[IDRelationshipWithPatient],[InterpreterRequired],[KeyHolder],[NextOfKin],[PersonalGuardianOrProxy],[PrincipalCarerForPatient],[RelationshipType],[RelationshipWithCounty],[RelationshipWithDateOfBirth],[RelationshipWithEmailAddress],[RelationshipWithFax],[RelationshipWithHouseName],[RelationshipWithHouseNumber],[RelationshipWithLocality],[RelationshipWithMobileTelephone],[RelationshipWithName],[RelationshipWithOrganisation],[RelationshipWithPostCode],[RelationshipWithPostTown],[RelationshipWithRoad],[RelationshipWithSex],[RelationshipWithSpokenLanguage],[RelationshipWithTelephone],[RelationshipWithWorkTelephone],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPatientRelationship
insert into tblSRPrimaryCareMedication([DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDReferralIn],[IsDentalMedication],[IsHospitalMedication],[IsOtherMedication],[IsRepeatMedication],[MedicationDosage],[MedicationQuantity],[NameOfMedication],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy])
select [DateEvent],[DateEventRecorded],[DateMedicationEnd],[DateMedicationStart],[IDDoneBy],[IDMultiLexDMD],[IDMultiLexPack],[IDMultiLexProduct],[IDOrganisation],[IDOrganisationDoneAt],[IDOrganisationVisibleTo],[IDPatient],[IDProfileEnteredBy],[IDReferralIn],[IsDentalMedication],[IsHospitalMedication],[IsOtherMedication],[IsRepeatMedication],[MedicationDosage],[MedicationQuantity],[NameOfMedication],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash],[TextualEventDoneBy] from [vm-dwdb].systmonedata.dbo.tblSRPrimaryCareMedication
insert into tblSRQuestionnaire([DtPublished],[IDOrganisation],[IDOrganisationVisibleTo],[IDProfilePublishedBy],[QuestionnaireName],[QuestionnaireVersion],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash])
select [DtPublished],[IDOrganisation],[IDOrganisationVisibleTo],[IDProfilePublishedBy],[QuestionnaireName],[QuestionnaireVersion],[RemovedData],[RowIdentifier],[SYSDateLastUpdated],[SYSDateLoaded],[SYSFileName],[SYSID],[SYSMostRecent],[SYSPrimaryKey],[SYSPrimaryKeyHash] from [vm-dwdb].systmonedata.dbo.tblSRQuestionnaire
There's a lot more tables than that but you get the idea.
Running the code gives me a message :-
"Msg 544, Level 16, State 1, Line 12
Cannot insert explicit value for identity column in table 'tblSRPatientContactDetails' when IDENTITY_INSERT is set to OFF."
I suppose I could take out the relevant column and let the system generate new ID's (won't cause me any issues to do that) unless anyone has any other ideas?
First, IDENTITY_INSERT can only be used on one table at a time.
Second, it must be in the same scope as your inserts.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply