Sql - format phone number
HI , i have table in the db with phone number column the numbers look like this 123456789 i want to format tham to 123-456-789 thanks!!! ...
tsql
SQL Server Syntax to select two rows of data and overwrite values that are different from the first row
Hi, I have a table that looks like the following: id, Name, LinkBackId, Param1, Param2, Param3 1, "Name1", NULL, 10, 20, 30 2, "Name2", NULL, 10, 20, 30 3, "Name3", 2, 14, NULL, NULL The LinkBackId is important here because this is associated with the id that is also in th...
insert data in increments
Does anyone know how to insert data into a table in increments of about 1000? I have a table with thousands of records that I want to insert into an identical table on a different server. ...
Compare dates in T-SQL, ignoring the time part
I'm using MS SQL 2005, and I want to check two dates for equality, but ignoring the time part. I know I can make use of DATEDIFF, but am concerned that it may be slow - this SP gets used in the DB a lot! Any suggestions? Edit: David Andres' comment: '"comparison" includes much more than equality' made me realise that I didn't make my...
Management Studio 2005: Will Cancelling a Statement trigger a Rollback?
A few minutes ago, while working out a new sproc, I executed the wrong delete statement. Something like this: Delete From SomeTable Where SomeStatusID=1 10 seconds into it, I realized that I typed in the wrong status and hit cancel. It said that the statement was canceled. I did a restore to a separate database to get back the table ...
SQL: What does =* mean?
I'm trying to trace some SQL in Microsoft Server. I came across a join that is using a convention unfamiliar to me. What does "=*" mean? WHERE table1.yr =* table2.yr -1 ...
How to escape a string for use with the LIKE operator in SQL Server?
I am looking for something that works in SQL Server similar to the @ symbol in c# which causes a string to be taken as it's literal. Eg: string text = "abcd\\efg"; Output of text = abcd\efg string text = @"abcd\\efg"; Output of text = abcd\\efg Note how the @ affected the string to take every character as is. Now I am not sure this...
SQL - Using Group By and Count to collect the total of unique two-field combinations
I have two fields (project and version) from two different tables, and I am trying to get the total of entries that match unique project/version combinations, however the versions in some projects match up with versions in other projects (Project 1 has version 1.0, and so does Project 2) so my SQL statement isn't working: SELECT TOP 100...
Determining the primary and foreign table in a relationship?
When designing a database, what usually determines what tables will be the primary and foreign table in a relationship? For example, if I have a table called posts and it contains and id column postid and I have a table called comments and it contains a column called postid. Which of these tables would be the primary one in the relati...
Is this the correct way to use sp_executesql in a stored procedure?
I have this huge stored procedure where I am building a sql statement. At the end, I create a parameter list like this: DOES @paramList need and N in front of it?? SELECT @paramList = '@param1 nvarchar(300), @param2 nvarchar(10), @param3 nvarchar(10), @param4 nvarchar(100), @param5 nvarchar(1000), @param6...
SQL Server Boolean Expression evaluation
I have a table that stores data from the county appraisal district. I want to add a computed column to tell me if the property is owner-occupied. I define "owner occupied" as true if the property address is the same as the owner address, false otherwise. Because of data entry inaccuracies in the source data from the county, if I do a st...
Error in query with XML data type
Hi, I have a query that strips data from a XML string, inserts it into a table variable and queries it to insert the data into another table. It runs like a charm in my localhost environment, but not in my production SQL Server database. The error message is "INSERT failed because the following SET options have incorrect settings: 'ARITH...
How to drop SQL default constraint without knowing its name?
In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is: IF EXISTS(SELECT * FROM sysconstraints WHERE id=OBJECT_ID('SomeTable') AND COL_NAME(id,colid)='ColName' AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1) ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTa...
Having trouble with this simple SQL Select statement.
I have two tables, A and B. Both have the exact same columns. I need to select all the items in TableA that ARE NOT in TableB. This is intersection, right? How can I do this? ...
SQL Selecting rows that are in both tables.
How can I select rows that exist is two tables. The intersection I guess? Any help? ProductosA and ProductosB, they're both tables with the exact same number and type of columns. How can I select the things that are inside of both using a single select statement? ...
SQL Injection, simplest solution.
I'm unsure how someone would break my SQL if I simply replace all incoming single quotes with double quotes. Can someone enlighten me for both Oracle and SQL Server examples? Thanks. string sql1 = "select * from users where user_id = '" + "O'Reily".Replace("'", "''").Replace("\", "") + "'"; ==> "select * from users where user_id = 'O...
Determine if a row for an fk exists in any one of 20 tables
I was trying to determine a better way than so many IF/THEN statements to determine if ONE OR MORE tables contain a ref to a forign key. I simply need to know (true/false) if a row exists in any one of 20 tables. All tables have the same fk field. I hope this makes sense, if not i'll try to explain further. ...
HOW to convert CSV to record set inside T-SQL?
In my stored procedure I am passing a filter (using "WHERE Column IN" clause) as a parameter. The value of the parameter is given as CSV. What is the best method to convert this CSV in to a record set. Example:- SELECT * FROM Employee WHERE Name IN ('John','Joe','Jerry','James') and I need to pass the names as a parameter whi...
Table Valued Function where did my query plan go?
I've just wrapped a complex SQL Statement in a Table-valued function on SQLServer 2000. When looking at the Query Plan for a SELECT * FROM dbo.NewFunc it just gives me a Table Scan of the table I have created. I'm guessing that this is because table is created in tempdb and I am just selecting from it. So the query is simply : SEL...
[SQL Server 2005] Function split string into decimals?
I'm currently trying to write a SQL Server 2005 function, which gets a string as parameter and creates a table with decimal values out of it. The problem is, that I have to define the decimal type based on parameters. This not working snippet should demonstrate the idea: CREATE FUNCTION [dbo].[ufn_ParseDecimal] ( @Sequence VARCHAR...