Search Results

Search found 49224 results on 1969 pages for 'database mirroring sql se'.

Page 414/1969 | < Previous Page | 410 411 412 413 414 415 416 417 418 419 420 421  | Next Page >

• Further filter SQL results

  - by eric

  I've got a query that returns a proper result set, using SQL 2005. It is as follows: select case when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2' else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) end as [Quarter], bugtypes.bugtypename, count(bug.bugid) as [Total] from bug left outer join bugtypes on bug.crntbugtypeid = bugtypes.bugtypeid and bug.projectid = bugtypes.projectid where (bug.projectid = 44 and bug.currentowner in (-1000000031,-1000000045) and bug.crntplatformid in (42,37,25,14)) or (bug.projectid = 44 and bug.currentowner in (select memberid from groupmembers where projectid = 44 and groupid in (87,88)) and bug.crntplatformid in (42,37,25,14)) group by case when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2' else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) end, bugtypes.bugtypename order by 1,3 desc It produces a nicely grouped list of years and quarters, an associated descriptor, and a count of incidents in descending count order. What I'd like to do is further filter this so it shows only the 10 most submitted incidents per quarter. What I'm struggling with is how to take this result set and achieve that.

  Read the article

• Parallelizing L2S Entity Retrieval

  - by MarkB

  Assuming a typical domain entity approach with SQL Server and a dbml/L2S DAL with a logic layer on top of that: In situations where lazy loading is not an option, I have settled on a convention where getting a list of entities does not also get each item's child entities (no loading), but getting a single entity does (eager loading). Since getting a single entity also gets children, it causes a cascading effect in which each child then gets its children too. This sounds bad, but as long as the model is not too deep, I usually don't see performance problems that outweigh the benefits of the ease of use. So if I want to get a list in which each of the items is fully hydrated with children, I combine the GetList and GetItem methods. So I'll get a list and then loop through it getting each item with the full cascade. Even this is generally acceptable in many of the projects I've worked on - but I have recently encountered situations with larger models and/or more data in which it needs to be more efficient. I've found that partitioning the loop and executing it on multiple threads yields excellent results. In my first experiment with a list of 50 items from one particular project, I did 5 threads of 10 items each and got a 3X improvement in time. Of course, the mileage will vary depending on the project but all else being equal this is clearly a big opportunity. However, before I go further, I was wondering what others have done that have already been through this. What are some good approaches to parallelizing this type of thing?

  Read the article

• Replace beginning words(SQL SERVER 2005, SET BASED)

  - by Newbie

  I have the below tables. tblInput Id WordPosition Words -- ----------- ----- 1 1 Hi 1 2 How 1 3 are 1 4 you 2 1 Ok 2 2 This 2 3 is 2 4 me tblReplacement Id ReplacementWords --- ---------------- 1 Hi 2 are 3 Ok 4 This The tblInput holds the list of words while the tblReplacement hold the words that we need to search in the tblInput and if a match is found then we need to replace those. But the problem is that, we need to replace those words if any match is found at the beginning. i.e. in the tblInput, in case of ID 1, the words that will be replaced is only 'Hi' and not 'are' since before 'are', 'How' is there and it is not in the tblReplacement list. in case of Id 2, the words that will be replaced are 'Ok' & 'This'. Since these both words are present in the tblReplacement table and after the first word i.e. 'Ok' is replaced, the second word which is 'This' here comes first in the list of ID category 2 . Since it is available in the tblReplacement, and is the first word now, so this will also be replaced. So the desired output will be Id NewWordsAfterReplacement --- ------------------------ 1 How 1 are 1 you 2 is 2 me My approach so far: ;With Cte1 As( Select t1.Id ,t1.Words ,t2.ReplacementWords From tblInput t1 Cross Join tblReplacement t2) ,Cte2 As( Select Id, NewWordsAfterReplacement = REPLACE(Words,ReplacementWords,'') From Cte1) Select * from Cte2 where NewWordsAfterReplacement <> '' But I am not getting the desired output. It is replacing all the matching words. Urgent help needed*.( SET BASED )* I am using SQL SERVER 2005. Thanks

  Read the article

