January 26, 2012 at 3:10 pm
I've got two databases, A and the Master and I need to append new data from A into the Master database, but I'm having problems. I need to append data from table BirdInfo and BirdCensus. The primary key for BirdInfo is the ID field and BirdCensus is linked to BirdInfo with the field BirdInfo_ID. The problem is that when I append data from the A database to the Master database, the values of the primary key for BirdInfo get changed so there won't be a conflict. Unfortunately, because those values have changed, the link to the BirdCensus table is broken. How do I append the tables and make sure that I don't break the connection?
I'm kind of learning as I go and this wasn't in my original job description. I have some experience with Access and can do some basic stuff with SQL Server, but this exceeds my experience. The problem is that the other scientists here trusted the database developer, but weren't involved with how things went. So, there's no documentation for how the database manager appended the tables in the past. The only thing I know is that the database manager appended the tables using DTS in SQL Server and removed the relationship in the diagram. I got that from a QA/QC spreadsheet that he created for one sampling year. I haven't been able to contact him.
January 26, 2012 at 4:15 pm
Post some DDL (Data Definition Language) a.k.a CREATE TABLE scripts of the source and target tables please.
I have some code handy that will help. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 27, 2012 at 7:57 am
I wish I could. I'm not well versed in writing code. Like I said, I'm kind of learning on my own as I go.
January 27, 2012 at 8:08 am
Right click on the tables and select SCRIPT TABLE AS > CREATE > New Query window. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 27, 2012 at 8:14 am
Derp. Here's the table from the data entry database (CTAPA). The data from this database needs to be appended to tables with the same name in the master database (CTAPM).
Here's BirdSiteInfro:
USE [CTAPA]
GO
/****** Object: Table [dbo].[BirdsSiteInfroM] Script Date: 01/27/2012 09:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BirdsSiteInfroM](
[SiteID] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Organism] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BYear] [numeric](10, 0) NOT NULL,
[TwnshpNo] [int] NULL,
[Rank] [int] NULL,
[Site] [int] NULL,
[ID] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
[ID_Old] [numeric](18, 0) NULL,
CONSTRAINT [PK_BirdsSiteInfroM_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And here's BirdCensus:
USE [CTAPA]
GO
/****** Object: Table [dbo].[BirdsCensusSpeciesInfroM] Script Date: 01/27/2012 09:13:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[BirdsCensusSpeciesInfroM](
[Habitat] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NumOfVisit] [int] NULL,
[Organism] [char](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BMonth] [int] NULL,
[BDay] [int] NULL,
[BDate] [datetime] NULL,
[Obs] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sky] [float] NULL,
[Wind] [float] NULL,
[BTemp] [float] NULL,
[Noise] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Point] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BTime] [datetime] NULL,
[Minutes] [float] NULL,
[Species] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Num_Inds] [int] NULL,
[Sex] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Vocal] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Direction] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dist] [float] NULL,
[Rounds] [int] NULL,
[Habitat2] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Poss_Br] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Prob_Br] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Conf_Br] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataEntryDate] [datetime] NULL,
[DataEntryPerson] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteID] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BirdsSiteInfro_ID] [numeric](10, 0) NULL,
[SortID] [numeric](10, 0) NULL,
[sort_id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_BirdsCensusSpeciesInfroM] PRIMARY KEY CLUSTERED
(
[sort_id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
January 27, 2012 at 8:37 am
Does ID_old need to hold the previous ID's value after migration?
And the foreign key is ...
BirdsCensusSpeciesInfroM.BirdsSiteInfro_ID ties to BirdsSiteInfroM.ID
And I assume you'll be able to do this when there is no activity in the table, correct?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 27, 2012 at 8:46 am
As far as ID_old is concerned, that's there because I was trying a few things in Access (where we do most of the data entry) to see if there was a way that I could just run an Update query to change from the old values to the new values. I'm leaving that column there for now, just so I have a reference to make sure I'm getting things done right. As in, the right data in BirdCensus points to the right site in BirdSite. It's not an autonumber field so that shouldn't be an issue. I created that column just for this purpose and I was planning on getting rid of it once the migration was successful.
You are correct about the foreign key.
If by no activity you mean that no one will be doing anything with the database while I'm doing this, then yes, that will not be an issue. I can prevent other people from using the database while I'm doing the migration.
January 27, 2012 at 8:51 am
OK, here you go.
PLEASE ......
Read the code and try to understand it 100% before you run it.
Run in a TEST environment if you have one.
Run a full backup before hand just for safety's sake.
Comment out the ROLLBACK and uncomment the COMMIT when you're ready to do the actual migration.
** Any other dependant objects/keys will most likely be broken unless they key values are the same in both DB's
DECLARE @next_BirdsSiteInfroM_ID BIGINT
CREATE TABLE #t (old_ID INT, new_ID INT);
BEGIN TRANSACTION
---- get the next ID from the target
SELECT @next_BirdsSiteInfroM_ID = MAX(ID) FROM CTAPM.dbo.BirdsSiteInfroM
---- verify/reset the identity seed values to the maximum value in the tables
DBCC CHECKIDENT ("CTAPM.dbo.BirdsSiteInfroM", RESEED, @next_BirdsSiteInfroM_ID);
---- we push the ID's into the temp table and apply the row numbering + new ID value
INSERT #t
SELECT
[ID] AS old_ID
,ROW_NUMBER() OVER (ORDER BY [ID]) + @next_BirdsSiteInfroM_ID AS new_ID
FROM
CTAPA.dbo.BirdsSiteInfroM
---- move the "children" rows
INSERT CTAPM.dbo.BirdsSiteInfroM
([SiteID]
,[Organism]
,[BYear]
,[TwnshpNo]
,[Rank]
,[Site]
-- ,[ID] doesn't need inserted as it's an IDENTITY
,[ID_Old])
SELECT
[SiteID]
,[Organism]
,[BYear]
,[TwnshpNo]
,[Rank]
,[Site]
,t.old_ID --- we're keeping the old ID
FROM
CTAPA.dbo.BirdsSiteInfroM AS m
INNER JOIN #t AS t
ON t.old_ID = m.[ID]
INSERT CTAPM.dbo.BirdsCensusSpeciesInfroM
([Habitat]
,[NumOfVisit]
,[Organism]
,[BMonth]
,[BDay]
,[BDate]
,[Obs]
,[Sky]
,[Wind]
,[BTemp]
,[Noise]
,[Type]
,[Point]
,[BTime]
,[Minutes]
,[Species]
,[Num_Inds]
,[Sex]
,[Vocal]
,[Direction]
,[Dist]
,[Rounds]
,[Habitat2]
,[Poss_Br]
,[Prob_Br]
,[Conf_Br]
,[DataEntryDate]
,[DataEntryPerson]
,[SiteID]
,[BirdsSiteInfro_ID]
,[SortID])
SELECT
[Habitat]
,[NumOfVisit]
,[Organism]
,[BMonth]
,[BDay]
,[BDate]
,[Obs]
,[Sky]
,[Wind]
,[BTemp]
,[Noise]
,[Type]
,[Point]
,[BTime]
,[Minutes]
,[Species]
,[Num_Inds]
,[Sex]
,[Vocal]
,[Direction]
,[Dist]
,[Rounds]
,[Habitat2]
,[Poss_Br]
,[Prob_Br]
,[Conf_Br]
,[DataEntryDate]
,[DataEntryPerson]
,[SiteID]
,t.new_ID
,[SortID]
FROM
CTAPA.dbo.BirdsCensusSpeciesInfroM AS m
INNER JOIN #t AS t
ON t.old_ID = m.BirdsSiteInfro_ID
ROLLBACK
--COMMIT
DROP TABLE #t
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 27, 2012 at 9:47 am
A couple of questions:
Does the code depend on the maximum value of the primary key in the Master database being larger than the maximum value in the data entry database? This is actually the case in my database, I just want to be sure that the code reflects that.
We have a separate database, called CTAPT that is called the test database. I'm guessing that the database manager created that database for this purpose. For running the code in this database, do I just change the code where it says CTAPM to CTAPT (the table and variable names in our databases are all the same)?
And a completely dumb question, that shows how much I really know about running code in SQL Server. Where do I run the code?
January 27, 2012 at 9:54 am
ryetimothy (1/27/2012)
A couple of questions:Does the code depend on the maximum value of the primary key in the Master database being larger than the maximum value in the data entry database? This is actually the case in my database, I just want to be sure that the code reflects that.
No, this code will handle any situation (source I > target ID and vice versa)
We have a separate database, called CTAPT that is called the test database. I'm guessing that the database manager created that database for this purpose. For running the code in this database, do I just change the code where it says CTAPM to CTAPT (the table and variable names in our databases are all the same)?
Yep, that should do the trick.
And a completely dumb question, that shows how much I really know about running code in SQL Server. Where do I run the code?
WOAH!, this is really scary. You'd run it in SQL Server Management Studio, but if you're that inexperienced, I'd suggest getting some help, whether its a contractor for a few days/weeks, but you're asking for problems otherwise. 🙂
*** FYI, going offline for most of the day today.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 27, 2012 at 10:01 am
Sorry, I should have been more specific. I know that it needs to be run in SQL Server Management Studio, I just need to know where to insert the code. Do I just create a new stored procedure?
January 27, 2012 at 8:09 pm
Are both databases on the same server?
Yes: Right click on one of the DB's in SSMS > New Query
- Test
- Test
- Test
- Run with COMMIT
No: Need to add servername in each from clause before the db name [server1].[ABCD].[dbo].
But I still must say, these forums are free. If anything goes wrong, you're ultimately going to have to fix it. I'm in no way trying to say you can't nor shouldn't attempt this, just be aware of the things you're not aware of. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 30, 2012 at 7:54 am
Both databases are indeed on the same server, so it looks like that won't complicate things. Thanks for the help. I've got a brother-in-law IT guy so he might be able to help out with some of the finer points and making sure I don't completely screw things up. In the past I've just done the appending piecemeal with Access, but I have this headache at the end of every year, so I decided it was time to make things easier for myself. I wish the old database manager had left more documentation, but considering that he didn't even change the default names for controls in the Access database side of things, that would probably be asking too much.
I usually tell people that I know enough to get myself into trouble, just not necessarily enough to get myself out of trouble. It's the darn unknown unknowns that always scare me.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply