Hi,
i am using the enterprise manager of our sql server 2000. i want to
export some (not all) tables from one DB to another one with all the
data inside these tables AND the primary keys. if i do i only receive
the tables with the data but all primary keys and indexes will be lost.
how to do it?
thanks, tobi
Can someone tell me if there's a way to export data from one database to another without losing the primary key information that has been set in the source database?
Please respond to URL. Thanks!
CC
How can I Export Database with foreing Key and primary key.
Operation is that
SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
Best Regards,
Athena.
Hi,When I copy tables in a database from one server to another usingenterprise manager, everything copies ok, except for field defaults.Has anyone seen this, and what is the solution?--http://www.dbForumz.com/ This article was posted by author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbForumz.com/General-Dis...pict174830.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973
View 5 Replies View RelatedI have a user that says that after she installed the ActiveX control for printing, that she can no longer export the reports. Has this happened to anyone else? What settings do I need to check on her machine to fix this problem?
View 2 Replies View RelatedHi everyone, I am trying to upgrade a 2000 DB to 2005 with SSIS Database Transfer.
I finally got it to work with an online transfer but the destination database has no primary keys, indexes or identity columns from the source DB.
What am I missing?
What other ways are there to upgrade a SS2000 DB to 2005?
Thank you.
I appologise if this is dumb to anyone but I need a lot of help. I had a large program with over 50 tables and over 100 queries written in access. I upsized to SQL into a server windows 2003. Everything was cool and I made all corrections such that the program was working fine. The program resided in a D: accessory drive which was going bad. I copied my program to a different drive and replaced the D drive and returned my program to the new drive. I amde sure all shares and securities were the same. Lo and Behold My program now is missing all the queries and tables and when I run it it says it is disconnected. What happened to my tables I am currentely sobbing right now.
View 3 Replies View RelatedI have a SQL Server database that I connect a front end to using an ODBC connection. Our LAN folks upgraded the server recently and now I can no longer see any of the tables through the ODBC connection that the user used for login has permission in SQL Server Enterprise Manager to see - throught the ODBC connection the user can only see things like:
dbo.spt_datatype_info
dbo.spt_datatype_info_ext
dbo.spt_fallback_db
dbo.spt_fallback_dev
dbo.spt_fallback_usg
.
.
.
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
etc.
I've tried deleting the user for the connection and re-establishing it with owner permissions. I've tried deleting the dsn and re-establishing that as well but nothing so far.
Please help!!!
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I would appreciate any suggestions.
Thank you.
LitePipe
When I export a table from my local SQL Server to my web-host's SQLServer, the primary key never seems to export. This happens whether Iuse the "Copy tables and views..." option or the "Copy objects anddata..." option. Anybody know why this happens?
View 3 Replies View RelatedCurrently, I'm exporting a database from production to local (test)machine. I've done this several times without problem, but during thelast few days, the primary keys have failed to export. Would anyoneknow what options might keep the keys from exporting?Thanks,Louis
View 3 Replies View RelatedI have two practice tables I have created and want to export the values of one into the source table. I want to know if I can export into a table and have the destination table automatically give a primary key value to a record? I haven't been able to figure this out even after fiddling with the "Enable identity insert" checkbox under the Column Mappings tab. I have created source tables with and without primary keys and neither works because of the fact that I need to have a value for a primary key in order to INSERT into the destination.
Do I have to copy the source records into a staging table and assign the PK values myself by hand? This can't be the answer.
ddave
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View 3 Replies View RelatedI have been given a project to complete where ... two tables "Flights" and "ScheduledFlights" have a column called "FlightNo" . In both these tables it is mentioned that "FlightNo" is primary key ?
Is this possible ? i am not talking about foreign key here....
Here is the script that i have created..
create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)
create table ScheduledFlights
(
FlightNo char(5) constraint FlightNo2 primary key clustered not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)
Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)
Hi,
Suppose i have two tables employee and orders.Can i join these two tables with candidate key colums in each table.
I have inherited a SQL Server Database that has three tables related to one another. The DB uses a Access 2013 front end. I know access fairly well, but am not all that competent when it comes to SQL Server. I am using SQL Server Management Studio 12. I need to change the primary key in one of the tables. When I do so, I get the message illustrated below. How can I get around this?
I did find the place where there is a table option of "Prevent Saving changes that require table re-creation". However, there seems to be a lot of comments that indicate it should not be turned off. If I don't turn it off, how can I
make the changes to the table?
With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId),
studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId),
CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
)
CREATE TABLE dbo.objScores ( objID varchar(20) NOT NULL CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID) REFERENCES objStructure (objID),
studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId) REFERENCES students (studentId),
courseId varchar(20) NOT NULL CONSTRAINT FK_course FOREIGN KEY (courseId) REFERENCES courseScores (courseId), CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId) )
Thanks all,Zath
Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.
Thanks,
Rick
I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.
I would appreciate if someone could help me out with this.
I found a few 'SearchAllTables' scripts out there to find a value in any table/column/row of any Database.
My problem is that none of these database search queries seem to look at primary key values.
The value I'm looking for is a primary key - I need to find all the other tables that have this primary key value.
how I could accomplish this?
Hello,We imported a bunch of tables from a database and realized that theprimary keys weren't copied to the destination db. In order to re-create the keys, we need to know which tables have them. Is there acommand that I can use (on the source db) to find out which tablescontain primary keys? The db has hundreds of tables and I'd rather notgo through each one to see which has a primary key.Also, for future reference, is there a way to include the primary keyon an import?Thanks,Peps
View 2 Replies View RelatedI've created a table called Employees with a primary key called EmployeeID. Â The table contains EmployeeID, FirstName and LastName columns. Â I now want to create a table called Team which will contain the columns TeamID, EmployeeID (to reference the column EmployeeID from the Employee table) and a column called TeamName. Â Sql won't let me create multiple primary keys in one table (I did think that was the case ) key but yet if I look at the Adventure Works database in the Person.PersonPhone table, I can see three primary keys defined.
View 3 Replies View RelatedI was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;
GO
SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;
This is the query that I did....
SELECT
     c.CustomerID,p.FirstName,p.MiddleName,p.LastName