• Stored procedure or function expects parameter which is not supplied

  - by user2920046

  I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied. My stored procedure is called "SHOWuser". I have checked it thoroughly and no parameters is missing. My code is: public void SHOWuser(string userName, string password, string emailAddress, List preferences) { SqlConnection dbcon = new SqlConnection(conn); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = dbcon; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "SHOWuser"; cmd.Parameters.AddWithValue("@userName", userName); cmd.Parameters.AddWithValue("@password", password); cmd.Parameters.AddWithValue("@emailAddress", emailAddress); dbcon.Open(); int i = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Parameters.Clear(); cmd.CommandText = "tbl_pref"; foreach (int preference in preferences) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i)); cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference)); cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } finally { dbcon.Close(); } and the stored procedure is: ALTER PROCEDURE [dbo].[SHOWuser] -- Add the parameters for the stored procedure here ( @userName varchar(50), @password nvarchar(50), @emailAddress nvarchar(50) ) AS BEGIN INSERT INTO tbl_user(userName,password,emailAddress) values(@userName,@password,@emailAddress) select tbl_user.userID,tbl_user.userName,tbl_user.password,tbl_user.emailAddress, stuff((select ',' + preferenceName from tbl_pref_master inner join tbl_preferences on tbl_pref_master.preferenceID = tbl_preferences.preferenceID where tbl_preferences.userID=tbl_user.userID FOR XML PATH ('')),1,1,' ' ) AS Preferences from tbl_user SELECT SCOPE_IDENTITY(); END Pls help, Thankx in advance...

  Read the article

• T-SQL UPDATE trigger help

  - by Tan

  Hi iam trying to make an update trigger in my database. But i get this error every time the triggers trigs. Error MEssage: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3rows) and heres my trigger ALTER TRIGGER [dbo].[x1pk_qp_update] ON [dbo].[x1pk] FOR UPDATE AS BEGIN TRY DECLARE @UserId int , @PackareKod int , @PersSign varchar(10) SELECT @PackareKod = q_packarekod , @PersSign = q_perssign FROM INSERTED IF @PersSign IS NOT NULL BEGIN IF EXISTS (SELECT * FROM [QPMardskog].[dbo].[UserAccount] WHERE [Account] = @PackareKod) BEGIN SET @UserId = (SELECT [UserId] FROM [QPMardskog].[dbo].[UserAccount] WHERE [Account] = @PackareKod) UPDATE [QPMardskog].[dbo].[UserAccount] SET [Active] = 1 WHERE [Account] = @PackareKod UPDATE [QPMardskog].[dbo].[User] SET [Active] = 1 WHERE [Id] = @UserId END END END TRY But i only update one row in the table how can it says 3 rows. Please advise.

  Read the article

• sql queries slower than expected

  - by neubert

  Before I show the query here are the relevant table definitions: CREATE TABLE phpbb_posts ( topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, poster_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, KEY topic_id (topic_id), KEY poster_id (poster_id), ); CREATE TABLE phpbb_topics ( topic_id mediumint(8) UNSIGNED NOT NULL auto_increment ); Here's the query I'm trying to do: SELECT p.topic_id, p.poster_id FROM phpbb_topics AS t LEFT JOIN phpbb_posts AS p ON p.topic_id = t.topic_id AND p.poster_id <> ... WHERE p.poster_id IS NULL; Basically, the query is an attempt to find all topics where the number of times someone other than the target user has posted in is zero. In other words, the topics where the only person who has posted is the target user. Problem is that query is taking a super long time. My general assumption when it comes to SQL is that JOINs of any are super fast and can be done in no time at all assuming all relevant columns are primary or foreign keys (which in this case they are). I tried out a few other queries: SELECT COUNT(1) FROM phpbb_topics AS t JOIN phpbb_posts AS p ON p.topic_id = t.topic_id; That returns 353340 pretty quickly. I then do these: SELECT COUNT(1) FROM phpbb_topics AS t JOIN phpbb_posts AS p ON p.topic_id = t.topic_id AND p.poster_id <> 77198; SELECT COUNT(1) FROM phpbb_topics AS t JOIN phpbb_posts AS p ON p.topic_id = t.topic_id WHERE p.poster_id <> 77198; And both of those take quite a while (between 15-30 seconds). If I change the < to a = it takes no time at all. Am I making some incorrect assumptions? Maybe my DB is just foobar'd?

  Read the article

• SQL Server 2008 Prior String Extract

  - by Saidur Rahman

  I have strings like the ones below in a SQL column. I want to extract them as a Gigabyte amount in aggregate. Example: Original Column ---------> Expected Output from a TSQL function ------------------------------------------- $15 / 1GB 24m + Intern 120MB ----------> 1.12 GB $19.95 / 500MB + $49.95 / 9GB Blackberry -----> 9.5GB $174.95 Blackberry 24GB + $10 / 1GB Datapack ----> 25GB $79 / 6GB --> 6GB Null --> Null $20 Plan --> 0GB Note: for our purpose, 1000MB = 1 GB (not 1024). The pattern is numbers followed by GB/MB, usually they are combined like 1GB (without any space but may sometimes may contain a space, it is not particularly important if hard to implement for this exception). Sometimes there are up to three or four instances of GB/MB occurring in the same string which are usually separated by a + sign (see row 2 and 3 of my example above). I have seen how we extract the dollar values in one of the answers where numbers were followed by $ or extract all integers in a string but I don't want to extract the dollar values or all the integers in a string. I just want the sum of GB/MB in the string.

  Read the article

• Error in My Add button SQL Server Management Studio And Visual Basic 2010

  - by user2882523

  Here is the thing i cant use insert querry in my code there is an error in my sqlcommand that says the ExecuteNonQuery() not match with the values blah blah here is my code Dim con As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDBFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Finals.mdf;Database=Finals;Trusted_Connection=Yes;") Dim cmd As New SqlClient.SqlCommand cmd.Connection = con cmd.CommandText = "Insert Into [Finals].[dbo].[Nokia] Values ('" & Unit.Text & "'),('" & Price.Text & " '),('" & Stack.Text & "'),('" & Processor.Text & "'),('" & Size.Text & "'),('" & RAM.Text & "'),('" & Internal.Text & "'),('" & ComboBox1.Text & "')" con.Open() cmd.ExecuteNonQuery() con.Close() } the problem is the cmd.CommandText can anyone pls help me

  Read the article

