Erase table


all
How we erase a precise table in a data base under SQL serveur2000
Thank you
Posted On: Friday 26th of October 2012 12:00:44 AM Total Views:  343
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Creating inter-table relationships using Primary Keys/Foreign Keys Problem

again, 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, teacher_id INT NOT NULL, class_title VARCHAR(50) NOT NULL, class_grade SMALLINT NOT NULL DEFAULT 6, class_tardies SMALLINT NOT NULL DEFAULT 0, class_absences SMALLINT NOT NULL DEFAULT 0, CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id) REFERENCES Users (user_id) ) 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, teacher_id INT NOT NULL, grade_id INT NOT NULL, CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id) REFERENCES Grades (grade_id), CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id) REFERENCES Classes (class_id, teacher_id) ) 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 Thank you for your assistance.
VIEWS ON THIS POST

401

Posted on:

Friday 26th October 2012
View Replies!

loop though table using RowID, not cursor (was "Loop")

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
VIEWS ON THIS POST

289

Posted on:

Friday 26th October 2012
View Replies!

Issues importing a text file (tab delimited) to a SQL table

I have a text file I am trying to import to a table. This text file is in a tab delimited format. I am using DTS to import the data to a new table I made. The fields are varchar and are set to allow nulls & allow 8,000 characters per field. The error I am getting is that the data exceeds the allowed amount (or something like that) in col4. Now I have checked everything in column 4 and nothing exceeds 5,000 spaces/characters combined. I have checked the entire sheet (in excel) for that fact, and there is not one single column/row/cell that exceeds 5,000 spaces/characters combined. What the heck could be causing SQL to tell me I am trying to import too much data in one column when there is nothing that even comes close to 8,000 characters & spaces combined
VIEWS ON THIS POST

330

Posted on:

Friday 26th October 2012
View Replies!

Code for creating a table in SQL from external source

I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute. The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
VIEWS ON THIS POST

337

Posted on:

Friday 26th October 2012
View Replies!

add checkbox column to access table (was "SQL query help.., please, any one??")

good people of this forum.. I am working on an eccess db, and using asp to update, change and so on... I want to add dynamicly a field to the db, using the ALTER TABLE command, but for some reson I can't find a way to make it work, the field type I am trying to add is boolean, and I want it to show up in the actual db as a checkbox, and not as a bit field (0/1), so I've been trying this and non of them work, and I couldn't find any sites with examples for this... any help would be very appricated..., this is what I've tryed so far and didn't work: "ALTER TABLE [Users] ADD [CHILD] boolean" "ALTER TABLE [Users] ADD [CHILD] boolean False" "ALTER TABLE [Users] ADD [CHILD] boolean(f)" "ALTER TABLE [Users] ADD [CHILD] boolean f" me out on this\t
VIEWS ON THIS POST

287

Posted on:

Friday 26th October 2012
View Replies!

table Self-joins with updates

table = PEOPLE Name Money Type ----- ----- ---- Steve 400 R Steve 100 R Paul 500 R Paul 100 R Matt 500 R Matt 200 R Matt 0 T Steve 0 T Paul 0 T I'm trying to add-up all of the Money values for each Name and store them into their names, but under Type 'T'. after the update command it should look like this Name Money Type ----- ----- ---- Steve 400 R Steve 100 R Paul 500 R Paul 100 R Matt 500 R Matt 200 R Matt 700 T Steve 500 T Paul 600 T
VIEWS ON THIS POST

325

Posted on:

Friday 26th October 2012
View Replies!

Trying to pull data from 2 tables into a query

I'm trying to create a SQL query to pull together a job info table and a customer info table in an Access DB. We can't have a unique ID on the customer that we can store in the job table because the customer info is dumped from Exchange and the ID (row number) in the cust_List table will change each time we export from Exchange. What I need to do is go through jobs, get the CO_ID, the CONTACT_LN and the CONTACT_FN and then match it against Customer_List>Account, Lastname, Firstname. Any ideas
VIEWS ON THIS POST

300

Posted on:

Friday 26th October 2012
View Replies!

Checking duplicate values two tables

Hi , Is it possible in SQL to Restrict value in one table checking a value on anather tables. Scenerio-1. I have two table let say, Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value. Scenerio -2.[/B] I have table called [B]SIC. This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns. Please Advise. Vijay
VIEWS ON THIS POST

333

Posted on:

Friday 26th October 2012
View Replies!

Converting a SQL table into Text file

How to convert a SQL table into Text file I have a table and I want to extract the values with the field names above to a text file. The query should also allow me to define the starting position of the fields in the text file. email: vijay.prasad@in.ibm.com
VIEWS ON THIS POST

335

Posted on:

Friday 26th October 2012
View Replies!

Copying data from one table to another table

I need to copy data from one table into another table. The field names are NOT the same: I need to INSERT Table 1 fields: cdescurl cname cdescription cimageurl category cprice INTO Table 2: product_url name description image_url category price How do I do this Thank you in advance for any help you can give me!
VIEWS ON THIS POST

287

Posted on:

Friday 26th October 2012
View Replies!

copy table to different server

i need a script that can copy a table from a db on one server to another for me, but i have no idea how.. help much appreciated !
VIEWS ON THIS POST

331

Posted on:

Friday 26th October 2012
View Replies!

aggregates from two unrelated tables

