Need Help Figuring Out How Many Tables To Create
Apr 17, 2006
Hi, I have a users table in my SQL Server database. Now, I am looking to create a table (or multiple tables) to allow users to post their weekly events, meetings, activities, and accomplishments to the database. Each Monday morning, each user will enter their new schedule for that week and the previous week's entry will be archived in the database. My question is: what would make more sense? Should I create one big table that would have the following columns:
- Week Number (the current week number in the year)
- User ID
- Events
- Meetings
- Activities
- Accomplishments
And each user would have one row in the database per week.
OR should I create 4 separate tables named Events, Meetings, Activities, Accomplishments. Each of these tables would have the following columns: (for instance, the Events table would contain:)
- eventID (auto incremented number)
- userID
- weekNumber
- event_description
Each time a user adds a new event to their schedule, a new row in the Events table is created. Each time a user adds a new accomplishment, a new row in the Accomplishments table is created. etc., etc.
Which approach seems to make more sense and would be easier to maintain? Also, which one would conserve database space better and result in faster querying.
Thanks, Dave
View 11 Replies
ADVERTISEMENT
Jul 23, 2005
Here's what's going on. I have a 2 computers (x & y) running SQL2000. Ibacked up a copy a DB from x and restored it on y. I have a Stored procthat runs in under 2 seconds on both x & y when running it throughAnalyzer, but when I call this stored proc running it throuhg my C#winforms app (running on computer z) it takes over 3 minutes oncomputer x and under 10 seconds on y.This stored proc does have a select clause as part of the where clause,but again it works fine on y.I've check the indexes and that looks good and I just did a restore ofthe database so they should be identical. And I don't think it's aperformance issue because the rest of the app runs actaully a bitfaster on x.The plans do have differences. Specifically with a mention ofParallelism in the fast one.Here are the plans:X (slow):|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote] ASC,[Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0 else[Expr1003]>=2))|--Nested Loops(Left Outer Join, OUTERREFERENCES:([r].[GuestId]))|--Hash Match(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))| |--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWARD)|--Hash Match(Cache, HASH:([r].[GuestId]),RESIDUAL:([r].[GuestId]=[r].[GuestId]))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1011])))|--StreamAggregate(DEFINE:([Expr1011]=Count(*)))|--IndexSpool(SEEK:([r2].[GuestId]=[r].[GuestId]))|--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))Y (Fast):|--Parallelism(Gather Streams)|--Sort(DISTINCT ORDER BY:([r].[GuestId] ASC, [g].[GuestNote]ASC, [Expr1005] ASC, [g].[Email] ASC, [g].[Phone1] ASC))|--Parallelism(Repartition Streams, PARTITIONCOLUMNS:([r].[GuestId], [g].[GuestNote], [Expr1005], [g].[Email],[g].[Phone1]))|--Compute Scalar(DEFINE:([Expr1005]=[g].[LastName]+','+[g].[FirstName]))|--Filter(WHERE:(If ([Expr1003] IS NULL) then 0else [Expr1003]>=2))|--ComputeScalar(DEFINE:([Expr1003]=Convert([Expr1013])))|--Hash Match Root(Right Outer Join,HASH:([r2].[GuestId])=([r].[GuestId]),RESIDUAL:([r2].[GuestId]=[r2].[GuestId]) AND([r2].[GuestId]=[r].[GuestId]) DEFINE:([Expr1013]=COUNT(*)))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r2].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r2]))|--Hash Match Team(Inner Join,HASH:([g].[GuestId])=([r].[GuestId]),RESIDUAL:([r].[GuestId]=[g].[GuestId]))|--Bitmap(HASH:([g].[GuestId]), DEFINE:([Bitmap1014]))||--Parallelism(Repartition Streams, PARTITION COLUMNS:([g].[GuestId]))| |--Clustered IndexScan(OBJECT:([Restaurant].[dbo].[Guest].[PK_Guest] AS [g]),WHERE:(len(isnull([g].[Email], ''))>6 AND charindex('@',isnull([g].[Email], ''), NULL)>1))|--Parallelism(RepartitionStreams, PARTITION COLUMNS:([r].[GuestId]),WHERE:(PROBE([Bitmap1014])=TRUE))|--Clustered IndexSeek(OBJECT:([Restaurant].[dbo].[Reservations].[PK_Reservations] AS[r]), SEEK:([r].[RestId]=1), WHERE:([r].[Date]<='Jan 1 2005 12:00AM'AND [r].[Date]>='Jan 1 2003 12:00AM') ORDERED FORWAny ideas of what I can check for?Thanks for any help.
View 1 Replies
View Related
Feb 1, 2002
I trying to find a way to find what the SPID of a given job that is running. I am trying to create a script that will give me the SPID the JOB_ID, and JOB_NAME. The problem comes in that if I use sysprocesses I have to pull the JOB_ID from program_name in sysprocesses and convert it into something then join it to sysjobs. Have not been sucessfull in that conversion. Any Ideas
thanks for any & all help
sean
View 1 Replies
View Related
Nov 19, 2007
I have an old backup file that I am pretty sure contains a table that I am looking for. Is there a way to verify what exactly a backup file contains without restoring it to a test server?
View 4 Replies
View Related
Oct 25, 2006
I have 3 tables, Items, Brands and Categories. Brands and Categories consists of a primary key as well as the Brand/Category name. Items has a bunch of columns as well as two foreign key fields, one for Brands and the other for Categories.I want a SELECT statement that would select each Brand(id and name) as well as the associated categories for each brand (i want this info the menu control) if this was a one-to-many relation, i wouldn't have had any problem... but since this is basically a many-to-many relation, i just can't figure it out
View 2 Replies
View Related
Feb 5, 2007
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
View 1 Replies
View Related
Aug 12, 2006
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
View 6 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Feb 5, 2000
Hi All,
I am a serious NT sql NEWBIE. I have installed v7 on my NT/MIIS. I want to allow
another party now to create tables on their SQL db they have runnning on my
server (their own domain)
What is the best way -? Do they send me the scripts or is there a way to run them
remotely and create them.
If they do send me the scripts what do I do?
Regards
Amy
View 2 Replies
View Related
May 9, 2008
hi everybody
Can anyone tell me how to crate log tables and how to insert value into log tables?
View 3 Replies
View Related
Mar 23, 2006
hello all...
i want to drop table 2Autonumber ---- table name but when i run query i am getting following error
drop table 2Autonumber
Error : Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '2'.
also know how to create such tables??
T.I.A
Papillon
View 2 Replies
View Related
Sep 12, 2007
Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
Thanks & Regrads
Jai Shankar
View 1 Replies
View Related
Jun 7, 2008
I was just looking at the AdventureWorks Database Sample and I see the Tables and the Procedures have different prefixes other than dbo. How can I create Tables that use different prefixes as I would like to organize my tables better. Thanks in advance.
View 2 Replies
View Related
Dec 21, 2005
Hello,In my web site I have 2 types of users: students and professors.All users need to login but the personal information for each user type has different fields.So I create 3 tables: professors, students and authentication,"professors" will have all the personal data from professors."students" will have all the personal data from students.authentication will have the username and password from all users.How can i link authentication table to both professors and students tables so when the user logins i get the data from that user, either a professor or student?And is the right way to do this?I am using SQL 2005 and Asp.Net 2.0Thanks,Miguel
View 1 Replies
View Related
Jun 9, 2004
I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:
View 1 Replies
View Related
Nov 30, 2004
I know this may sound stupid but I really can't figure out how to do it. I just keep going around in cricles.
I need to create six table under three different users. so I would have users1 owning tableA and tableB. Then I would have users2 owning another copy of tableA and tableB. same for users3.
But I just can't find a way of doing it, I am using enterprise manager. Please help, Ed
View 5 Replies
View Related
Apr 5, 2006
stepdefinition has
steptype
flowid
stepid
task has
taskid
flowid
stepid
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
here's a visual
http://www.filecabin.com/up1/1144249279-task.gif
View 2 Replies
View Related
May 31, 2006
I have to create a history of tables. Therefore I must know the previous structure of the table. I have to document the changes of the structures of the tables.
Is there a solution for the version 2000?
View 2 Replies
View Related
Jan 20, 2012
I'm attempting to use SQL statement to create tables within a new database, and I'm somewhat lacking in experience for this. Normally I'd simply create them through the GUI, but I want to get the hang of DDL statements.
I have written what I think are scripts to create one table with a primary key, create a unique index on it, and then create another table with a primary key and constrain two columns in the second table to the non-primary unique index in the first.
Code:
Use SKUTracking
CREATE TABLE
SKUTracking.dbo.tblOraUnits
[code]...
View 7 Replies
View Related
Jul 16, 2014
Create a Database with the following:
a) Database to contain a list of people, their addresses, email addresses, and phone numbers.
b) Database to contain a list of cars, their names, their type (2 doors,4 doors, etc.), and their colour.
c) Database to allow only one owner per car
d) Database to allow multiple people to be able to act upon behalf of one car. (Disclosure of MOT details, request change of parts, etc)
View 4 Replies
View Related
Apr 9, 2007
Hi
I have two database as Malathi,Indoo
In Malathi database i have
Employee Table AS
----------------------
Eid Int (PK)
Enam varchar(50)
In Indoo Database I have
Job table as
Eid int (FK)
Jid Int (PK)
Now how can i create relation b/w two tables of different database
Any One can help for this query
Malathi Rao
View 3 Replies
View Related
Sep 27, 2007
Hello all;
I'm so new in NHibernate and I wonder that "can nhibernate create tables into my db"?
If it can; how?
Which confugrations are needed?
Thanks...
View 1 Replies
View Related
Oct 8, 2007
Im trying to bring together two tables into one, but they're records dont always match, so im getting akward returned records. So now i want to combine the fields i need from these two tables to create a new one. How would that be coded.
I want to
SELECT
[Customer_Bill_Customer_No],
[Customer_Bill_Customer_No],
[Sales_Header_Order_DateTime],
[Sales_Header_Your_Reference]
from
[NavisionReplication].[dbo].[Tbl_Sales_Header]
and
SELECT
[Sales_Invoice_Header_No]
FROM [NavisionReplication].[dbo].[Tbl_Sales_Invoice_Header]
now , how would i do that?
View 2 Replies
View Related
Jan 14, 2008
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins
(
AdminID INT NOT NULL PRIMARY KEY,
SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID)
)
CREATE TABLE SecurityGroups
(
SecurityGroupID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL REFERENCES Admins (AdminID)
)
Any suggestions are highly appreciated...
View 7 Replies
View Related
Oct 7, 2006
I'm currently developing an RDF application which need to handle lots of datatypes. But I want to use SQL-Servers capabilities for efficient querying and sorting. Therefore, I've wanted to create a Main Table which stores a Reference to the Table where the Data is stored. The Data itself should get stored in a Datatype-specific Table.
The Typed Table might get created by something like:
public void CreateTypedLiterals(Type type)
{
String sql = String.Format(
"CREATE TABLE [Literals_{1}] (" +
"ID int DISTINCT NOT NULL, Value {1})",
// BUG: does not work
// WARNING: introduces a potential sql-injection problem
type.ToString()
);
...
As you can see on the statements this solution makes many troubles. So I've wanted to implement it in a more fine way using a DataTable:
[SqlProcedure]
public void CreateTypedLiterals(Type type)
{
DataTable TypedLiterals =
new DataTable(
String.Format("Literals_{0}", type.ToString()));
TypedLiterals.Columns.Add(
"ID", typeof(int), "DISTINCT NOT NULL");
TypedLiterals.Columns.Add("Value", type);
...
But I have totally no Idea how to fetch this result into the existing Database. It might be cool to simply access the Database as it would be a .NET Dataset in the form:
using(Microsoft.SqlServer.Server)
{
CurrentDatabase.Tables.Add(TypedLiterals);
}
But this is afaik not possible. Has anybody an idea how to solve this issure?
View 5 Replies
View Related
Apr 19, 2007
Is there a way to create tables on the sql ce db that matches the tables I'm pulling from the main sql server?
example;
if I do a pull from Customers and ID is a primary key, how can i create this table on my handheld and have the ID set to primary key as well?
I want to do this on all my tables I need to pull from and create on my sql ce db
View 1 Replies
View Related
Jan 13, 2004
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
May I know is there solution for it?
Thank you very much!
View 4 Replies
View Related
Jun 21, 2005
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
View 1 Replies
View Related
Sep 25, 2005
I have install the MSDE from Ofiice 2000 cd and my MSDE sql server is running fine that I can see from my tray bar with green arrow button. Now i want to create the database so can any body tell me how do i create database. Is there ne query analyser or enterprise manager by which I can create database. Also when i try to install starterkit it doen not allow me to install it. It ask me to select database from the dropdown list and when I select Localhost (only option available) and click on test connection it gives me error UnsuccessfullI am new at MSDN so please help me.
View 4 Replies
View Related
Jan 2, 2006
Hi All, 1st of all happy New Year to all asp.net forum members
I am new at asp.net. I want to design a website
using asp.net as frontend and sql database as backend. I am able to
connect and add,update as well delete records when I use MsAcess and
Asp.net using the following connection strings...
**********
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("/database/northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers where city LIKE 'Berlin' order by city ASC"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
**********
But when I try to connect to sql database using the following connection strings I am unable to do so...
**************
SqlConnection myConnection = new SqlConnection("server=PLATINUMVSdotNET;database=pubs;Trusted_Connection=yes");
SqlDataAdapter myCommand = new SqlDataAdapter(" * from Authors", myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "Authors");
MyDataGrid.DataSource=ds.Tables["Authors"].DefaultView;
MyDataGrid.DataBind();
**************
I have written the servername as "PLATINUMVSdotNET" because when I
installed SQL SERVER 2000 I found a tray icon where the server name was
displaying the same (my computer name is PLATINUM).
When I used webmatrix I enterd the same server name and windows
authentication I was able to create a database but How to create
table...
Please help me out
Thanks in advance...
View 4 Replies
View Related
Sep 20, 2007
Hi
I am supposed to execute the script provided to create tables in a database...but I am not getting it from where I run those scripts ?
The script has commands to create tables...
From where do i execute those ?
View 3 Replies
View Related
Apr 8, 2012
I have three tables :
England_Summer_2001
England_Summer_2002
England_Summer_2003
The tables have the following columns :
Player, Position, [From], [To], Fee, Type, ID, League, Window
I want to create a new table, EnglandFinal with all the data from the three tables although I'm guessing it would not be a good idea to copy the primary keys (ID column) as they would clash.
I have played around with CREATE and INSERT into and UNION but I get various errors. I'm sure I've done this before!
This creates a table from a single table :
select * into Final
from England_Summer_2001
View 14 Replies
View Related
