How Do I Move Data And Preserve Foreign Key Relationships
Feb 21, 2005
How do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.
I can do this using procedural programming but that is usually not the way to go.
(btw, this is a cleaned up version of a question I tacked on to another thread)
Hey guys this is a SQL Server 2005, .net 2.0 question. I got 3 tables: Job, Times, Employee. The times table is related to Job and Employee by foreign keys. The Times table is pretty much a table where the employee's time logs are kept for a specific job. Since there was a M:M relationship between Job - Employee, I decided to create a Times table in between, and the primary key of this table should actually be the PK of Job and the PK Employee, but I couldn't figure out how to make a PK consisting of two fields. Anyhow, my main question is: How to assign an Employee to a specific job in .net? In other words, what's the best way of retrieving the Job_id and Emp_id and sticking them in the Times table? I was doing a form with a drop down for Emp_id and Job_id, but I don't know how to do the inserts... I'm confused and would appreciate any help.THANKS!
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
I working a tutorial where I will learn a lot of new stuff, LINQ being one of them. As a part of this tutorial, there are four tables and foreign keys. My db skills were limited to simple tables and i've nevr done anything where I related tables before. I'm a bit confused about foreign keys and how to set them up. I think I'm setting up right, but I have a doubt. I have a couple of screen caps to show you what where my doubt comes from at: http://www.noelakins.com/keys.htmlWhat is causing my confusion is that the connecting lines between the Albums and Events table diagrams in my setup seems to point back to the Photos table, and the Photos table seems to point to the Tags table. From the tutorial, the connecting lines seem to be pointing the other direction. Am I setting up the key right, or do I have them backwards? I'm setting out to learn a lot of stuff in this tutorial, so when I mess it up, i would like to know that it isn't because the foreign keys are wrong.EDIT:After doing some googling and some reading and watching, I'm a little less confused about how to set up foreign keys, at least how to do it in VWD. My problem now seems to be understanding the relationships. In the tutorial example, the Tags table has a FK on the PhotoID field, which matches the primary key (PhotoID) in the Photos table. Now, does this mean that the Tags table is the parent table and the Photos table the child? If this is true, then the other two tables must be child tables of the Photos table. At least that how I think this should work. The issue that I'm having with this is trying to understand how a cascade delete would work. Is the foreign key relationship a one way or both way relationship? For example, if I have made a foreign key on the AlbumID field of the Photos table, where the primary key table is Albums, PK is AlbumID and the foreign key table is Photos, then, if I do a cascade delete of a particular photo, does it delete the album too? That wouldn't make sense. Foreign keys and cascade deletes must be one way, right? Thanks
I'm using SQL Server 2005 Management Studio. I've created several Foreign Key Relationships using Database Diagrams. Now I'm trying to edit the constraints on the relationships, but when I right click on the relationship, the only option available is "Delete Relationships from Database". But I am not able to edit the relationship. Does anyone know how to do this?
I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work. How can I update the publication code? Thanks
GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries] GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code]) REFERENCES [dbo].[Publications] ([Country_id], [Publication_code]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]
GO ALTER TABLE [dbo].[Publications] WITH CHECK ADD CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I cannot find a good way to view existing Foreigh Key Relationships in SQL Server Management Studio Express. In the Object Explorer, if you click on a table to expand it, you can see a tree view of EVERYTHING about the table (Columns, Keys, Contraints, Triggers, Indexes, Statistics), but NOTHING about Foreign Key Relationships!!
The only way I have found is that you have to open the table in Design mode, then right click, and choose Relationships, and only then can you see what FK Relationships are defined.
You should be able to see the FK Rletaionships in the tree view of the Object Explorer window!
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID]) REFERENCES [Category] ([CategoryID]) ON UPDATE CASCADE ON DELETE CASCADE
I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?
Thanks
Code Snippet
- Unable to create relationship 'FK_SAMPLE_GRID'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
I'm new here. Could anyone explain to me about alternative way to backup the databases excluding the Backup Data process. I find a way by export and import data from DTS. But somehow it doen't copy the relationships and the keys. Could anyone clarify this. Thanks
Hi, I'm a noob who sucks at programming, and sucks even more when it comes to database.
I'm operating a small website with an SQL Server database that drives my shopping cart. I want to close this website down, but I don't want to lose all the data in the database in case I want to do something in the future.
How do you backup the entire database into a file so i might be able to import it at later times? Do I have to do this with SQL, T-SQL, or the manager?
Hey there - I have a textbox (C# 1.1 OR 2.0) that I want to take it's contents and post to a datatype of some sort in SQL2k5 and have it preserve the breaks (return keystrokes) so when the data is presented, it can hold a paragraph form. Is this possible?something like this: <asp:TextBox id="txtContent" runat="server" TextMode="MultiLine" Rows="10"></asp:TextBox>Right now, I'm using nvarchar(4000) as the datatype but if I were to type, say 3 paragraphs, the data returned in the datagrid I bind that data to shows only one long string of data. For the time being, I have been puttting HTML formatting tags in the content I'm posting to the DB, but ultimately don't want to have to do that. I don't want to have to use a Text Editor (like FTB or FCKeditor) if necessary because they seem very much involved for the simple need I have which is to preserve paragraph formatting.So, is there another datatype in SQL2k5 that I should be using? Or are there simple code snippets I can utilize that will recognize a carriage return as a </p><p> tag?I'm trying to migrate to 2.0 as much as possible, so any C# code would be greatly appreciated in 2.0!Thanks for your help!iSheahan
Hi,I restored a database 2 months ago. After the restore, I lost all my"users" for that database. It has been a while, so I don't rememberthe detail of what I did.How can I reserve all the users when restoring a database from a file?Did I do something wrong?Thank you in advance,Eddy
Not exactly a TSQL question. Query Editor in Management Studio keeps reformatting my sql code every time I hit save, making the code much harder to read. Is there a way to preserve line breaks and indentation in the SQL when creating a view? Thanks.
I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups. While printing the report, data print with blank space and move to continue data to next page.
Departure flight : 70 rows First Page : 42 rows printed Second Page : 23 rows printed [ Supposed to be print 28 , if the total count of records more than 23 and less than 42 then the page print only 23 records ] Third Page : 5 rows printed
Departure flight : 42 rows First Page : 42 rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]
Departure flight : 26 rows First Page : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ] Second Page : 3 rows printed
The ERP manufacturer used an image data type to store large text data fields. I am trying to move these data types from one database to another database using either Sql Queries or MS Access. I can cast them as an 8000 char varchar to read them directly but have no luck importing into these image data fields.
Access and Crystal are not able to read these fields directly.
Any suggestions? Most information about these fields has to do with loading files but I am just moving data.
I have a default installation of SQLServer2000 on a host runningWin2003. I want to install SQLServer2005 on the same machine, butpreserve the original SQLServer2000 installation. How do I do this --install into new directory? new directory and named instance? justnew named instance? What stumbling blocks could I run into?
I am looking to preserve the ROWGUID column that is created automatically when a table is added to a publication as I have noticed that the column is removed and readded (with new values) each time the publication is deleted and recreated. We are planning on using the values in the rowguid column for a non-replication purpose, and don't wish the column to be removed or the values to be reset in the event the 'owning' publication is dropped for whatever reason. I've done some research and have identified the preserve_rowguidcol column of the sysmergearticles table as a candidate for achieving the desired goal. Before moving forward, I wanted to ask for input regarding this. Are there any best practices I've overlooked on this topic? Neither Google nor this forum's search results yield any real discussion on this topic.
declare @xmldoc as xml select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>' select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
I spent huge amount of time figuring out how to preserve lading and trailing white spaces on report display without success. Can anyone help me here?
My problem is I have data with leading and or trailing white spaces and I need to show it as is. In designer preview it shows correct values. As soon as report is published and accessed on web, it truncates the whitespaces . I had a look at source, it shows values are correctly fetched(with spaces) but are ignored while rendering. I also tried replacing blank space with , however it reads this as &nbsp;.
I am using asp.net 2.0 and SQL serer 2005 reporting services.
I have the following code tha imports the contents of a text file into a varchar(max) field.
Unfortunately the CR/LF are stripped out when I look at the field.
How can I preserve them?
Code Snippet DECLARE @obj VARCHAR(MAX) SELECT @obj=BulkColumn FROM OPENROWSET(BULK 'C:qsiObjectCreation.sql',SINGLE_CLOB) AS ExternalFile insert into scriptor (script) values (@obj)
Next month client is moving servers to new location. The servers will get new ip and subnet Our user db's which are on a SAN will be moving to new SAN. My plan is the following, please correct any mistakes.
1. Do full backups of everything 2. Detach user db's 3. Copy these files to usb box (100 gig worth) 4. After server is in new world bring it up 5. Copy db files from usb 6. Attach db files
I'm a C# developer, not a DBA, but fairly familiar with sql server 2000 and learning 2005. I have a need to update a table on one sql server from another remote sql 2005 server. Both will be SQL 2005, but will not be on the same network, so there will be firewall issues to contend with. Security is not a huge concern as the data is public info and not personal secure information or anything like that. What I'll have is a table of data in one sql database. I will have a copy of that data on another sql server that is used at an ISP and drives a web site. I need the table at the ISP sql server to get updated from the SQL server that is onsite in our office on our private network. I would like to not have to get network admins to open sql server ports if possible, so I guess I'm wondering if there is an easy way with SQL 2005 to update data from one sql server to another in the environment that I have described that maybe could expose the data over a common port (ie port 80). I don't want to have to use integrated security as I'm not sure this would work between remote databases in this scenario. I don't know if the web service end point support in sql 2005 would allow this. The examples I have seen online show a sql endpoint created and then accessed from a .NET application. I can do that if I have to, but I would rather do all of this inside the database in some fashion. I am open to any idea that would work and if I need to, I can ditch the idea of using something over port 80 and can get the necessary ports open between the sql servers. This is a pretty simple scenario that we have with one table of data needing to be updated weekly on a web based sql server from a remote sql server.
Any ideas on how you would approach this would be greatly appreciated. Security for the data I'm accessing is not a huge concern as the data is public domain as I've said, but I need to make sure the rest of the server and database on both ends are secured.
I have tables and a function as representated by the code below. The names for objects here are just for representation and not the actual names of objects. Table RDTEST may have one or multiple values for RD for each PID. So the function GIVERD will return one or multiple values of RD for each value of PID passed to it.
When I run the following query, I get the required result except the rows for CID 500 for which PID is NULL in table T1. I want the rows for CID 500 as well with PID values as NULL.
SELECT A.CID, A.ANI, A.PID, B.RD FROM T1 AS A CROSS APPLY GIVERD(A.PID) B
hi, I currently uses SQL Server 2005.I need to move the data to my website, how do I do that? I have the SQL Server Management Studio Express but I couldn't not get it to export the data in .sql file (which is needed to import to my website's SQL database).Please let me know of any tools I need to perform the task. thanks.
Currently we update database A everyday with tables from database B. Database is India and has poor connection speed. What would be the best method of moving this data. Currently it's about 100 tables and on average its about 900 mb. Any input is greatly appreciated. thanks,
Hello everybody We need to move table T1 from database A to T1 database B on same server
size of table T1 15 GB and 40000000 rows
database B just created and will act as warehouse
could it be done simply by 1.creating table T1 on db B and then 2.set db to simple recovery 3. insert into B.dbo.T1 select * from A.dbo.T1 4. create all the indexes on table T1 in db B
We are currently moving our environment. I was told that we need to copy all the stuff over. More specifically, we want to move everything except the data, as our data is dynamic and will fill up in a few days by itself.
What is the best way to move everything over from one server instance to another?
My current approach is the following:
1. Create the file groups we have on our current server on the new server 2. Script out all databases with stored procedures, functions, views, priviliges, indexes ... 3. Script out all the jobs 4. Script out all the dts packages (or rather save each in a file) 5. Load all scripts into the new sql server 6. Re-create user accounts (can these be scripted out also and then loaded?)
Am I missing something or is there a wiser alternative?
