Two Basic Questions.
in [Server Configuration]
|
From: Marian Henna on 1 Mar 2010 15:00 Hello, I am new to MS SQL 2K, migrating from MySQL. First of all, this company I just started with has an MS SQL2K database sitting on a windows xp pro machine. We are moving it to MS Sever instead, don't know why it was on XP Pro - bad decision we think. Second, in MS SQL we need to update a field during an insert event. So my two questions are: 1) If we run the script to create the DB on the Server, can we drop the DB and load the original DB from the XP machine? I think this is the correct terminology. 2) We have a column in a table that we need to update via a calculation of two fields. So we need to do something like this: --->>> WO_Total = WO_Item_Cost * WO_Item_QTY. Not sure how to put this in to a trigger. We do this fine in MySQL, but can't seem to figure out how to make this trigger. Thanks, and if I'm not clear enough I certainly can provide more detail.
From: John Bell on 1 Mar 2010 16:44 On Mon, 1 Mar 2010 13:00:52 -0700, "Marian Henna" <marion.henna(a)yacobe.net> wrote: >Hello, I am new to MS SQL 2K, migrating from MySQL. > >First of all, this company I just started with has an MS SQL2K database >sitting on a windows xp pro machine. We are moving it to MS Sever instead, >don't know why it was on XP Pro - bad decision we think. > >Second, in MS SQL we need to update a field during an insert event. So my >two questions are: > >1) If we run the script to create the DB on the Server, can we drop the DB >and load the original DB from the XP machine? I think this is the correct >terminology. >2) We have a column in a table that we need to update via a calculation of >two fields. So we need to do something like this: > >--->>> WO_Total = WO_Item_Cost * WO_Item_QTY. > >Not sure how to put this in to a trigger. We do this fine in MySQL, but >can't seem to figure out how to make this trigger. > >Thanks, and if I'm not clear enough I certainly can provide more detail. > Hi I would not move to SQL 2000 as there is no mainstream support for it see http://blogs.msdn.com/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx For moving the database look at http://support.microsoft.com/kb/224071, make sure that you move logins, jobs, packages etc as well. If you look in Books online or at http://msdn.microsoft.com/en-us/library/ms189799.aspx you'll find the INSERTED and DELETED logical (conceptual) tables, so you will need to add the sum of the products of Cost and Qry from the INSERTED logical table to the current total and substract the sum of the product from the DELETED logical table grouping by the key columns. Post DDL and example data if you want an example. John
From: Hugo Kornelis on 1 Mar 2010 19:28 On Mon, 1 Mar 2010 13:00:52 -0700, Marian Henna wrote: >2) We have a column in a table that we need to update via a calculation of >two fields. So we need to do something like this: > >--->>> WO_Total = WO_Item_Cost * WO_Item_QTY. > >Not sure how to put this in to a trigger. We do this fine in MySQL, but >can't seem to figure out how to make this trigger. Hi Marian, No need for a trigger, just create a computed column in the table: CREATE TABLE Demo (PrimKey int NOT NULL PRIMARY KEY, Col1 int NOT NULL, Col2 int NOT NULL, Col3 AS Col1 * Col2); INSERT INTO Demo (PrimKey, Col1, Col2) VALUES (1, 2, 3); SELECT PrimKey, Col1, Col2, Col3 FROM Demo; DROP TABLE Demo; -- Hugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Marian Henna on 1 Mar 2010 22:36 Thank you John. The reason is that my company owns SQL 2000 so we want to do this enterimly. We are going to be evaluating SQL2K5 and SQL2K8. We obviously want to make the right decision as we are having some serious drawbacks with MySQL. So, for the short term we were thinking SQL2K now, then take a few courses, get the right SQL consultant to assist to make the best move in about 3 months. Thanks again for the information and links. Also, the gear we have is not up to the SQL2K5/8 minimum requirements, so we will be investing in that as well. "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:drboo5521isarbvvf2adf6a14q953ib8q3(a)4ax.com... > On Mon, 1 Mar 2010 13:00:52 -0700, "Marian Henna" > <marion.henna(a)yacobe.net> wrote: > >>Hello, I am new to MS SQL 2K, migrating from MySQL. >> >>First of all, this company I just started with has an MS SQL2K database >>sitting on a windows xp pro machine. We are moving it to MS Sever instead, >>don't know why it was on XP Pro - bad decision we think. >> >>Second, in MS SQL we need to update a field during an insert event. So my >>two questions are: >> >>1) If we run the script to create the DB on the Server, can we drop the DB >>and load the original DB from the XP machine? I think this is the correct >>terminology. >>2) We have a column in a table that we need to update via a calculation of >>two fields. So we need to do something like this: >> >>--->>> WO_Total = WO_Item_Cost * WO_Item_QTY. >> >>Not sure how to put this in to a trigger. We do this fine in MySQL, but >>can't seem to figure out how to make this trigger. >> >>Thanks, and if I'm not clear enough I certainly can provide more detail. >> > > Hi > > I would not move to SQL 2000 as there is no mainstream support for it > see > http://blogs.msdn.com/sqlreleaseservices/archive/2008/02/15/end-of-mainstream-support-for-sql-server-2005-sp1-and-sql-server-2000-sp4.aspx > > For moving the database look at > http://support.microsoft.com/kb/224071, make sure that you move > logins, jobs, packages etc as well. > > If you look in Books online or at > http://msdn.microsoft.com/en-us/library/ms189799.aspx > you'll find the INSERTED and DELETED logical (conceptual) tables, so > you will need to add the sum of the products of Cost and Qry from the > INSERTED logical table to the current total and substract the sum of > the product from the DELETED logical table grouping by the key > columns. Post DDL and example data if you want an example. > > John > >
From: Maura Jenkins on 1 Mar 2010 23:32 "Marian Henna" <marion.henna(a)yacobe.net> wrote in message news:OJqKpnXuKHA.5940(a)TK2MSFTNGP02.phx.gbl... > 1) If we run the script to create the DB on the Server, can we drop the DB > and load the original DB from the XP machine? I think this is the correct > terminology. this is not too clear; drop what DB and run what script? what you should be able to do is detach the DB on the XP machine, copy it to the second machine and then attach it there ... haven't done one in a while so I'm short on details here ... point is that it's not too difficult .. but there are some potential gotchas if your FROM and TO server configurations vary from one another > 2) We have a column in a table that we need to update via a calculation of > two fields. So we need to do something like this: > > --->>> WO_Total = WO_Item_Cost * WO_Item_QTY. assume you have an identity column "id" as your primary key; your trigger can be this simple: CREATE TRIGGER calc ON yourTable FOR INSERT AS UPDATE yourTable SET WO_Total = WO_Item_Cost * WO_Item_QTY WHERE id = @@IDENTITY you don't have to have an IDENTITY column; but you will need some predicate for the WHERE that updates the current row only another example for WHERE: WHERE emp_id = inserted.emp_id
|
Next
|
Last
Pages: 1 2 3 Prev: FTS: can I find texts not containing a specified word? Next: maintenance plan log files |