Hi I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.phpt=39 Pls help me to fix this sql Regds Bala
VIEWS ON THIS POST

362

Posted on:

Friday 26th October 2012
View Replies!

Combining info from two tables?

, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other. So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written Sorry if this is a little confusing but im having a hard time visualising how to do this.
VIEWS ON THIS POST

393

Posted on:

Friday 26th October 2012
View Replies!

Help with MS SQL Server - updating data from 2 tables

Hi all, Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables If yes- what would be the query structure for that Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it Thank you in advance.
VIEWS ON THIS POST

324

Posted on:

Friday 26th October 2012
View Replies!

problem w/ linked table permissions

This question involves SQL server as well as Access, so I hope that someone can follow. I'm trying to give someone read and write access to a table in SQL server through a linked table scenario in Access. I set up a new user account with datareader and datawriter permissions and made sure it went into the particular table as well. I also re-created the DSN on the user's local machine using this new login information. However, each time you open up the table after logging in using this info and try to edit a field, it gives a "Write Conflict" error and gives three choices: Save Changes, Copy to Clipboard, Drop Changes and of course, Save Changes is disabled. We also tried logging in using the db admin account and it won't work. Anyone have similar experiences with this If so, is there a work-around so that you can edit data through Access
VIEWS ON THIS POST

316

Posted on:

Friday 26th October 2012
View Replies!

two select statements on one table

Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this. SELECT column1 FROM table1 WHERE column2 = (select column2 from table1 where column3 = 14) Any ideas greatly appreciated.
VIEWS ON THIS POST

311

Posted on:

Friday 26th October 2012
View Replies!

Open table in Query Analyzer

In query Analyzer you can right click on a table and select open. When you do it opens the table as if you were in Enterprise Manager(you can edit the data). Does anyone know the syntax of how to write that in a query so you do not have to right click and select open
VIEWS ON THIS POST

285

Posted on:

Friday 26th October 2012
View Replies!

Classic ASP AddNew record to MsSQL table

Having trouble with a classic asp page, that gives a HTTP 500 - Internal server error when I try to add a new record to a table. I'm guessing the formatting needs to be changed. It was working fine when I was using in a .mdb database Code: rsTicketReviewUpdate.Open "SNA_Ticket_Reviews", adoCon rsTicketReviewUpdate.AddNew rsTicketReviewUpdate.Fields("MAIN_TICKET_ID") = Request.Form("MainTicketID") rsTicketReviewUpdate.Fields("MTTR_Drivers") = Request.Form("MTTR_Drivers") rsTicketReviewUpdate.Fields("MTTR_Actions") = Request.Form("MTTR_Actions") rsTicketReviewUpdate.Fields("OTR_Drivers") = Request.Form("OTR_Drivers") rsTicketReviewUpdate.Fields("OTR_Actions") = Request.Form("OTR_Actions") rsTicketReviewUpdate.Fields("Misc_Notes") = Request.Form("Misc_Notes") rsTicketReviewUpdate.Fields("AdjRpt") = Request.Form("AdjRpt") rsTicketReviewUpdate.Fields("RptCauseInfo") = Request.Form("RptCauseInfo") rsTicketReviewUpdate.Fields("AdjZ_Location") = Request.Form("AdjZLocation") rsTicketReviewUpdate.Fields("AdjXO_CIRCUIT_ID") = Request.Form("AdjXO_CIRCUIT_ID") rsTicketReviewUpdate.Fields("AdjMTTR") = FormatNumber((Request.Form("AdjMTTR")), 2) rsTicketReviewUpdate.Fields("AdjMTTR_Reason") = Request.Form("AdjMTTR_Reason") rsTicketReviewUpdate.Fields("AdjOTR") = FormatNumber((Request.Form("AdjOTR")), 2) rsTicketReviewUpdate.Fields("AdjOTR_Reason") = Request.Form("AdjOTR_Reason") rsTicketReviewUpdate.Fields("AdjPRIORITY") = Request.Form("AdjPRIORITY") rsTicketReviewUpdate.Fields("AdjPRIORITY_Reason") = Request.Form("AdjPRIORITY_Reason") rsTicketReviewUpdate.Update rsTicketReviewUpdate.Close
VIEWS ON THIS POST

128

Posted on:

Wednesday 7th November 2012
View Replies!

Transfer data between two tables in a database

Hi all, I am using MsSQL 5.0 server database. I need to transfer the data from one table to another within a SQL database based on the particular time interval. Is it possible to transfer data among the tables a database automatically. Please help me.
VIEWS ON THIS POST

169

Posted on:

Wednesday 7th November 2012
View Replies!

A little help with a stored procedure- how to update from one table to another

I want to update an existing table from another table. I would like to take the id# from table1 and insert it into table#2 and also update other columns in table #2 with getdate(). I am doing the following. I tried putting the 'studid' inside the parentheses, but it didn't like this... Any tips would be appreciated. Thank you. Code: ALTER PROCEDURE dbo.updCourseRoster AS INSERT INTO [COURSE ROSTER] (DATEADDED,TIMEADDED) VALUES(GETDATE(),GETDATE()), STUDID = STUDENTSMG.STUDENTID SELECT STUDENTID FROM STUDENTSMG WHERE STUDENTSMG.NOTES LIKE '"XLS SOURCE"'
VIEWS ON THIS POST

189

Posted on:

Wednesday 7th November 2012
View Replies!