View Tables And Insert Updates
Jun 2, 2006Hi to all
I have a View of 3 tables I would like to be able to update insert
data into Views table that in return do the same in the original tables is that possible?
Thank you
ADVERTISEMENT
Merge Data From Two Tables Into One Table - No Updates/only Insert
Sep 10, 2007
Hi all,,
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question.
I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example:
Table A
--------------
1 test test1 test2 test3 test4 test5
2 test test6 test7 test8 test9 test10
Table X
------------
1 test test1 test2 test99 test4 test5
2 test test98 test97 test 96 test95 test94
--------------------------------------------------------
Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..
The new Table A should look like
NEW Table A'
-----------------
test test1 test2 test3 test4 test5
test test6 test7 test8 test9 test10
test test98 test97 test 96 test95 test94
------------------------------------
I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
Thanks in advance.
How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..
Oct 30, 2007Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
Best Way To Insert A DateModified In Updates
Jul 24, 2007how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed
View 2 Replies View RelatedUpdates, But Doesnt Insert..
Nov 5, 2007Hi,I have a button with code to update a column in a table, and insert other values into another table.I currently have it working to update the column, but not insert.It gives an error message, but still updates the column. It doesn't insert, which leads me to believe the error is somewhere in the insert code..It isn't giving me any red squiggly lines in the code though, and the error doesn't show where its referring to, just giving a stack trace.. Error: Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.Incorrect syntax near '@User'.Stack: [SqlException (0x80131904): Incorrect syntax near ')'.Incorrect syntax near '@User'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +180 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2411 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1089 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115 detailproview.ExecuteInsert(String quantity) +452 detailproview.Button2_Command(Object sender, CommandEventArgs e) +79 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +75 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +155 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4886 Code:private bool ExecuteUpdate(int quantity){ SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - @qty WHERE productID=@productID"; command.Parameters.Add("@qty", TextBox1.Text); command.Parameters.Add("@productID", labid.Text); command.ExecuteNonQuery(); con.Close(); return true;} private bool ExecuteInsert(String quantity) { SqlConnection con = new SqlConnection(); con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"; con.Open(); SqlCommand command = new SqlCommand(); command.Connection = con; TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1"); Label labname = (Label)FormView1.FindControl("Label3"); Label labid = (Label)FormView1.FindControl("Label13"); command.CommandText = "INSERT INTO Transactions (Usersname)VALUES (@User)"+ "INSERT INTO Transactions (Itemid)VALUES (@productID)"+ "INSERT INTO Transactions (itemname)VALUES (@Itemsname)"+ "INSERT INTO Transactions (Date)VALUES (+DateTime.Now.ToString() +)"+ "INSERT INTO Transactions (Qty)VALUES (@qty)"+ command.Parameters.Add("@User", System.Web.HttpContext.Current.User.Identity.Name); command.Parameters.Add("@Itemsname", labname.Text); command.Parameters.Add("@productID", labid.Text); command.Parameters.Add("@qty", TextBox1.Text); command.ExecuteNonQuery(); con.Close(); return true; }protected void Button2_Click(object sender, EventArgs e){ TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteUpdate(Int32.Parse(TextBox1.Text) );}protected void Button2_Command(object sender, CommandEventArgs e) { if (e.CommandName == "Update") { TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox; ExecuteInsert(TextBox1.Text); } }} If anyone can help, ill be very grateful!! Thanks!Jon
View 7 Replies View RelatedHow Do I Look Through The Log To See Updates To Tables In Sql Sever 2005?
Aug 22, 2006hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?
View 5 Replies View RelatedCounting Inserts & Updates From A Specific Or Group Of Tables?
Mar 11, 2002I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this?
Thank you in advance.
Colin P.
Enterprise Manager - Slow Updates When Editing Tables
Jul 20, 2005Hi,I have a table with about 70k rows. When I use open table --> returnall rows and then edit a field Enterprise Manager then says it has toreturn the entire result set for the update to take place.This can take a long time and makes editing data a slow process. Isthere any way to get Enterprise Manager just to update the one row??Thanks Tom
View 1 Replies View RelatedStored Procedure Doesn't Recompile After Replication Updates Underlying Tables
Mar 21, 2007We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
View 3 Replies View RelatedCannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
Apr 9, 2007Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
Dec 19, 2007I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
Insert Records From Foxpro Tables To SQL Server Tables
Apr 22, 2004Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
Insert From A View
May 22, 2008Hi,
I need to write a stored procedure which can insert into a table some records and these records are results of a view.
Can anyone tell me what should I do?
Thanks
Insert New Row To View
Jul 20, 2006
Hi all ,
I have a problem to insert new data to a view which was created from 2 tables.
I am trying to so from run time
Any help will be appreciated
Can't View Tables In EM!
Sep 25, 2002I have suddenly lost the ability to view the tables for one of my database via Enterprise Manager. It just sits at an hourglass and never returns!
Alos, if I attempt to link to the tables via MSAccess's ODBC interface, I get a timeout.
I am able to view the table list for other user databases, as well as the system databases.
I am able to view other object list (like SPs) for the database in question, via Enterprise Manager.
I am able to perform a select query against the sysobjects table for the database in question.
Any ideas on why Enterprise Manager, and ODBC links, are hanging while trying to access the tables for one particular user database?
Any help is appreciated.
All other functions appear normal. If I can't figure anything out, or no one has any other ideas, I will try rebooting the server after the users are gone for the day.
Sql EX 05 Can't View DB And Tables
Nov 28, 2005Hi, I€™m new to sql ex 2005 and have a couple of questions I can€™t find the answers to. Q.1 I am working with VS web dev ex 2005 and I€™m trying to backup my db, is it as simple as copying the .mdf file? Or should I create a text file, or something else? Q.2 How do I view the content of the db in my web programs in the sql server 2005 ex? I can€™t figure out what to do, to view the content. I have tried to find tutorials on ex 05 but can€™t find answers to my questions. Help would be appreciated. Thank you.
View 7 Replies View RelatedINSERT Record Through A View
Apr 20, 2004Is is possible to insert a record through a view. If so, how?
USE Northwind
GO
CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)
GO
ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO
CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO
SELECT * FROM MyProducts
GO
DROP VIEW MyProducts
GO
DROP TABLE tbFlatPanelForms
GO
DROP TABLE tbDoubleTeeForms
GO
DROP TABLE tbForms
GO
Mike B
View 2 Tables With A Certain Criteria
Aug 28, 2000Is it possible to to view 2 tables with a common field name and display it in the following way
Name telephon no.
John 123-4567
789-4561
987-6543
Peter 159-7536
654-9874
896-3214
456-9874
without repeating the name in each row.
Thanks
List Tables In A View??
Jan 5, 2005Is there a way to easily list the tables/views that a view is using to get its data?
Thanks in advance,
Shawn
Identyfying Which Tables Have Gone Into A View
Oct 6, 2005I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views. Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view? I have used the following SQL, but unfortunately it gives too many results: SELECT VIEWS.name AS VIEW_NAME, TABLES.name AS TABLE_NAME, VIEW_SQL.text FROM sysobjects VIEWS INNER JOIN syscomments VIEW_SQL ON VIEWS.id = VIEW_SQL.id INNER JOIN sysobjects TABLES ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%' WHERE (VIEWS.xtype = 'V') AND (TABLES.xtype = 'U') ORDER BY VIEWS.name, TABLES.name Justin
View 2 Replies View RelatedView From 2 Tables / Multiple Row To One
May 2, 2014I want to combine 2 tables into a view. The first table is a list of 'Products' (P1, P2.....)
Table: Product
IDUserStart Date..
=====================================
P1Fred12/04/2012
P2Jane01/12/2011
P3Mike01/12/2011
P4Stu01/12/2009
P5 Etc
Table two is qualities for each product. A product can have from zero to 8 qualities. Qualities have a description and a value (varchar).
Table: Qualities
ProductDescrip.Value
=====================================
P1ColourRed
P2ColourGreen
P2Hard2.2
P2Weight123
P3ColourBlack
P4ColourWhite
P4Weight8.12
P6Mass12.2
P2abcdef Etc
I would like to create a view that joins these tables. Each row show a product and all the qualities it has, from quality 1 to quality 8. If there is no N'th quality, to show blank/null/zero.
The problem is how to get values from multiple rows (in table Qualities) in a single row in the view.
Wanted View
ProductUserQuality1Value1Quality Value2Quality3Value3Quality4Value4 etc..
=====================================
P1FredColourRed0000000
P2JaneColourGreenHard2.2Weight123abcdef
P3MikeColourBlack000000
P4StuColourWhiteWeight8.120000
etc
How To Find The Tables In A View
Nov 15, 2006Dear friends,
is there any way to find the table names from a view?
ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........
or we have to use sp_helptext?
is there any better way?
thank you very much.
Vinod
View From A Merge Of Two Tables
May 15, 2006Hi,I hope this is the right place to ask this question. If it is not,please let me know a more appropriate place.I have two tables.CREATE TABLE tblEmployees(EmployeeID int identity NOT NULL,LastName varchar(50) NULL,FirstName varchar(50) NULL,);CREATE TABLE tlkpDept(DeptID char(5) NULL,Name char(10) NULL,LongName char(50) NULL);Now I want to create a view called AssignedTo. [The application I'mdoing, will track the status of our customer requests. Each requestcan be assigned to either an individual employee or an entiredepartment]I want the view to have two columns, ID and LongName. ID should beeither the DeptID or the EmployeeID. The LongName column should beeither the LongName or the FirstName + LastName.I'm not even sure how to begin to write such a complex SQL.EnterpriseManager is being less than helpful too.Can anyone help?Thanks in advance.-Tom
View 4 Replies View RelatedView Of Max Values From Many Tables
May 22, 2007I have about 14 tables that I am trying to tie together in a view tosee a user's status in our company.Joe User:Email - ActiveADP - ActivePortal - Inactiveetc.We need to track application access across the company, so we havetables for each application that contain user info(username,password(encrypted), start date, end date, changed date) so that wecan track who has what, and when they were given access as well aswhen it was taken away.Every application table has a userID attached to it.What I would like to do is to take the userID field and look for theirapplication access across the company. To do this, i'll have to lookfor the max value in each table because someone could be given access,have it taken away, and be given it again. People move all over theplace here, so we have to be able to track who has what, when, and atwhat building.I started out with trying to left outer join the tables together, butit didn't work. I tried doing something along the lines of:selectesarfAppEmail.emailID,esarfAppEmail.esarfUserID,CASE WHEN esarfAppEmail.endDate IS NULL Then 'Active' else 'Inactive'end as EmailStatus--,
Quote:
Concat Tables Into One Row In View
Jun 26, 2007If I have table1 and table2 with table2 having multiple rows tied to asingle row in table 1.What I am trying to do is set up a view that has one row that showsthe followingtable1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,table2.row3.detailI'd like to be able to do a select on the view and only come back withone row per widget. If possible, I'd actually like to be able toconcat all the rows from table 2 into one column if that's possible.table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail- table2.row3.detail), table1.dateCreatedthxM@
View 1 Replies View RelatedView Between Tables In Different Databases
Jun 25, 2007We have created a view that selects from tables in two databases. it looks like something like this:
CREATE VIEW myview AS
SELECT col FROM db1.mytab
UNION SELECT col FROM db2.mytab;
Both tables have col as primary key.
When we executes SELECT col FROM myview WHERE col='SOMEVALUE' we receives the correct result but SQL server does not use any indexes. It performs a index scan on both tables. Wh and what can we do about it? The tables contains millions of rows so the performance is terrible.
The following works fine:
SELECT col FROM db1.mytab WHERE col ='SOMEVALUE'
UNION SELECT col FROM db2.mytab WHERE col ='SOMEVALUE';
Qe are running SQL Server 2000 and TRANSACT SQL
Best regards
/Ingvar
How To View INFORMATION_SCHEMA.TABLES
Jun 20, 2007Hi:
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
Thanks a lot
Insert To A Sql Database From A Details View!?!?!!!?!?
Apr 12, 2007I have tried just about everything i could find on the internet to fix this.
Anyway.....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address
I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error.
Anyone who can figure this out must be a sql genius (or I am a complete idoit).
Here is the code I have so far:
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<script runat="server">
Dim strClient_Name As String
Dim strClient_Address As String
</script>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Client ID"
DataSourceID="SqlDataSource1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="Client Name" HeaderText="Client Name" SortExpression="Client Name" />
<asp:BoundField DataField="Client Address" HeaderText="Client Address" SortExpression="Client Address" />
<asp:BoundField DataField="Client ID" HeaderText="Client ID" InsertVisible="False"
ReadOnly="True" SortExpression="Client ID" />
<asp:CommandField ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>"
InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"
SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients">
</asp:SqlDataSource>
</asp:Content>
Best Regards and Thank You,
The King
Distributed View Insert Fails
May 7, 2008
Hi Folks,
I have been trying to setup distributed views. On two machines I was successful. These machines do not have any domain configured. I was running SQL 2000 Enterprise on both machines with Win2K.
The steps I followed were:
1) Add linked servers
EXEC master.dbo.sp_addlinkedserver @server = 'FED2', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='WEBSERVER1', @catalog='Federated_Bridge'
2) Add linked servers login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='FED2',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
3) Create table
create table Entity_2(id int not null ,EntityID int not null)
go
4) Add Table constraints
ALTER TABLE [dbo].Entity_2 ADD
CONSTRAINT [CK_11] CHECK ([EntityID] <= 10),
CONSTRAINT [PK_Entity_2] PRIMARY KEY CLUSTERED (id,EntityID )
GO
5) Set Xact Abort on
SET XACT_ABORT ON
GO
..The above steps were repeated on two machines. Step 4 was changed so the check constraint did not overlap.
The steps worked. But now I am trying to do the same thing on two different machines. Both are running Win2K and SQL 2000. The only difference that I can see in the setup is that these new machines are setup under a single domain - ie there is a domain controller - whereas in the first case there is no domain controller.
The error message I get is:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Please help.
INSERT INTO MULTIPLE TABLE VIEW ?
Oct 11, 2007Hi There
Is it possible to insert or update through a view that effects multiple tables in Sql Server 2005 ?
Of if it is possible what ar ethe limitations ?
Thanx
Bulk Insert In To A Partitioned View?
May 24, 2006Greetings once again my SQL friends,
I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :
Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation
How to solve this problem?
Linking Two Tables In A Detail View
Apr 10, 2007I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor
Joining Multiple Tables In A View.
Dec 5, 2005I have three tables
1st table is Student
StudnetID (pk)
Other fields…
2nd table is PhoneType
PhoneTypeID (pk)
PhoneType
3rd table is StudentHasPhone
SHPID (pk)
StudnetID (fk)
PhoneTypeID (fk)
PhoneNumber
PhoneType is an auxiliary table that has 5 records in it Home phone, Cell phone, Work phone, Pager, and Fax. Is there a way to do a join or maybe make a view of a view that would allow me to ultimately end up with…
StudnetID: 1
Name: John
HomePhone: 123-456-7890
WorkPhone: 123-456-7890
CellPhone:
Pager: 123-456-7890
Fax:
Memo: This is one student record.
Some students will have no phone number, some will have all 5 most will have one or two. If possible I would like to do a setup like this in my database to keep from having to have null fields for 4 phone numbers that the majority of records won’t have.
Thanks in advanced,
Nathan Rover