Modifying A Table From A View

Jun 18, 2008

I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.

I also have an inner join view with the initials from both tables as the joining field.

I want the users to be able to modify the data table (MainTable) but also display the users name field.

here is the sql of the view:

SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials

View 3 Replies


ADVERTISEMENT

Newbie: Modifying Table = Wrong Data In View?

Apr 15, 2004

Hi there,

Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.

Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?

Thanks.

Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).

View 7 Replies View Related

Modifying View Data

Mar 28, 2006

Hi,

I was asking how can I make updating and deleting for data through database views in Microsoft SQL Server 2005




Best Regards,

View 5 Replies View Related

Database Automatically Creates Xxx_Temp Table While Modifying / Updating Table Structure .

Dec 16, 2007

Hello friends,

I am new to the SQL Server 2005 development.

From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.

Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,

"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".

where xxx is the table name.

I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.

Thanks in advance. Any help will be appreciated.

View 5 Replies View Related

Modifying SYSJOBSTEPS Table

Dec 19, 2002

I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?

Thanks!

View 3 Replies View Related

Modifying A Field In Table?

Jan 21, 2014

I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?

I have never done dropping a table and recreate it?

View 3 Replies View Related

Trouble Modifying A Table

Mar 13, 2007

Hi There,

I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:

------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------

If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)

I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!

Let me know if I need to provide more info. Thanks.

View 3 Replies View Related

Help On Comparing And Modifying A Resultset Or Table

Jun 14, 2007

HI everybody need help badly on the following

have to recordsets with the following with the following SQL

SELECT place, count(minus)as countminus
order by place
group by place

THE RESULT WOULD BE

PLACE countminus

ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0

MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus

THE RESULT WOULD BE:

PLUS COUNTPLUS

ABC 10
DEF 20

my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...

with this condition..

I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...

THE RESULT WOULD BE THIS.....


PLACE countminus COUNTPLUS

ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0


I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS

View 2 Replies View Related

Modifying Existing Table Schemas?

Jul 28, 2006

By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo.

Is there a way to change the existing schema of a table without disrupting the data that is inside the table?

For example:

Current: dbo.Products

Desired Change: NewSchemaName.Products

Thanks.

View 4 Replies View Related

Get Invalid Cursor State When Modifying A Table In SQL 2000

Jul 3, 2007

When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.

SP3 has been applied to SQL Server 2000.

Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.

View 2 Replies View Related

Trouble With Update Trigger Modifying Table Which Fired Trigger

Jul 20, 2005

Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'

View 1 Replies View Related

Large Table-Table Partition, View Or Other Method?

Aug 27, 2007

Hi everyone,

I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?

Can you please give some suggestions? It will be very helpful if you can post some references or examples.

Thank you!

View 12 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Table/view

Nov 16, 2006

there is a table - TableA in my DB
which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables.
my admin is creating a View which is just like TableA.

my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.

View 2 Replies View Related

View From A Table

Aug 14, 2005

HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards

View 1 Replies View Related

Using Table UDF In A View

May 4, 2007

If anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Modifying A Column

Jan 10, 2002

I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:

ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY

but this does not work.

Any idea how to accomplish this?

Thanks!

Mike

View 1 Replies View Related

Modifying Data

Apr 28, 2004

I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.


Alter Procedure usp_UpdateJobName

@JobNameOld nchar, @JobNameNew nchar

As

UPDATE
JobName
SET
JobName=@JobNameNew

FROM
tblRMADATA

WHERE
tblRMADATA.JobName=@JobNameOld

View 4 Replies View Related

Modifying The DTS Package.

Apr 28, 2004

I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata

So how and where can I change my package.

Thanks.

View 2 Replies View Related

Modifying The DTS Package.

May 5, 2004

I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.

View 1 Replies View Related

Help With Modifying Query

May 29, 2008

Hi,

I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.

What I want to add is the following data.

For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate

Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.


Any assistance is much appreciated!!

thanks once again
mike123


SELECT CONVERT(varchar(10),referDate,112) AS referDate,

SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',

count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,

SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',

SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'



FROM tblreferemails R

WHERE DateDiff(dd, referDate, GetDate()) < 5

GROUP BY CONVERT(varchar(10),referDate,112)

ORDER BY referDate DESC









CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)




CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]

GO

View 3 Replies View Related

Help In Modifying Query

Nov 19, 2007

Greetings

I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)

INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL

select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1

EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2

How can I modify the query to achieve the expected output?

Thanks:

View 9 Replies View Related

Modifying Report

Feb 13, 2008

Hi our reports server has some reports on it.

we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO".

can we get rdl file from reporting server ?

View 3 Replies View Related

Modifying DTS In 2005

Sep 27, 2007

Can I modify 2000 DTS in 2005.I mean to say add some functionality to the existing DTS.

View 1 Replies View Related

Turning A View Into A Table

Apr 15, 2007

I am in a scenario where my tables are refreshed every morning by a batch update.  I have built a few views off of one table.  To increase speed I would like to take all the rows from one of the view s and insert them into their own table.  I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate 

View 1 Replies View Related

Using A View As A Lookup Table

Jan 29, 2004

Hi

I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.

In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:

1) Is it possible to perform a JOIN on a view in the way that I would need to here?

2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?

Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.

Cheers
Jon

View 2 Replies View Related

How To Connect To A SQL View NOT Table

Jul 14, 2005

Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View.  The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns. 
 

View 2 Replies View Related

Table And View Will Not Drop

Jun 13, 2001

We have a table created by an application, and a view that joins the table with other tables.

For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.

However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.

Anyone know what could be the problem here?

View 2 Replies View Related

Design View Of Table

Jul 25, 2001

How do you get the design view of a table in sql server query analyser?
I know in oracle its desc table_name.
Thanks

View 6 Replies View Related

What&#39;s Different From SELECT FROM A TABLE To A VIEW?

Oct 12, 2000

Hi all,
What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following?
Example for:
-- TABLE:
set statistics io on
select * from TABLE_NAME
-->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0

-- VIEW:
Create view VIEW_NAME as SELECT * FROM TABLE_NAME
set statistics io on
select * from VIEW_NAME
-->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.

View 2 Replies View Related

Hide Table View

Oct 14, 1999

Hi,

How can I hide all the system tables so that only user tables show in table view.

Thank you very much!

John

View 1 Replies View Related

Best Way To View Table Properties

Apr 28, 2003

Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?

View 5 Replies View Related

View Table Problem

Mar 27, 2007

Hello

I am trying to insert records to view table.

Msg 4405, Level 16, State 1, Line 6
View or function 'table1' is not updatable because the modification affects multiple base tables.

table1 is view table

My Synatx is

insert into table1
select table1.col1, table2.col2
from table2, table3
where table2_id = table3_id

what is wrong with these.
Thanks

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved