I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.
I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....
thanks
David
Hello Everyone,
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 trying to find the most efficient method of importing XML files into SQL Server using SSIS. The objective is to read these files created in a source directory and potentially there will be a large number of these created every second.
I've experimented with the XML Source data flow task and been able to import using either the OLEDB Destination or SQL Server Destination.Using the associated XSD file
These methods work well when there are no PK or FK relationships, i.e. the target tables are stand alone.
However when I use a schema that has relationships I need to be able to import and be able to return the key then use this key to import into another table to ensure the FK relationship and so on.....
From what I know I will need to create multple Data Flow tasks when reading a single xml file then in turn create a 'look up' on a table to see if this entity exists and on error insert using the OLEDB command and then somehow return the PK generated???
Has anyone tried this? And would be happy to share there experiences?
It seems a lot easier to use an SP and then use an execute SQL task method
Your thoughts would be greatly appreciated
Thanks
John
hello,
i am wondering, for what i use relations between tables? if i make sure that the data inserted into the table matches, for what i need table relationships?
i have a table named Pictures with: PictureID, UserID, UserName, PictureName, Description...
do i have to make relationships between UserID from Pictures and UserId from aspnet_Users?
and the second table PicturesComments witch has: CommentID, PictureID, UserID, UserName...
do i have to makre relationship between PictureID from PictureComments and PictureID from Pictures AND between UserID from Pictures and UserId from aspnet_Users?
sorry for this stupid questions...
thanks
I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.
The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.
Currently my tables can be summarized like this.
Pre-Advice Table
Barcode
Lots of other info…
Scanners Table
Barcode
Date
Time
Weight
Which device input this information
Suppliers Weight Table
Barcode
Date
Time
Weight
Here is the process(es):
1.Pre-advice table populated.
2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.
3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.
At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.
Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.
Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.
I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.
how I will need to set up my tables/relationships in order to achieve my requirements?
There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID
View 4 Replies View RelatedHi, I have two tables. One is called ContentRecord and it is one field, an identity field that is a primary key. I have another table that is has several fields including a field that is a foreign key called contentFK. I have set up a foreign key relationship between the tables. There is a one to many relationship. I would like to know the best way to add a record to both tables. The way I am thinking to do it is to do the insert statement to add the record to the ContentRecord which will return the value of the primary key field. Then do a seperate insert statement to insert the record into the second table where I set the contentFK equal to the returned value from the first statement. Does that make sense? Is there a better way to do this?
Thanks,
Laura
Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.
View 1 Replies View RelatedCan anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than
having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!)
Thanks.
Neil.
Can Anybody explain me this how to Do this ?
Is this done by definning the Primary key and foreign key constraints ?
Hello Friends,
I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules.
I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.
Guys,
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
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
IS there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY, studentId varchar(50) NOT NULL, course varchar(50) )
CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY, course varchar(50) NOT NULL, sco varchar(50) )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath
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 Relatedtrying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
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...I have 4 connected table in sql, and i have view that query this 4 and create 1 table, can i export that to csv file?thank you...
View 2 Replies View RelatedHi all,
I work on a data warehouse. My source system is moving from Oracle to Sql Server. I'm looking for a way to export multiple tables into distinct files. I've started using DTS 'Export Data' function, but that is one table at a time. I'd like to put multiple input/outputs into a package so that I can run one package to take care of a bunch of small tables while I run my larger tables simulataneously. Right now, it seems that I'll need to make one DTS package per table. Any suggestions how I might be able to avoid that? Thanks.
I am really new to SQL Server so be patient i guess. I have installed sql server 2005 on my home computer but at first i wasnt able to connect to the server at school where i had started creating tables already. Instead of going up to the school i opted to do it from home and start over. Well I am wondering if there is a way to copy all the tables that i have created on my home computer and just import them into the database on the server at school. Hope this isnt confusing. Anyways, this forum always has helped me in the past, so i am confident someone will be able to point me in the right direction. Thanks in advance.
View 3 Replies View RelatedA very simple question
I have two tables in the database on one server
I want to export it to another database on another server
How can I do this?
I tried DTS but I guess it works from one server to another server on same network
When I went to Query Analyzer, I can see scripting options but that only gives me the structure of the table in .sql format
Is it possible to get the whole data in .sql format and then run it on another server
Please suggest methods
Thanks
Hello everybody,
I am new to this forum and I dont know much about MS SQL Server and SQL Language at all. Anyway, I just wrote a SQL Script (Query) that pulls one sql table from a large database. Because I need this script for many similar cases (only one parameter changes), I will write a java program which produces the specific sql code with respect to the particular situation. Thus my Query will produce many tables. How can automate to export each of them in an excel? It can not be the case that I have to build a excel file for each sql-table manually. The SQL Script should produce the excel file automaticly.
My question for short:
"select * from final"
how can i export the resulting table to an excel file (which should be built by ms sql server in exactly this moment)?
Thanks a lot
Temperature
Hi,I've used SQL Enterprise Manager to Export my selected db's locally.My main question is how do I move the export to another pc - whichfiles need to be moved, etc?ThanksLouis
View 3 Replies View Relatedhi, i installed sql server 2005 express on my tests server, i need to move a database from my sql server 2000 to test how it works with 2005.
i understood that the export/import option is not available in express version. but i still need to pass my database, other wise i cant check the power of the 2005 version.
please tell me about other way to do!
Hi ,
I need to export data from sql data base tables to .csv files with vertical bar (column seperator).The names of the .csv file is to be similar to table name. Eg. exporting Employee table should result in Employee.txt / Employee.csv
Using export / import wizard I could export only 1 table at a time to text file.
The content in the file will be as shown below:
EMP |ALL |103000353|102000001|104000000|n
EQUP|ALL |104000117|104000001|105000000|n
As there are huge number of tables and I need to transfer all the user tables to text files seperately, Is there any method / sql query / script to export multiple tables at a time , such that with a single execution i could export 100 tables to 100 text files individually.
Looking forward for solution
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
Hi guys,
I am looking for a way to export the TABLES only from my database to another database on a nightly basis (we already have scripts that backup the entire db).
The 'Import/Export' wizard in Management Studio works great but there's no automate/schedule option.
Thanks for your help
MelloI am not sure if this is eaven possible but I need a script to quicklyexport selected tables from a SQL database into Access.The script should preferably run from an ASP page on demand or perhapsbe triggered by an update of one of the selected tables.Any help will be greatly appreciated.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View Related
How would I be able to export all my sql server tables back to MS Access?
R
Hi,
I'm trying to use enterprise manager (v 8.0) to generate a sql script. I know I can select the tables I want to script and then select "Generate SQL Script".
My problem is that I can't seem to figure out a way to get the INSERT statements for all the data in the tables into that same script. I can get the table CREATES, ALTERS, etc.
Is there a way to include the INSERTs for data as well???
Hi
Having small query
How can i able to Export Data from Tables to Specific Local files using Procedures?
Environment Details
OS : Windows 2000 server
Database : MS-SQL Server
Solutions are needed asap.
Thanks in advance
cheers
vasu
How can i print or export all fields for all tables in my sql 2005 db?
View 3 Replies View RelatedHello,
I just want to know how can I create a SSIS package to export a few distinct tables into distinct HTML pages.
If anyone can help.
Thanks in advance.
Best regards...