FROM
     Sales.Customer AS c INNER JOIN Person.Person AS p
ON
     c.CustomerID=p.BusinessEntityID
ORDER BY
        p.BusinessEntityID;
Keys :-
Person Table
[PK_Person_BusinessEntityID]
[FK_Person_BusinessEntity_BusinessEntityID]
Customer Table
[PK_Customer_CustomerID]
[FK_Customer_Person_PersonID]
However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??
...got tired of looking at them by hand.
Cheers
-b
DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)
Select @vcDB='mydb',@vcSchema='dbo'
DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC
open cLoop
FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'
FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop
we like some of the advantages filegroups offer but wonder if the purpose is somewhat defeated if it is true that tables sitting on other filegroups still have to work with the same transaction log that the db's primary tables work with.
View 4 Replies View RelatedHow do i find all the tables that have an Identity Column as a primary key in a database. Thanks.
View 8 Replies View RelatedHope this is in the right thread, sorry if not!
I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***
This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table
***
id indid colid keyno
933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1
Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).
So, anyone know how i could solve this problem?
Regards
/Anders
Hi
I would like to export db tables to another server. I have try to use export feature but it will not exporting index triggers, constraint, etc. Is there any efficient way to export table data as well as other info?
Hi all,
In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations
Right now my code looks like this
foreach(object obj in Objects){
int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
}
The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
Thanks
Regards
Prasad.
Hello All,
Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.
Many thanks in advance for any and all help,
Gurmi
Hi all,
I’m trying to export 120 tables from SQL server 2000 to SQL server 2005 with their Primary and corresponding records.
Is there way to do this?
Thanks for any help.
Abrahim
how to find all primary key columns & foreign key columns in all database tables?
View 1 Replies View RelatedI want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View Related