I Just checking a newly deisgned db at my new place, AND see there is NO FK contraints EXISTS FOR many tables, so you can enter:
INSERT INTO tProdValues (Value, CategoryID) rows with any CategoryID (eg. = 99),
even they don't exist in tCategoryTypes, is this OK?
Why do we need a junction table in a many to many relationship? Why can't everything be in just 2 tables?
View 9 Replies View RelatedI have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
[Code] ...
how to achieve this.l tried with temp tables it doesn't work.
How can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
I have four tables with relationships as shown. They have a circular relationship and so one of the relationships is forced to be inactive.
Operation (Commodity, OperationKey) ==========>
Commodity (Commodity)
/
/
|
|
Advice (OperationKey) ====== (inactive)=======>
Operation Commmodity (Commodity, OperationKey)
I have the following measure:
Advice No. Commodity:=CALCULATE (
COUNTROWS ( 'Advice' ),
USERELATIONSHIP(Advice[OperationKey],'Operation Commodity'[OperationKey]),
operation
)
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
I have a database DB1 and DBTest
DBTest Database is empty. I want to copy all the objects in DB1 - schema1 how can I do that?
I am looking for some SQL Scripts/tool to compare the two sql database and generate the difference results into Excel file. I am not looking for Sync/change scripts.
Example:
Result Type Desc
-----------------------
Col1 Column Added
Table1 Table Removed
Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
I refresh QA environments with copies of our production database quite often. Many of the users also have read-only logins to production, but not all. I've noticed that in QA the users in the restored database are matched up with QA server logins that no longer have "DBO" as their default schema. We almost always use DBO, nothing else.
On the most recent restore, I didn't drop the target DB first, just restored with "replace." Does it matter whether I drop or replace in this instance?
The one user reporting issues could not open the database in management studio to view objects/tables etc. I noticed their default schema was their domain login, so fixed that and they now no longer have the issue.
I am trying to do the above using the following...
ALTER SCHEMA [dbo] TRANSFER [schemaame].[tablename];
...but getting the following error...
Msg 15530, Level 16, State 1, Line 5
The object with name "tablename" already exists.
The tablename I see in SSMS is schemaame.Tablename
When I look at the properties of the table, it says the name is 'tablename'.
I cant work out how I can change the schema of the table. Also, surely having a in a schema name is not recommended, right?
So we have our HA group servers and databases, now we want to deploy schema changes to the HA group databases.
1.) Can we deploy the changes to Server2.mydatabase whilst still having Server1.mydatabase available to users?
2.) If yes, what is involved in doing so
3.) If no, What is the best suggestions to apply schema changes to HA databases.
In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies View RelatedI need to copy all objects (views, procedures, tables, functions, etc -- no data) that are under a specific Schema in a database, but the Generate Script option in SSMS doesn't give me the option to do this. How I can generate a script that's Schema specific?
View 2 Replies View RelatedQuery to find the date/time when a database schema was created and who created it.
View 2 Replies View RelatedI have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
View 2 Replies View RelatedI'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedHello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies View RelatedI am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
How can I set a one-to-one relationship using the Management Studio Express and SQL Server 2005 Express
tblClient, CleintID (PK)
tblProcess, ClientID (FK)
Hello
I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result....it's just ALL of the criteria must be matched and a row returned:
example:
Transaction table: id, reference
attribute table: attributeid, attribute
transactionAttribute: attributeid, transactionid
Example dat
Attribute table contains: 1 Red, 2 Blue, 3 Green
Transaction table contains: 1 one, 2 two, 3 three
transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)
If I pass in Red, Blue, Green - I need to be returned "one" only
If I pass in Red - I need to be returned "three" only
If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria
If anyone's able to help that would be wonderful!
Thanks, Paul
How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)
When I try to set a relationshop between gamegenre and game I'm getting this error:
'gf_game' table saved successfully'gf_gamegenre' table- Unable to create relationship 'FK_gf_gamegenre_gf_game'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'.
Thanks for any help!
Can anyone provided insight on how to create a one-to-one relationship between
SQL tables? Every time I try to link two tables that should be one-to-one, the link says one-to-many.
How can I specify one-to-one when SQL Server automatically thinks it is a one-to-many?
Thanks,
Kellie
hi all,
how to implement the many to many relation ship....
i get stucked it.......
plz help
thanx
sajjad
Hey,
I know I'm asking a stupid question but I need to get a clear response please:
why using One-to-One relationship instead of meging the 2 tables in only one?
thanks.
Hi,
do you use ISA relationship or do you avoid it? Why?
Could you write me some its benefitsand drowback?
Thanks
Hey,
is it possible to get the relationship of a table via sql? Like I need to know which relations the table t has. Is that possible?
Thanks
I created 2 tables with one to one relationship. if I add a record intable A, how does table B record get created? Does SQL do thisautomatically because it is one to one relationship? or do I need tocreate a trigger? if i need a trigger, how do I get the ID of newrecord to create the same ID in table B?thanks for any help.Joe Klein
View 7 Replies View Related