.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Melody Anderson
Post New Web Links

Updating the design of an SQL table

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

guys i would like to update the design of a table.

Trying to save the design i am getting an error that the operation isn't permitted.

A constraint exist between this table and another table

any idea ? thanks


View Complete Post

More Related Resource Links

Updating table from a table on another server

I am updating a column in table from a table on another server and am having some troubles. My code looks like this...   UPDATE TABLE1 SET COLUMN1A = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2A FROM TABLE1, SERVER2.DATABASE2.SCHEMA2.TABLE2 WHERE TABLE1.COLUMN1B = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2B   I keep getting "The multi-part identifier ____ could not be bound."   Security rights are in place and I have access to Server2    Thoughts?

Best table design for modelling a 1:0..1


Hi Gurus,


Apologies if this isn't the right place for this kind of question but I felt sure you guys would know the answer to this fairly simple design question.


I would like to model an item and a subject entity.  

An item can be assigned to zero or at most one subject.  

A subject may consume zero or one item.


How would it be best to model this kind of relationship in the database?  I was originally thinking of having an item FK in the subject table but that would potentially allow multiple subjects to be assigned the same item.  Then I was thinking of having a subject FK in the item table that would force only one subject to be assigned 1 item.

I think I'm just going round in circles here and it may be that my business logic should take care of these issues.  


My question is how is it best to model this kind of relationship?


Kind regards

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


convert string to int before updating database table?


I have a form in which I display data from a database table, change the data and then update the database table. The form consists of textboxes, so all data displayed in the form is string. However - one data is int in the database table (the rest is varchar), so I need to convert it from string to int when I update the database table. How do I do it?

protected void SaveProd_Click(object sender, EventArgs e)
        string BildID = TextBoxProdPictID.Text;
        string namn = TextBoxProdName.Text;
        string kategori = TextBoxProdCategory.Text;
        string stl = TextBoxProdSize.Text;
        string pris = TextBoxProdPrice.Text;
        string iLager = TextBoxProdNumberOf.Text; //this is int in the database table

        SqlConnection conn = new SqlConnection(config);
      //  conn.Open();
        string sql = "UPDATE Produkter SET kategori ='" + kategori + "', namn ='" + namn + "', stl ='" + stl + "', pris ='" + pris + "', iLager ='" + iLager + "' WHERE ID ='" + ID + "'";
        SqlCommand comm = new SqlCommand(sql, conn);//Conversion failed when converting the varchar value '__Page' to data type int.
        SqlDataReader dr = comm.ExecuteReader(); 

        //clear t

How to create a Table of content in .rdl design in BIDS.



well am having a problem on.

How to create a Table of content in .rdl design in BIDS. either it should be dynamic or there should be bookmark of each section in TOC.

can any one help me out.?..



Help with design choice -- selecting parts for ordering table


The next part of what I'm working on will involve adding parts to an order, and will be more complex than probably anything I have done.

The amount of parts to choose from will be very extensive.

Anyone care to plant the seed,  have an example, or helpful input on easy ways to select the parts,  add them to a temp table/data table/data set, until all parts are selected (?), and insert into the database when done?

Dropdowns would, in cases, have a large amount of items. Select items from a gridview Master/Detail style? Checkbox lists?

I'm not looking for code, just a point in the right direction. I've looked at some shopping cart/e-commerce type designs, but it just doesn't fit what I'm trying to do very well.



How to create a Table of content in .rdl design in BIDS dynamically according to the Page no wise..?



well i am able to create the TOC statically by using the Document Map properties as describe in my older post issue 

but now i just want to create the Table of contents dynamically generate using the page no wise..is it possible...how can we do it while designing the .rdl in BIDS..



Having trouble updating 1,000,000 row table


Using this T-SQL statement this process should take about 2 minutes.  We know this by limiting the update to 100,000 rows. (the table has an identity field so we can control how many and where to start).  100,000 rows update in 11 sec.  That translates to about 2 minutes for the entire million row table.  There is some kind of problem that resolves itself after a while.  We timed the statement in a larger proc where it was contained and it eventually finished after 2 to 4 hours. 






