sql

Generate MySQL data dump in SQL from PHP

I'm writing a PHP script to generate SQL dumps from my database for version control purposes. It already dumps the data structure by means of running the appropriate SHOW CREATE .... query. Now I want to dump data itself but I'm unsure about the best method. My requirements are: I need a record per row Rows must be sorted by primary ke...

Save All Values of Database to Text File?

I was wondering if there was a way to extract all the values of a column, and then save each value into separate text files (labeled ANYTHING, 1-100 works fine). I know how to do this through another language, but I'm using SQL Server 2008 with Microsoft SQL Server Manager and it would save me some time from figuring out how to do it th...

Transactional isolation level needed for safely incrementing ids

I'm writing a small piece of software that is to insert records into a database used by a commercial application. The unique primary keys (ids) in the relevant table(s) are sequential, but does not seem to be set to "auto increment". Thus, I assume, I will have to find the largest id, increment it and use that value for the record I'm in...

In T-SQL ,a Programe is not working

In SQL declare @keyword varchar(100) declare @val varchar(100) set @keyword = '%asp.net%' set @val = '%c#%' select f_Resume_uid, f_Resume_Title, f_Resume,f_Filename from tbl_resume where (f_Resume like + @keyword + ) AND (f_Resume like + @val +) --select f_Resume_uid, f_Resume_Title, f_Resume,f_Filename from tbl_resume where (f_Resume...

Replacing characters with specified one

Hello All, I have a string as follows - MFMFMF now i want to change this string to FMFMFM how to do this , help needed pls i had tried select replace(replace('mfmfmf','M','F'),'F','M') this gives me result - MMMMMM which i donot what i want the output to be FMFMFM Need your help D.Mahesh ...

Select return dynamic columns

I have two tables: Standards and Service Offerings. A Standard can have multiple Service Offerings. Each Standard can have a different number of Service Offerings associated to it. What I need to be able to do is write a view that will return some common data and then list the service offerings on one line. For example: Standard Id | D...

Sqlite: Selecting records spread over total records

I have a sql / sqlite question. I need to write a query that select some values from a sqlite database table. I always want the maximal returned records to be 20. If the total selected records are more than 20 I need to select 20 records that are spread evenly (no random) over the total records. It is also important that I always select ...

When is a query too big?

Hey guys, I'm monitoring calls and putting them into a database. I'm putting caller, called, start, end in database. There are an average of 70-80 calls a day (none in the weekend), so it comes to 350-400 a week. The program will be used for a long time, so after a year there will be many items in the database. A part of the program s...

Loop Stored Procedure in VB.Net (win form)

Hello, I am trying to run a for loop for a backup system and inside that i want to run a SP that will loop. Below is the code that does not work for me.. Any ideas please? Dim TotalTables As Integer Dim i As Integer TotalTables = 10 For i = 1 To TotalTables objDL.BackupTables(220, i, 001) ' (This is a method from the DL and the 3 ...

MYSQL: COUNT with GROUP BY, LEFT JOIN and WHERE clause doesn't return zero values

Hi guys, thanks in advance for any help on this topic! I'm sure this has a very simple answer, but I can't seem to find it (not sure what to search on!). A standard count / group by query may look like this: SELECT COUNT(`t2`.`name`) FROM `table_1` `t1` LEFT JOIN `table_2` `t2` ON `t1`.`key_id` = `t2`.`key_id` WHERE `t1`.`anothe...

What is the correct SQL for a trigger to copy a record to an identical table?

I have two tables TABLE1 and TABLE2 with identical structures. I need a trigger to copy a record after insert from TABLE1 to TABLE2. What's the proper SQL for this? ...

SQL Server BEGIN/END vs BEGIN TRANS/COMMIT/ROLLBACK

I have been trying to find info on the web about the differences between these statements, and it seems to me they are identical but I can't find confirmation of that or any kind of comparison between the two. What is the difference between doing this: BEGIN -- Some update, insert, set statements END and doing this BEGIN TRANS ...

How to write Sql or LinqToSql for this scenario?

How to write Sql or LinqToSql for this scenario? A table has the following data: Id UserName Price Date Status 1 Mike 2 2010-4-25 0:00:00 Success 2 Mike 3 2010-4-25 0:00:00 Fail 3 Mike 2 2010-4-25 0:00:00 Success 4 Lily 5 2010-4-25 0:00:00 Success 5 Mike 1 2010-4-25 0:00:00 Fai...

Database Tagging Structure

Hi, I have three tables in my database. News, reviews and tutorials. I would like to implement tagging functionality. I have done this before by having a tag table to define my tags, and a lookup table which has the type (news, tutorial, review), itemId and the tagId. This has worked fine, however for my new site, I want to have PK FK...

Ensure good spread of geographical data from SQL database

To display data on google maps, I have >50k rows of data in SQL server 2005 with the following (simplified) structure PointID Latitude Longitude TypeID I can select a small subset within a bounding box, ensuring a decent mix of Types like this: ..... ( @NELat float, @NELong float, @SWLat float, @SWLong float ) as select top 100 Poin...

Parameterized SQL statements vs. very simple method

When I started to write the first SQL-Statements in my programs I felt quite comfortable with protecting myself against SQL-Injection with a very simple method that a colleague showed me. It replaced all single quotes with two single quotes. So for example there is a searchfield in which you can enter a customername to search in the cus...

How Can I Execute *.exe From Table Trigger?

i would like to start an exe or dll once record is updated. If field of interest is updated i would like to call an *.exe with parameters. Is this possible in MySql and oracle? ...

"Executing SQL directly; no cursor" error when using SCOPE_IDENTITY/IDENT_CURRENT

There wasn't much on google about this error, so I'm asking here. I'm switching a PHP web application from using MySQL to SQL Server 2008 (using ODBC, not php_mssql). Running queries or anything else isn't a problem, but when I try to do scope_identity (or any similar functions), I get the error "Executing SQL directly; no cursor". I'm d...

Postgesql select from 2 tables. Joins?

I have 2 tables that look like this: Table "public.phone_lists" Column | Type | Modifiers ----------+-------------------+-------------------------------------------------------------------- id | integer | not null default nextval(('"phone_list...

SQL Query to find duplicates not returning any results

I know there are duplicate account numbers in this table, but this query returns no results. SELECT [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2], COUNT([ACCT NBR 1]) AS NumOccurences FROM DebitCardData.dbo.['ATM Checking Accts - Active$'] GROUP BY [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2] ...