• Create a complex SQL query?

  - by mazzzzz

  Hey guys, I have a program that allows me to run queries against a large database. I have two tables that are important right now, Deposits and withdraws. Each contains a history of every user. I need to take each table, add up every deposit and withdraws (per user), then subtract the withdraws from the deposits. I then need to return every user whos result is negative (aka they withdrew more then they deposited). Is this possible in one query? Example: Deposit Table: |ID|UserName|Amount| |1 | Use1 |100.00| |2 | Use1 |50.00 | |3 | Use2 |25.00 | |4 | Use1 | 5.00 | WithDraw Table: |ID|UserName|Amount| |2 | Use2 | 5.00 | |1 | Use1 |100.00| |4 | Use1 | 5.00 | |3 | Use2 |25.00 | So then the result would output: |OverWithdrawers| | Use2 | Is this possible (I sure don't know how to do it)? Thanks for any help, Max

  Read the article

• Fetch last item in a category that fits specific criteria

  - by Franz

  Let's assume I have a database with two tables: categories and articles. Every article belongs to a category. Now, let's assume I want to fetch the latest article of each category that fits a specific criteria (read: the article does). If it weren't for that extra criteria, I could just add a column called last_article_id or something similar to the categories table - even though that wouldn't be properly normalized. How can I do this though? I assume there's something using GROUP BY and HAVING?

  Read the article

• LINQ To SQL Dynamic Select

  - by mcass20

  Can someone show me how to indicate which columns I would like returned at run-time from a LINQ To SQL statement? I am allowing the user to select items in a checkboxlist representing the columns they would like displayed in a gridview that is bound to the results of a L2S query. I am able to dynamically generate the WHERE clause but am unable to do the same with the SELECT piece. Here is a sample: var query = from log in context.Logs select log; query = query.Where(Log => Log.Timestamp > CustomReport.ReportDateStart); query = query.Where(Log => Log.Timestamp < CustomReport.ReportDateEnd); query = query.Where(Log => Log.ProcessName == CustomReport.ProcessName); foreach (Pair filter in CustomReport.ExtColsToFilter) { sExtFilters = "<key>" + filter.First + "</key><value>" + filter.Second + "</value>"; query = query.Where(Log => Log.FormattedMessage.Contains(sExtFilters)); }

  Read the article

• SQL Standard Regarding Left Outer Join and Where Conditions

  - by Ryan

  I am getting different results based on a filter condition in a query based on where I place the filter condition. My questions are: Is there a technical difference between these queries? Is there anything in the SQL standard that explains the different resultsets in the queries? Given the simplified scenario: --Table: Parent Columns: ID, Name, Description --Table: Child Columns: ID, ParentID, Name, Description --Query 1 SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description FROM Parent p LEFT OUTER JOIN Child c ON (p.ID = c.ParentID) WHERE c.ID IS NULL OR c.Description = 'FilterCondition' --Query 2 SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description FROM Parent p LEFT OUTER JOIN Child c ON (p.ID = c.ParentID AND c.Description = 'FilterCondition') I assumed the queries would return the same resultsets and I was surprised when they didn't. I am using MS SQL2005 and in the actual queries, query 1 returned ~700 rows and query 2 returned ~1100 rows and I couldn't detect a pattern on which rows were returned and which rows were excluded. There were still many rows in query 1 with child rows with data and NULL data. I prefer the style of query 2 (and I think it is more optimal), but I thought the queries would return the same results.

  Read the article

• Forcing LINQ to SQL to make one single call for all child rows

  - by zaph0d

  Let say I have a method (example taken from another post): public IQueryable<CityBlock> GetCityBlocks(){ var results = from o in db.city_blocks let buildings = GetBuildingsOnBlock(o.block_id) //returns Iqueryable select new CityBlock { BuildingsOnBlock = buildings, BlockOwner = o.block_owner }; return results; } In the calling method I add Skip() and Take() methods plus some filtering and then do a ToList(). The trouble is that I am getting dozens of database calls - one for all the city blocks and then a separate one for each building. Is there a way that I can refactor this code to just make two calls: one for the city blocks and one for all the buildings

  Read the article

• Relating categories with tags using SQL

  - by Pablo

  I want be able to find tags of items under the a certain category. Following is example of my database design: images +----------+-----+-------------+-----+ | image_id | ... | category_id | ... | +----------+-----+-------------+-----+ | 1 | ... | 11 | ... | +----------+-----+-------------+-----+ | 2 | ... | 12 | ... | +----------+-----+-------------+-----+ | 3 | ... | 11 | ... | +----------+-----+-------------+-----+ | 4 | ... | 11 | ... | +----------+-----+-------------+-----+ images_tags +----------+--------+ | image_id | tag_id | +----------+--------+ | 1 | 53 | +----------+--------+ | 3 | 54 | +----------+--------+ | 2 | 55 | +----------+--------+ | 1 | 56 | +----------+--------+ | 4 | 57 | +----------+--------+ tags and categories each have their own table relating the id to an actual name(text). So my question is how will i find out that images with category_id=11 have have the tag_id 53 54 55 56 57. In other words how to find the tags that images in certain category have?

  Read the article

• Help with SQL query

  - by user154301

  Hello, I have list of DateTime values, and for each value I need to fetch something from the database. I would like to do this with one query. I know it's possible to pass a table (list) to the stored procedure, but Im not sure how to write the query itself. Let's say I have the following table: CREATE TABLE Shows( ShowId [int] NOT NULL, StartTime DateTime NOT NULL, EndTime DateTime NOT NULL ) and an array of dates DECLARE @myDateArray MyCustomDateArrayType Now, if I were fetching a single item, I would write a query like this: SELECT * FROM Shows WHERE StartTime > @ArrayItem and @ArrayItem < EndTime where @ArrayItem is an item from @myDateArray . But how do I formulate the query that would fetch the information for all array items? Thanks!

  Read the article

• SSIS Migration - Pulling IDs from dest DB?

  - by TheSciz

  So I'm working on migrating some data to a new server. In the new server, each entry in the MAIN table is assigned a new GUID when the transfer takes place. A few other tables must be migrated, and their records must link to the GUID in the MAIN table. Example... WorksheetID --- GUID 1245677903 --- 1 AccidentID --- WorksheetID --- Guid 12121412 --- 1245677903 --- 1 The guid is used moreso for versioning purposes, but my question is this. In SSIS, is there any way to pull the Worksheet's GUID from the destination database and assign it directly to the entries in the 'Accident' table? Or do I have to just dump the data into the source DB and run some scripts to get everything nicely referenced? Any help would be greatly appreciated.

  Read the article

• SQL Server insert performance with and without primary key

  - by Eric

  Summary: I have a table populated via the following: insert into the_table (...) select ... from some_other_table Running the above query with no primary key on the_table is ~15x faster than running it with a primary key, and I don't understand why. The details: I think this is best explained through code examples. I have a table: create table the_table ( a int not null, b smallint not null, c tinyint not null ); If I add a primary key, this insert query is terribly slow: alter table the_table add constraint PK_the_table primary key(a, b); -- Inserting ~880,000 rows insert into the_table (a,b,c) select a,b,c from some_view; Without the primary key, the same insert query is about 15x faster. However, after populating the_table without a primary key, I can add the primary key constraint and that only takes a few seconds. This one really makes no sense to me. More info: The estimated execution plan shows 0% total query time spent on the clustered index insert SQL Server 2008 R2 Developer edition, 10.50.1600 Any ideas?

  Read the article

• How can I treat a sequence value like a generated key?

  - by Jeff Knecht

  Here is my situation and my constraints: I am using Java 5, JDBC, and DB2 9.5 My database table contains a BIGINT value which represents the primary key. For various reasons that are too complicated to go into here, the way I insert records into the table is by executing an insert against a VIEW; an INSTEAD OF trigger retrieves the NEXT_VAL from a SEQUENCE and performs the INSERT into the target table. I can change the triggers, but I cannot change the underlying table or the general approach of inserting through the view. I want to retrieve the sequence value from JDBC as if it were a generated key. Question: How can I get access to the value pulled from the SEQUENCE. Is there some message I can fire within DB2 to float this sequence value back to the JDBC driver?

  Read the article

• Run a proc on several different values of one parameter

  - by WEFX

  I have the following query that gets run within a proc. The function MyFunction returns a table, and this query joins on that table. This proc works great when a @MyArg value is supplied. However, I’m wondering if there’s a way to run this on all @MyArg values in the database. I’m sure there’s a way to do it within a loop, but I know that loops are generally to be avoided at the db layer. I really just need to perform this for the sake of checking (and possibly cleansing) some bad data. SELECT ColumnA, ColumnB, ColumnC FROM ( SELECT a.ColumnA, a.ColumnB, a.ColumnC, ROW_NUMBER() over(partition by a.ColumnD order by f.ColumnX) as RowNum FROM dbo.MyTableA AS a INNER JOIN dbo.MyFunction(@MyArg) f ON f.myID = a.myID WHERE (a.myBit = 1 OR a.myID = @MyArg) ) AS x WHERE x.rownum = 1;

  Read the article

• Linq to SQL Intersect help needed

  - by mohang

  Hi, I have tried various suggestions given in SO. I still did not get the answers needed. Kindly help me. I appreciate your help. I have two sets. I need help to get the linq to sql intersection done. I have two sets. IQueryable<BusinessEntity> firstSet = from ent in all entities where ... // Code to get the first set. IQueryable<BusinessEntity> secondSet = from ent in all entities where... // Code to get the second set. Now I want the intersection, that is common elements of these sets. I have tried various ways including the following and I did not get the result I wanted. Please help me to get the right result. var commonEntities = (from ent1 in firstSet from ent2 in secondSet where ent1.BusinessEntityId == ent2.BusinessEntityId select ent1);

  Read the article

• sql select from a large number of IDs

  - by Claudiu

  I have a table, Foo. I run a query on Foo to get the ids from a subset of Foo. I then want to run a more complicated set of queries, but only on those IDs. Is there an efficient way to do this? The best I can think of is creating a query such as: SELECT ... --complicated stuff WHERE ... --more stuff AND id IN (1, 2, 3, 9, 413, 4324, ..., 939393) That is, I construct a huge "IN" clause. Is this efficient? Is there a more efficient way of doing this, or is the only way to JOIN with the inital query that gets the IDs? If it helps, I'm using SQLObject to connect to a PostgreSQL database, and I have access to the cursor that executed the query to get all the IDs.

  Read the article

• problem with sql statement using vb.net

  - by newBie

  hi i got some problem i got this 3 line statement: Dim infoID As Integer = objCommand1.ExecuteScalar() Dim strSQL2 As String = "insert into feedBackHotel (infoID, feedBackView) values(" + infoID + ",'" + FeedBack + "')" Dim objCommand2 As New SqlCommand(strSQL2, conn) objCommand2.ExecuteNonQuery() the problem here is..the strSQL2 can capture the infoID from previous statement, but it didnt insert into the database. it will pop out this error "Conversion from string "insert into feedBackHotel (infoI" to type 'Double' is not valid." in both table related, use same data type (int) but for the feedBackHotel's infoID i allow it to be null..bcoz if i make it not null, it will show another error.. im using vb.net ..can anyone help?

  Read the article

• Need a field / flag / status number for mutliple use ?

  - by Jules

  I want to create a field in my database which will be easy to query. I think if I give a bit of background this will make more sense. My table has listings shown on my website. I run a program which looks at the listings a decides whether to hide them from being shown on the site. I also hide listings manually for various reasons. I want to store these reasons in a field, so more than one reason could be made for hiding. So I need some form of logic to determine which reasons have been used. Can anyone offer me any guidance on what will be future-proof aka new reasons and what will be quick and easy to query upon ?

  Read the article

• How to emulate a BEFORE DELETE trigger in SQL Server 2005

  - by Mark

  Let's say I have three tables, [ONE], [ONE_TWO], and [TWO]. [ONE_TWO] is a many-to-many join table with only [ONE_ID and [TWO_ID] columns. There are foreign keys set up to link [ONE] to [ONE_TWO] and [TWO] to [ONE_TWO]. The FKs use the ON DELETE CASCADE option so that if either a [ONE] or [TWO] record is deleted, the associated [ONE_TWO] records will be automatically deleted as well. I want to have a trigger on the [TWO] table such that when a [TWO] record is deleted, it executes a stored procedure that takes a [ONE_ID] as a parameter, passing the [ONE_ID] values that were linked to the [TWO_ID] before the delete occurred: DECLARE @Statement NVARCHAR(max) SET @Statement = '' SELECT @Statement = @Statement + N'EXEC [MyProc] ''' + CAST([one_two].[one_id] AS VARCHAR(36)) + '''; ' FROM deleted JOIN [one_two] ON deleted.[two_id] = [one_two].[two_id] EXEC (@Statement) Clearly, I need a BEFORE DELETE trigger, but there is no such thing in SQL Server 2005. I can't use an INSTEAD OF trigger because of the cascading FK. I get the impression that if I use a FOR DELETE trigger, when I join [deleted] to [ONE_TWO] to find the list of [ONE_ID] values, the FK cascade will have already deleted the associated [ONE_TWO] records so I will never find any [ONE_ID] values. Is this true? If so, how can I achieve my objective? I'm thinking that I'd need to change the FK joining [TWO] to [ONE_TWO] to not use cascades and to do the delete from [ONE_TWO] manually in the trigger just before I manually delete the [TWO] records. But I'd rather not go through all that if there is a simpler way.

  Read the article

• Processing a resultset to look up foriegn keys (and poulate a new table!)

  - by Gilly

  Hi, I've been handed a dataset that has some fairly basic table structures with no keys at all. eg {myRubishTable} - Area(varchar),AuthorityName(varchar),StartYear(varchar),StartMonth(varcha),EndYear(varchar),EndMonth(varchar),Amount(Money) there are other tables that use the Area and AuthorityName columns as well as a general use of Month and Years so I I figured a good first step was to pull Area and Authority into their own tables. I now want to process the data in the original table and lookup the key value to put into my new table with foreign keys which looks like this. (lookup Tables) {Area} - id (int, PK), name (varchar(50)) {AuthorityName} - id(int, PK), name(varchar(50) (TargetTable) {myBetterTable} - id (int,PK), area_id(int FK-Area),authority_name_id(int FK-AuthorityName),StartYear (varchar),StartMonth(varchar),EndYear(varchar),EndMonth(varchar),Amount(money) so row one in the old table read MYAREA, MYAUTHORITY,2009,Jan,2010,Feb,10000 and I want to populate the new table with 1,1,1,2009,Jan,2010,Feb,10000 where the first '1' is the primary key and the second two '1's are the ids in the lookup tables. Can anyone point me to the most efficient way of achieving this using just SQL? Thanks in advance Footnote:- I've achieved what I needed with some pretty simple WHERE clauses (I had left a rogue tablename in the FROM which was throwing me :o( ) but would be interested to know if this is the most efficient. ie SELECT [area].[area_id], [authority].[authority_name_id], [myRubishTable].[StartYear], [myRubishTable].[StartMonth], [myRubishTable].[EndYear], [myRubishTable].[EndMonth], [myRubishTable].[Amount] FROM [myRubishTable],[Area],[AuthorityName] WHERE [myRubishTable].[Area]=[Area].[name] AND [myRubishTable].[Authority Name]=[dim_AuthorityName].[name] TIA

  Read the article

< Previous Page | 410 411 412 413 414 415 416 417 418 419 420 421  | Next Page >