[Cost] = dw_Prod.

Updating a table using a variable object


I have a variable of Data Type 'Object'. I populate this with 1 row of data via an Execute SQL Task control,  because I have a For Each Loop Container around this Execute SQL task control that gets 1 ID at a time. What I want to do is now that I have the row of data in my variable object, is update my table with the new data.

I am thinking I need to start with a Data Flow Task? But I'm not sure how/what to use to reference my variable so that I can map it to my destination.

Updating values of one column of table 1 from a different table


Hi I need to update one column with the values from a column from a different table joined by the subtypes. Please see below...







PRODUCT =(dbo.[Subtype-desc$].[Subtype-description])

Which table design is good for performance?


hi All,

I have situation to create table with datetime column for OLAP (more select stmt.) database with following conditions,

1.       Need to insert only date (no need time)

2.       Only SQL Server 2005 or less can be used.

3.       More than 10 million records capacity of the table.


use tempdb
drop table T1;
drop table T2;
create table T1(col1 int, colD tinyint, colM tinyint, colY smallint);
create table T2(col1 int, colDate datetime);
insert in

Dropdownlist is not updating the table

I am wondering what I need to correct , Detailsview is in ReadOnly mode. 
  <asp:BoundField DataField="ShipTo" HeaderText="ShipTo" SortExpression="ShipTo" />
                <asp:TemplateField HeaderText="PaymentMethod" SortExpression="PaymentMethod">
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("PaymentMethod") %>'></asp:TextBox>
                        <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="AccessDatasource1"
                            DataTextField="PaymentType" DataValueField="id">
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("PaymentMethod") %>'></asp:TextBox>
                        <asp:Label ID="Label1" runat="

Multiuser SSIS Package Updating One table


Suppose I have oen SSIS package that update to ONE table and multiple users are executing that package (using Agent JOB). SO is there any chance that the package will fail for some user, due to table lock?

Note: For my OLEDB Source component I am using SQL query with a where condition for that user and that insert to OLEDB Target SQL Table.

If Yes, How should I handel multiuser situation here so that all users can insert into the same table.


Updating multiple Indexes on a table - Performance issue



I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated


Updating sql express 2008 database table from an XML file.


I'll start off with the usual caveats.. new to the forum, new to programming, not a programmer by trade..just a dabbler


A little background..


I run a hockey pool for me and a few friends. I currently use Excel to produce everything. Every week I download a csv file from a stats reseller, update my pool, then run some macros to create web pages and upload those to my site. It works alright but I want to take my pool/site to the next level. So I decided to do a new site using ASP.NET and sql express.


My plan is to download an XML file from the same reseller and update my "Player" table every week with the "new" stats.


I used a Dataset, SqlDataAdapter, and the XML file from the reseller to populate my "Player" table in the beginning. It was pretty slick, pretty easy. My problem is I can't seem to figure out how to update my "Player" table with my new stats.


First I tried using the same Dataset/SqlAdapter but it threw a Primary Key exception, saying I couldn't insert that ID because it was already existing in the table. I can fill my dataset from the "Player" table change things and save it back to the "Player" table..but that's not what I want to do.


In my min

Thoughts on table design?


We are building a new OLTP database that has a design consideration that I am looking for feedback on. that consideration is tracking history of change on every row of every table in the design. CDC has been ruled out because its missing some feature that is needed.

My first thought for tracking history is to use triggers to capture changes to an audit table. however, the current design uses the stored procedures that insert/update rows to write to the audit tables. basically the current row is queried for and stored in a temp table...then a hash is created on the row...a hash is created on the new row and if they are different a row goes into the history table and the row is updated.

(i think this is bad design as the same code would have to be repeated for each procedure written that updates a table, and you have about 20-30 lines of code cluttering up every stored proc. for some reason some developers think triggers are evil though because they are "hidden")

the second thought on these history tables is that a row should be entered into the history table on insert of a row AND on update...the update should insert the new value.  I find that most people only want to store the old value of the changed record and not the new value.  the reason i would store the new value is so you dont have to look in 2 tables to find what a recor

CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend