insert query - Page 22 - Developer IT

Tricky SQL query involving consecutive values

- by Gabriel

I need to perform a relatively easy to explain but (given my somewhat limited skills) hard to write SQL query. Assume we have a table similar to this one: exam_no | name | surname | result | date ---------+------+---------+--------+------------ 1 | John | Doe | PASS | 2012-01-01 1 | Ryan | Smith | FAIL | 2012-01-02 <-- 1 | Ann | Evans | PASS | 2012-01-03 1 | Mary | Lee | FAIL | 2012-01-04 ... | ... | ... | ... | ... 2 | John | Doe | FAIL | 2012-02-01 <-- 2 | Ryan | Smith | FAIL | 2012-02-02 2 | Ann | Evans | FAIL | 2012-02-03 2 | Mary | Lee | PASS | 2012-02-04 ... | ... | ... | ... | ... 3 | John | Doe | FAIL | 2012-03-01 3 | Ryan | Smith | FAIL | 2012-03-02 3 | Ann | Evans | PASS | 2012-03-03 3 | Mary | Lee | FAIL | 2012-03-04 <-- Note that exam_no and date aren't necessarily related as one might expect from the kind of example I chose. Now, the query that I need to do is as follows: From the latest exam (exam_no = 3) find all the students that have failed (John Doe, Ryan Smith and Mary Lee). For each of these students find the date of the first of the batch of consecutively failing exams. Another way to put it would be: for each of these students find the date of the first failing exam that comes after their last passing exam. (Look at the arrows in the table). The resulting table should be something like this: name | surname | date_since_failing ------+---------+-------------------- John | Doe | 2012-02-01 Ryan | Smith | 2012-01-02 Mary | Lee | 2012-01-04 Ann | Evans | 2012-02-03 How can I perform such a query? Thank you for your time.

Read the article

Data in two databases, eager spool resulting in query

- by Valkyrie

I have two databases in SQL2k5: one that holds a large amount of static data (SQL Database 1) (never updated but frequently inserted into) and one that holds relational data (SQL Database 2) related to the static data. They're separated mainly because of corporate guidelines and business requirements: assume for the following problem that combining them is not practical. There are places in SQLDB2 that PKs in SQLDB1 are referenced; triggers control the referential integrity, since cross-database relationships are troublesome in SQL Server. BUT, because of the large amount of data in SQLDB1, I'm getting eager spools on queries that join from the Id in SQLDB2 that references the data in SQLDB1. (With me so far? Maybe an example will help:) SELECT t.Id, t.Name, t2.Company FROM SQLDB1.table t INNER JOIN SQLDB2.table t2 ON t.Id = t2.FKId This query results in a eager spool that's 84% of the load of the query; the table in SQLDB1 has 35M rows, so it's completely choking this query. I can't create a view on the table in SQLDB1 and use that as my FK/index; it doesn't want me to create a constraint based on a view. Anyone have any idea how I can fix this huge bottleneck? (Short of putting the static data in the first db: believe me, I've argued that one until I'm blue in the face to no avail.) Thanks! valkyrie Edit: also can't create an indexed view because you can't put schemabinding on a view that references a table outside the database where the view resides. Dang it.

Read the article

Zend Framework how to echo value of SUM query

- by Rick de Graaf

Hello, I created a query for the zend framework, in which I try to retrieve the sum of a column, in this case the column named 'time'. This is the query I use: $this->timequery = $this->db_tasks->fetchAll($this->db_tasks->select()->from('tasks', 'SUM(time)')->where('projectnumber =' . $this->value_project)); $this->view->sumtime = $this->timequery; Echoing the query tells me this is right. But I can't echo the result properly. Currently I'm using: echo $this->sumtime['SUM(time)']; Returning the following error: Catchable fatal error: Object of class Zend_Db_Table_Row could not be converted to string in C:\xampp\htdocs\BManagement\application\views\scripts\tasks\index.phtml on line 46 Line 46 being the line with the echo in my view. I've been searching now for two days on how to figure this out, or achieve the same result in a different way. Tried to serialize the value, but that didn't work either. Is there somebody who knows how to achieve the total sum of a database column? Any help is greatly appriciated! note: Pretty new to zend framework...

Read the article

How to make comment reply query in MYSQL?

- by Prashant

I am having comment reply (only till one level) functionality. All comments can have as many as replies but no replies can have their further replies. So my database table structure is like below Id ParentId Comment 1 0 this is come sample comment text 2 0 this is come sample comment text 3 0 this is come sample comment text 4 1 this is come sample comment text 5 0 this is come sample comment text 6 3 this is come sample comment text 7 1 this is come sample comment text In the above structures, commentid, 1 (has 2 replies) and 3 (1 reply) has replies. So to fetch the comments and their replies, one simple method is first I fetch all the comments having ParentId as 0 and then by running a while loop fetch all the replies of that particular commentId. But that seems to be running hundreds of queries if I'll have around 200 comments on a particular record. So I want to make a query which will fetch Comments with their replies sequentially as following; Id ParentId Comment 1 0 this is come sample comment text 4 1 this is come sample comment text 7 1 this is come sample comment text 2 0 this is come sample comment text 3 0 this is come sample comment text 6 3 this is come sample comment text 5 0 this is come sample comment text I also have a comment date column in my comment table, if anyone wants to use this with comment query. So finally I want to fetch all the comments and their replies by using one single mysql query. Please tell me how I can do that? Thanks

Read the article

Ldap query returns null result when deployed.

- by Trey Carroll

I'm using a very simple Ldap query in my asp.net mvc 2.0 site: String ldapPath = ConfigReader.LdapPath; String emailAddress = null; try { DirectorySearcher search = new DirectorySearcher(ConfigReader.LdapPath); search.Filter = String.Format("(&(objectClass=user)(objectCategory=person)(objectSid={0})) ", securityIdentifierValue); // add the mail property to the list of props to retrieve search.PropertiesToLoad.Add("mail"); var result = search.FindOne(); if (result == null) { throw new Exception("Ldap Query with filter:" + search.Filter.ToString() + " returned a null value (no match found)"); } else { emailAddress = result.Properties["mail"][0].ToString(); } } catch (ArgumentOutOfRangeException aoorEx) { throw new Exception( "The query could not find an email for this user."); } catch (Exception ex) { //_log.Error(string.Format("======!!!!!! ERROR ERROR ERROR !!!!! in LdapLookupUtil.cs getEmailFromLdap Exception: {0}", ex)); throw ex; } return emailAddress; It works fine on my localhost machine. It works fine when I run it in VS2010 on the server. It always returns a null result when deployed. Here is my web.config: Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in \Windows\Microsoft.Net\Framework\v2.x\Config -- section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. -- <!-- -- section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. -- I'm running it under the default app pool. Does anybody see the problem? This is driving me crazy!

Read the article

Where clause in joins vs Where clause in Sub Query

- by Kanavi

DDL create table t ( id int Identity(1,1), nam varchar(100) ) create table t1 ( id int Identity(1,1), nam varchar(100) ) DML Insert into t( nam)values( 'a') Insert into t( nam)values( 'b') Insert into t( nam)values( 'c') Insert into t( nam)values( 'd') Insert into t( nam)values( 'e') Insert into t( nam)values( 'f') Insert into t1( nam)values( 'aa') Insert into t1( nam)values( 'bb') Insert into t1( nam)values( 'cc') Insert into t1( nam)values( 'dd') Insert into t1( nam)values( 'ee') Insert into t1( nam)values( 'ff') Query - 1 Select t.*, t1.* From t t Inner join t1 t1 on t.id = t1.id Where t.id = 1 Query 1 SQL profiler Result Reads = 56, Duration = 4 Query - 2 Select T1.*, K.* from ( Select id, nam from t Where id = 1 )K Inner Join t1 T1 on T1.id = K.id Query 2 SQL Profiler Results Reads = 262 and Duration = 2 You can also see my SQlFiddle Query - Which query should be used and why?

Read the article

Erlang ODBC parameter query with null parameters

- by Schlomer

Is it possible to pass null values to parameter queries? For example Sql = "insert into TableX values (?,?)". Params = [{sql_integer, [Val1]}, {sql_float, [Val2]}]. % Val2 may be a float, or it may be the atom, undefined odbc:param_query(OdbcRef, Sql, Params). Now, of course odbc:param_query/3 is going to complain if Val2 is undefined when trying to match to a sql_float, but my question is... Is it possible to use a parameterized query, such as: Sql = "insert into TableY values (?,?,?,?,?,?,?,?,?)". with any null parameters? I have a use case where I am dumping a large number of real-time data into a database by either inserting or updating. Some of the tables I am updating have a dozen or so nullable fields, and I do not have a guarantee that all of the data will be there. Concatenating a SQL together for each query, checking for null values seems complex, and the wrong way to do it. Having a parameterized query for each permutation is simply not an option. Any thoughts or ideas would be fantastic! Thank you!

Read the article

SQL Query to duplicate records based on If statement

- by user328371

Hi, I'm trying to write an SQL query that will duplicate records depending on a field in another table. I am running mySQL 5. (I know duplicating records shows that the database structure is bad, but I did not design the database and am not in a position to redo it all - it's a shopp ecommerce database running on wordpress.) Each product with a particular attribute needs a link to the same few images, so the product will need a row per image in a table - the database doesn't actually contain the image, just its filename. (the images are of clipart for a customer to select from) Based on these records... SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes' I want to do something like this.. For each record that matches that query, copy records 5134 - 5139 from wp_shopp_asset but change the id so it's unique and set the cell in column 'parent' to have the value of 'product' from the table wp_shopp_spec. This will mean 6 new records are created for each record matching the above query, all with the same value in 'parent' but with unique ids and every other column copied from the original (ie. records 5134-5139) Hope that's clear enough - any help greatly appreciated.

Read the article

Trouble creating a SQL query

- by JoBu1324

I've been thinking about how to compose this SQL query for a while now, but after thinking about it for a few hours I thought I'd ask the SO community to see if they have any ideas. Here is a mock up of the relevant portion of the tables: contracts id date ar (yes/no) term payments contract_id payment_date The object of the query is to determine, per month, how many payments we expect, vs how many payments we received. conditions for expecting a payment Expected payments begin on contracts.term months after contracts.date, if contracts.ar is "yes". Payments continue to be expected until the month after the first missed payment. There is one other complication to this: payments might be late, but they need to show up as if they were paid on the date expected. The data is all there, but I've been having trouble wrapping my head around the SQL query. I am not an SQL guru - I merely have a decent amount of experience handling simpler queries. I'd like to avoid filtering the results in code, if possible - but without your help that may be what I have to do. Expected Output Month Expected Payments Received Payments January 500 450 February 498 478 March 234 211 April 987 789 ... SQL Fiddle I've created an SQL Fiddle: http://sqlfiddle.com/#!2/a2c3f/2

Read the article

Strange data swapping error occurs when I attempt to update rows in my table from another table in m

- by Wesley

So I have a table of data that is 10,000 lines long. Several of the columns in the table simply describe information about one of the columns, meaning, that only one column has the content, and the rest of the columns describe the location of the content (its for a book). Right now, only 6,000 of the 10,000 rows' content column is filled with its content. Rows 6-10,000's content column simply says null. I have another table in the db that has the content for rows 6,000-10,000, with the correct corresponding primary key which would (seemingly) make it easy to update the 10,000 row table. I have been trying an update query such as the following: UPDATE table(10,000) SET content_column = (SELECT content FROM table(6,000-10,000) WHERE table(10,000).id = table(6-10,000.id) Which kind of works, the only problem is that it pulls in the data from the second table just fine, but it replaces the existing content column with null. So rows 1-6,000's content column become null, and rows 6-10,000's content column have the correct values...Pretty strange I thought anyway. Does anybody have any thoughts about where I am going wrong? If you could show me a better sql query, I would appreciate it! Thanks

Read the article

how to query an embedded entity by using a query builder

- by user577719

I've searched quite a time for an answer to this question. Following Codesmell: @Entity public class Person { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) protected Integer id; @Column(nullable = true, length = 50) @Size(max = 50) private String name; @Embedded @Valid protected Adress adress; public void setId(Integer id) { this.id = id; } public Integer getId() { return this.id; } public void setName(String name) { this.name = name; } public void getName() { return this.name; } public void setAdress(Adress adress) { this.adress = adress; } public void getAdress() { return this.adress; } } @Embeddable public class Adress { @Column(nullable = false, length = 50) @Size(max = 50) @NotNull private String place; public void setPlace(String place) { this.place = place; } public void getPlace() { return this.place; } } public class PersonDaoJpa { public List<Ort> findByPerson(final Person person) { CriteriaBuilder builder = this.entityManager.getCriteriaBuilder(); CriteriaQuery<Person> query = builder.createQuery(Person.class); Root<Person> rootPerson = query.from(Person.class); List<Predicate> wherePredicates = new ArrayList<Predicate>(); if (person.getName() != null) { wherePredicates.add( builder.like(builder.lower(rootPerson.<String>get("name")), ort.getName().toLowerCase()) ); } Adresse adresse = ort.getAdresse(); if (adresse != null) { if(adresse.getPlace() != null) { // this won't work wherePredicates.add( builder.like(builder.lower(rootPerson.<String>get("person.adress.place")), adresse.getPlace().toLowerCase()) ); } } Predicate whereClause = builder.and(wherePredicates.toArray(new Predicate[0])); query.where(whereClause); return this.entityManager.createQuery(query).getResultList(); } } How can I access the Adress.place through rootPerson? rootPerson.get("place"), or rootPerson.get("adress.place") won't work...

Read the article

Ultra-grand super acts_as_tree rails query

- by Bloudermilk

Right now I'm dealing with an issue regarding an intense acts_as_tree MySQL query via rails. The model I am querying is Foo. A Foo can belong to any one City, State or Country. My goal is to query Foos based on their location. My locations table is set up like so: I have a table in my database called locations I use a combination of acts_as_tree and polymorphic associations to store each individual location as either a City, State or Country. (This means that my table consists of the rows id, name, parent_id, type) Let's say for instance, I want to query Foos in the state "California". Beside Foos that directly belong to "California", I should get all Foos that belong every City in "California" like Foos in "Los Angeles" and "San Francisco". Not only that, but I should get any Foos that belong to the Country that "California" is in, "United States". I've tried a few things with associations to no avail. I feel like I'm missing some super-helpful Rails-fu here. Any advice?

Read the article

Why doesn't this CompiledQuery give a performance improvement?

- by Grammarian

I am trying to speed up an often used query. Using a CompiledQuery seemed to be the answer. But when I tried the compiled version, there was no difference in performance between the compiled and non-compiled versions. Can someone please tell me why using Queries.FindTradeByTradeTagCompiled is not faster than using Queries.FindTradeByTradeTag? static class Queries { // Pre-compiled query, as per http://msdn.microsoft.com/en-us/library/bb896297 private static readonly Func<MyEntities, int, IQueryable<Trade>> mCompiledFindTradeQuery = CompiledQuery.Compile<MyEntities, int, IQueryable<Trade>>( (entities, tag) => from trade in entities.TradeSet where trade.trade_tag == tag select trade); public static Trade FindTradeByTradeTagCompiled(MyEntities entities, int tag) { IQueryable<Trade> tradeQuery = mCompiledFindTradeQuery(entities, tag); return tradeQuery.FirstOrDefault(); } public static Trade FindTradeByTradeTag(MyEntities entities, int tag) { IQueryable<Trade> tradeQuery = from trade in entities.TradeSet where trade.trade_tag == tag select trade; return tradeQuery.FirstOrDefault(); } }

Read the article

Entity framework 4.0 compiled query with Where() clause issue

- by Andrey Salnikov

Hello, I encountered with some strange behavior of System.Data.Objects.CompiledQuery.Compile function - here is my code for compile simple query: private static readonly Func<DataContext, long, Product> productQuery = CompiledQuery.Compile((DataContext ctx, long id) => ctx.Entities.OfType<Data.Product>().Where(p => p.Id == id) .Select(p=>new Product{Id = p.Id}).SingleOrDefault()); where DataContext inherited from ObjectContext and Product is a projection of POCO Data.Product class. My data context in first run contains Data.Product {Id == 1L} and in second Data.Product {Id == 2L}. First using of compilled query productQuery(dataContext, 1L) works perfect - in result I have Product {Id == 1L} but second run productQuery(dataContext, 2L) always returns null, instead of context in second run contains single product with id == 2L. If I remove Where clause I will get correct product (with id == 2L). It seems that first id value caching while first run of productQuery, and therefore all further calls valid only when dataContext contains Data.Product {id==1L}. This issue can't be reproduced if I've used direct query instead of its precompiled version. Also, all tests I've performed on test mdf base using SQL Server 2008 express and Visual studio 2010 final from my ASP.net application.

Read the article

Help converting subquery to query with joins

- by Tim

I'm stuck on a query with a join. The client's site is running mysql4, so a subquery isn't an option. My attempts to rewrite using a join aren't going too well. I need to select all of the contractors listed in the contractors table who are not in the contractors2label table with a given label ID & county ID. Yet, they might be listed in contractors2label with other label and county IDs. Table: contractors cID (primary, autonumber) company (varchar) ...etc... Table: contractors2label cID labelID countyID psID This query with a subquery works: SELECT company, contractors.cID FROM contractors WHERE contractors.complete = 1 AND contractors.archived = 0 AND contractors.cID NOT IN ( SELECT contractors2label.cID FROM contractors2label WHERE labelID <> 1 AND countyID <> 1 ) I thought this query with a join would be the equivalent, but it returns no results. A manual scan of the data shows I should get 34 rows, which is what the subquery above returns. SELECT company, contractors.cID FROM contractors LEFT OUTER JOIN contractors2label ON contractors.cID = contractors2label.cID WHERE contractors.complete = 1 AND contractors.archived = 0 AND contractors2label.labelID <> 1 AND contractors2label.countyID <> 1 AND contractors2label.cID IS NULL

Read the article

MySql product\tag query optimisation - please help!

- by Nige

Read the article

please suggest mysql query for this

- by I Like PHP

I HAVE TWO TABLES shown below table_joining id join_id(PK) transfer_id(FK) unit_id transfer_date joining_date 1 j_1 t_1 u_1 2010-06-05 2010-03-05 2 j_2 t_2 u_3 2010-05-10 2010-03-10 3 j_3 t_3 u_6 2010-04-10 2010-01-01 4 j_5 NULL u_3 NULL 2010-06-05 5 j_6 NULL u_4 NULL 2010-05-05 table_transfer id transfer_id(PK) pastUnitId futureUnitId effective_transfer_date 1 t_1 u_3 u_1 2010-06-05 2 t_2 u_6 u_1 2010-05-10 3 t_3 u_5 u_3 2010-04-10 now i want to know total employee detalis( using join_id) which are currently working on unit u_3 . means i want only join_id j_1 (has transfered but effective_transfer_date is future date, right now in u_3) j_2 ( tansfered and right now in `u_3` bcoz effective_transfer_date has been passed) j_6 ( right now in `u_3` and never transfered) what i need to take care of below steps( as far as i know ) <1> first need to check from table_joining whether transfer_id is NULL or not <2> if transfer_id= is NULL then see unit_id=u_3 where joining_date <=CURDATE() ( means that person already joined u_3) <3> if transfer_id is NOT NULL then go to table_transfer using transfer_id (foreign key reference) <4> now see the effective_transfer_date regrading that transfer_id whether effective_transfer_date<=CURDATE() <5> if transfer date has been passed(means transfer has been done) then return futureUnitID otherwise return pastUnitID i used two separate query but don't know how to join those query?? for step <1 ans <2 SELECT unit_id FROM table_joining WHERE joining_date<=CURDATE() AND transfer_id IS NULL AND unit_id='u_3' for step<5 SELECT IF(effective_transfer_date <= CURDATE(),futureUnitId,pastUnitId) AS currentUnitID FROM table_transfer // here how do we select only those rows which have currentUnitID='u_3' ?? please guide me the process?? i m just confused with JOINS. i think using LEFT JOIN can return the data i need, or if we use subquery value to main query? but i m not getting how to implement ...please help me. Thanks for helping me alwayz

Read the article

Query to bring count from comma seperated Value

- by Mugil

I have Two Tables One for Storing Products and Other for Storing Orders List. CREATE TABLE ProductsList(ProductId INT NOT NULL PRIMARY KEY, ProductName VARCHAR(50)) INSERT INTO ProductsList(ProductId, ProductName) VALUES(1,'Product A'), (2,'Product B'), (3,'Product C'), (4,'Product D'), (5,'Product E'), (6,'Product F'), (7,'Product G'), (8,'Product H'), (9,'Product I'), (10,'Product J'); CREATE TABLE OrderList(OrderId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, EmailId VARCHAR(50), CSVProductIds VARCHAR(50)) SELECT * FROM OrderList INSERT INTO OrderList(EmailId, CSVProductIds) VALUES('[email protected]', '2,4,1,5,7'), ('[email protected]', '5,7,4'), ('[email protected]', '2'), ('[email protected]', '8,9'), ('[email protected]', '4,5,9'), ('[email protected]', '1,2,3'), ('[email protected]', '9,10'), ('[email protected]', '1,5'); Output ItemName NoOfOrders Product A 4 Product B 3 Product C 1 Product D 3 Product E 4 Product F 0 Product G 2 Product H 1 Product I 2 Product J 1 The Order List Stores the ItemsId as Comma separated value for every customer who places order.Like this i am having more than 40k Records in my dB table Now I am assigned with a task of creating report in which I should display Items and No of People ordered Items as Shown Below I Used Query as below in my PHP to bring the Orders One By One and storing in array. SELECT COUNT(PL.EmailId) FROM OrderList PL WHERE CSVProductIds LIKE '2' OR CSVProductIds LIKE '%,2,%' OR CSVProductIds LIKE '%,2' OR CSVProductIds LIKE '2,%'; 1.Is it possible to get the same out put by using Single Query 2.Does using a like in mysql query slows down the dB when the table has more no of records i.e 40k rows

Read the article

SQL Server-Determine which query is taking a long time to complete

- by Neil Smith

Cool little trick to determine which sql query which is taking a long time to execute, first while offending query is running from another machine do EXEC sp_who2 Locate the SPID responsible via Login, DBName and ProgramName columns, then do DBCC INPUTBUFFER (<SPID>) The offending query will be in the EventInfo column. This is a great little time saver for me, before I found out about this I used to split my concatenated query script in to multiple sql files until I located the problem query

Read the article

Lookup for data sources in a query

- by DAXShekhar

public static str lookupDatasourceOfQuery(Query _query) { Query query = _query; QueryBuildDataSource qbds; int dsIterator; Map map = new Map(Types::String, Types::String); ; for (dsIterator = 1; dsIterator <= query.dataSourceCount(); dsIterator++) { qbds = query.dataSourceNo(dsIterator); map.insert(qbds.name(), qbds.name()); } return pickList(map, "Data source", "Data sources"); }

Read the article

[php,mysql] insert only adds upto 1000 records and ignoresall records after that.

- by user560559

Hello i have a large database where the client stores personal messages and fire email notifications [if allowed by the users]. certain users have the option of sending messages to their entire network of friends. some users have over 5000 friends in their network so if they select the whole network they'll be sending messages to over 5000 friends and system will store all the messages into a table. the problem is this that it does not insert more than 1000 records and ignores all inserts after the first 1000. i have increased the packet size, bulk_insert_buffer_size but still no luck. since the system stores some of the info in another table for reports, every insert returns its new message id. due to this i can not use the "insert into table (column1,column2) values (value1,value2) , (value1,value2)....etc." table engine is innodb, mysql version is 5.1.3 and is hosted on amazon web services. all i want is to fix this issue of inserting more than 1000 records at a time. as mentioned earlier, it works fine but only up to 1000 records and simply ignores all the records after that. i'm using php foreach(){} to insert message for each friend and if email is available, send notification to the user. this foreach(){} also inserts the same record in another table [with only 3 columns] for generating reports. thank you in advance for all the help and support. WMA.

Read the article

Insert only adds upto 1000 records and ignoresall records after that.

- by user560559

i have a large database where the client stores personal messages and fire email notifications [if allowed by the users]. certain users have the option of sending messages to their entire network of friends. some users have over 5000 friends in their network so if they select the whole network they'll be sending messages to over 5000 friends and system will store all the messages into a table. the problem is this that it does not insert more than 1000 records and ignores all inserts after the first 1000. i have increased the packet size, bulk_insert_buffer_size but still no luck. since the system stores some of the info in another table for reports, every insert returns its new message id. due to this i can not use the "insert into table (column1,column2) values (value1,value2) , (value1,value2)....etc." table engine is innodb, mysql version is 5.1.3 and is hosted on amazon web services. all i want is to fix this issue of inserting more than 1000 records at a time. as mentioned earlier, it works fine but only up to 1000 records and simply ignores all the records after that. i'm using php foreach(){} to insert message for each friend and if email is available, send notification to the user. this foreach(){} also inserts the same record in another table [with only 3 columns] for generating reports.

Read the article

How do I insert XML into another XML file with XSLT?

- by Sandra

Hello, I looked at this thread to find out how to insert XML into XML with XSLT http://stackoverflow.com/questions/862954/insert-xml-node-at-a-specific-position-of-an-existing-document But I have a problem since I need to insert XML between two grand child nodes. For example I want to insert <s>...</s> between <r>...</r> and <t>...</t> in this file <root> <child1> <a>...</a> <r>...</r> <t>...</t> <z>...</z> </child1> </root> to create this file <root> <child1> <a>...</a> <r>...</r> <s>...</s> <t>...</t> <z>...</z> </child1> </root> Thanks for your help.

Read the article

SQL SERVER – Answer – Value of Identity Column after TRUNCATE command

- by pinaldave

Earlier I had one conversation with reader where I almost got a headache. I suggest all of you to read it before continuing this blog post SQL SERVER – Reseting Identity Values for All Tables. I believed that he faced this situation because he did not understand the difference between SQL SERVER – DELETE, TRUNCATE and RESEED Identity. I wrote a follow up blog post explaining the difference between them. I asked a small question in the second blog post and I received many interesting comments. Let us go over the question and its answer here one more time. Here is the scenario to set up the puzzle. Create Table with Seed Identity = 11 Insert Value and Check Seed (it will be 11) Reseed it to 1 Insert Value and Check Seed (it will be 2) TRUNCATE Table Insert Value and Check Seed (it will be 11) Let us see the T-SQL Script for the same. USE [TempDB] GO -- Create Table CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(11,1) NOT NULL, [var] [nchar](10) NULL ) ON [PRIMARY] GO -- Build sample data INSERT INTO [TestTable] VALUES ('val') GO -- Select Data SELECT * FROM [TestTable] GO -- Reseed to 1 DBCC CHECKIDENT ('TestTable', RESEED, 1) GO -- Build sample data INSERT INTO [TestTable] VALUES ('val') GO -- Select Data SELECT * FROM [TestTable] GO -- Truncate table TRUNCATE TABLE [TestTable] GO -- Build sample data INSERT INTO [TestTable] VALUES ('val') GO -- Select Data SELECT * FROM [TestTable] GO -- Question for you Here -- Clean up DROP TABLE [TestTable] GO Now let us see the output of three of the select statements. 1) First Select after create table 2) Second Select after reseed table 3) Third Select after truncate table The reason is simple: If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

Read the article

SQLAuthority News – Public Training Classes In Hyderabad 12-14 May – Microsoft SQL Server 2005/2008

- by pinaldave

After successfully delivering many corporate trainings as well as the private training Solid Quality Mentors, India is launching the Public Training in Hyderabad for SQL Server 2008 and SharePoint 2010. This is going to be one of the most unique and one-of-a-kind events in India where Solid Quality Mentors are offering public classes. I will be leading the training on Microsoft SQL Server 2005/2008 Query Optimization & Performance Tuning. This intensive, 3-day course intends to give attendees an in-depth look at Query Optimization and Performance Tuning in SQL Server 2005 and 2008. Designed to prepare SQL Server developers and administrators for a transition into SQL Server 2005 or 2008, the course covers the best practices for a variety of essential tasks in order to maximize the performance. At the end of the course, there would be daily discussions about your real-world problems and find appropriate solutions. Note: Scroll down for course fees, discount, dates and location. Do not forget to take advantage of Discount code ‘SQLAuthority‘. The training premises are very well-equipped as they will be having 1:1 computers. Every participant will be provided with printed course materials. I will pick up your entire lunch tab and we will have lots of SQL talk together. The best participant will receive a special gift at the end of the course. Even though the quality of the material to be delivered together with the course will be of extremely high standard, the course fees are set at a very moderate rate. The fee for the course is INR 14,000/person for the whole 3-day convention. At the rate of 1 USD = 44 INR, this fee converts to less than USD 300. At this rate, it is totally possible to fly from anywhere from the world to India and take the training and still save handsome pocket money. It would be even better if you register using the discount code “SQLAuthority“, for you will instantly get an INR 3000 discount, reducing the total cost of the training to INR 11,000/person for whole 3 days course. This is a onetime offer and will not be available in the future. Please note that there will be a 10.3% service tax on course fees. To register, either send an email to [email protected] or call +91 95940 43399. Feel free to drop me an email at [email protected] for any additional information and clarification. Training Date and Time: May 12-14, 2010 10 AM- 6 PM. Training Venue: Abridge Solutions, #90/B/C/3/1, Ganesh GHR & MSY Plaza, Vittalrao Nagar, Near Image Hospital, Madhapur, Hyderabad – 500 081. The details of the course is as listed below. Day 1 : Strengthen the basics along with SQL Server 2005/2008 New Features Module 01: Subqueries, Ranking Functions, Joins and Set Operations Module 02: Table Expressions Module 03: TOP and APPLY Module 04: SQL Server 2008 Enhancements Day 2: Query Optimization & Performance Tuning 1 Module 05: Logical Query Processing Module 06: Query Tuning Module 07: Introduction to the Query Processor Module 08: Review of common query coding which causes poor performance Day 3: Query Optimization & Performance Tuning 2 Module 09: SQL Server Indexing and index maintenance Module 10: Plan Guides, query hints, UDFs, and Computed Columns Module 11: Understanding SQL Server Execution Plans Module 12: Real World Index and Optimization Tips Download the complete PDF brochure. We are also going to have SharePoint 2010 training by Joy Rathnayake on 10-11 May. All the details for discount applies to the same as well. Reference : Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQL Training, SQLAuthority News, T SQL, Technology

Search Results

Search found 23955 results on 959 pages for 'insert query'.

Page 22/959 | < Previous Page | 18 19 20 21 22 23 24 25 26 27 28 29 | Next Page >

- by Gabriel

- by Valkyrie

- by Rick de Graaf

- by Prashant

- by Trey Carroll

- by Kanavi

- by Schlomer

- by user328371

- by JoBu1324

- by Wesley

- by user577719

- by Bloudermilk

- by Grammarian

- by Andrey Salnikov

- by Tim

- by Nige

- by I Like PHP

- by Mugil

- by Neil Smith

- by DAXShekhar

- by user560559

- by user560559

- by Sandra

- by pinaldave

- by pinaldave

< Previous Page | 18 19 20 21 22 23 24 25 26 27 28 29 | Next Page >