Add column, with default value, to existing table in SQL Server
How do you add a column, with a default value, to an existing table in SQL Server 2000/2005? ...
sql
Removing leading zeroes from a field in a SQL statement
I am working on a SQL query that reads from a SQLServer database to produce an extract file. One of the requirements to remove the leading zeroes from a particular field, which is a simple VARCHAR(10) field. So, for example, if the field contains '00001A', the SELECT statement needs to return the data as '1A'. Is there a way in SQL to ...
Non-iterative / Non-looping Way To Calculate Effective Date?
I have a table called OffDays, where weekends and holiday dates are kept. I have a table called LeadTime where amount of time (in days) for a product to be manufactured is stored. Finally I have a table called Order where a product and the order date is kept. Is it possible to query when a product will be finished manufacturing withou...
Handling the data in an IN clause, with SQL parameters?
We all know that prepared statements are one of the best way of fending of SQL injection attacks. What is the best way of creating a prepared statement with an "IN" clause. Is there an easy way to do this with an unspecified number of values? Take the following query for example. SELECT ID,Column1,Column2 FROM MyTable WHERE ID IN (1,...
How do you query object collections in Java (Criteria/SQL-like)?
Suppose you have a collection of a few hundred in-memory objects and you need to query this List to return objects matching some SQL or Criteria like query. For example, you might have a List of Car objects and you want to return all cars made during the 1960s, with a license plate that starts with AZ, ordered by the name of the car mode...
How can I maintain consistent DB schema accross 18 databases (sql server)?
We have 18 databases that should have identical schemas, but don't. In certain scenarios, a table was added to one, but not the rest. Or, certain stored procedures were required in a handful of databases, but not the others. Or, our DBA forgot to run a script to add views on all of the databases. What is the best way to keep database...
How to use system_user in audit trigger but still use connection pooling?
I would like to do both of the following things: use audit triggers on my database tables to identify which user updated what; use connection pooling to improve performance For #1, I use 'system_user' in the database trigger to identify the user making the change, but this prevent me from doing #2 which requires a generic connection ...
Sql server triggers - order of execution
Does anyone know how Sql server determines the order triggers (of same type, i.e. before triggers) are executed. And is there anyway of changing this so that I can specify the order I want. If not, why not. Thanks. ...
How do I return random numbers as a column in SQL Server 2005?
I'm running a SQL query on SQL Server 2005, and in addition to 2 columns being queried from the database, I'd also like to return 1 column of random numbers along with them. I tried this: select column1, column2, floor(rand() * 10000) as column3 from table1 ...which kinda works, but the problem is that this query returns the same rand...
Fetch one row per account id from list
I have a table with game scores, allowing multiple rows per account id: scores (id, score, accountid). I want a list of the top 10 scorer ids and their scores. Can you provide an sql statement to select the top 10 scores, but only one score per account id? Thanks! ...
How do you priortize multiple triggers of a table?
I have a couple of triggers on a table that I want to keep separate and would like to priortize them. I could have just one trigger and do the logic there, but I was wondering if there was an easier/logical way of accomplishing this of having it in a pre-defined order. ...
T-SQL triggers firing a "Column name or number of supplied values does not match table definition" error
Here's something I haven't been able to fix, and I've looked everywhere. Perhaps someone here will know! I have a table called dandb_raw, with three columns in particular: dunsId (PK), name, and searchName. I also have a trigger that acts on this table: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dandb_raw_searchNa...
What's the canonical way to store arbitrary (possibly marked up) text in SQL?
What do wikis/stackoverflow/etc. do when it comes to storing text? Is the text broken at newlines? Is it broken into fixed-length chunks? How do you best store arbitrarily long chunks of text? ...
Select max in Group
I have a simple table comments (id int, revision int, comment varchar(140)) with some content like this: 1|1|hallo1| 1|2|hallo2| 1|3|hallo3| 2|1|hallo1| 2|2|hallo2| I'm searching for a sql statement which will return each comment with the highest revision: 1|3|hallo3| 2|2|hallo2| I've got a solution select id, revision, comment ...
How do you list all the primary keys of a table in SQL Server?
Simple question, how do you list all the primary keys of a table with T-SQL? I know how to get indexes on a table, but can't remember PK's. ...
How to insert multiple records and get the identity value?
I'm inserting multiple records into a table A from another table B. Is there a way to get the identity value of table A record and update table b record with out doing a cursor? Create Table A (id int identity, Fname nvarchar(50), Lname nvarchar(50)) Create Table B (Fname nvarchar(50), Lname nvarchar(50), NewId int) Insert into A(fnam...
SQL server 2000 Like Statement Usage
I have a SQL statement that looks like: SELECT [Phone] FROM [Table] WHERE ( [Phone] LIKE '[A-Z][a-z]' OR [Phone] = 'N/A' OR [Phone] LIKE '[0]' ) The part I'm having trouble with is the where statement with the "LIKEs". I've seen SQL statements where authors used likes statements in the way I'm using them above. At first, I...
SQL Bulk import from CSV
I need to import a large CSV file into an SQL server. I'm using this : BULK INSERT CSVTest FROM 'c:\csvfile.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO problem is all my fields are surrounded by quotes (" ") so a row actually looks like : "1","","2","","so...
Practical limit to length of SQL query (specifically MySQL)
Is it particularly bad to have a very, very large SQL query with lots of (potentially redundant) WHERE clauses? For example, here's a query I've generated from my web application with everything turned off, which should be the largest possible query for this program to generate: SELECT * FROM 4e_magic_items INNER JOIN 4e_magic_item_lev...
SQL Server Service Broker
Were there any major or really cool features added to SQL Server Service Broker in 2008? ...