tsql

SQL: Dynamic Where Clause using IN statement dying on records with NULL values

I am trying to implement search funtionality for our database through a website. The database has one master table and five tables that are foreign keyed to the master (detail tables). Detail tables hold nullable integer values that are hooked to lookup tables in the application. I want to allow a user to select any/all values from a loo...

SQL query to find where no primary key exists

Hi I have re-added a foreign key relationship to a table in order to do so I had to abandon checking data on creation. I presume some parent(Company) objects have been deleted and I want to find the orphaned(Division) records. How would I find an row where the foreign key does not exist in the primary table? This is what I was thinking ...

How to check if a date is in a list of dates - TSQL

I am trying to create a SQL query that checks if a date is in a list of dates but my query doesn't work... SELECT * FROM TABLE1 WHERE field1 = value1 AND convert(nvarchar(15),date_start,101) IN (SELECT convert(nvarchar(15),date_end,101) FROM TABLE2 ) This query should return some values but it doesn't......

How do i create index column in sql?

I'm using SQL-Server 2005. I have table with many columns, rows i select have where clause which makes impossible to use identity as indexer. I want select all these row + indexer row (acting like identity). example 2 jack 4 thomas 8 james to 1 2 jack 2 4 thomas 3 8 james thanks ...

TSQL Case/When question

Hello, I have a huge query which uses case/when often. Now I have this SQL here, which does not work. (select case when xyz.something = 1 then 'SOMETEXT' else (select case when xyz.somethingelse = 1) then 'SOMEOTHERTEXT' end) (select case when xyz.somethingelseagain = 2) then '...

Insert C# table data into a SQL Server table

Hi, Is there anyway to insert in bulk a System.Data.DataTable in C# into a SQL Server table using a store procedure and passing this table as parameter? The table won't have a fixed number of records, Thanks ...

ltrim(rtrim(x)) leave blanks on rtl content - anyone knows on a work around?

i have a table [Company] with a column [Address3] defined as varchar(50) i can not control the values entered into that table - but i need to extract the values without leading and trailing spaces. i perform the following query: SELECT DISTINCT RTRIM(LTRIM([Address3])) Address3 FROM [Company] ORDER BY Address3 the column contain both ...

Store IP Address in Data Base best way

Hi, I need create a table to store Black listed IP address in case of spammers in my blog. I would like to know: What is the best datatype to store IP addresses. What others field I can list in this table that you think could be useful against spammer. I use MS SQL 2008 DB Thanks for your time ...

UPDATE column from another column two tables away

Not that good with SQL yet but I'm learning. So I have 3 tables: tblOne(Id, Type) tblTemp1(Name, Type) tblTemp2(Id, Name) Basically, I want to update tblOne where the where it's 'Id' matches from tblTemp2, but than that also goes and grabs its 'Type' from tblTemp1 where the 'Name' matches. Can anyone help? ...

SSRS Parameters. Allowing "All" or "Null"

SSRS parameters are a pain. I want to be able to re-use reports for many different needs by allowing the users access to many different parameters and making them optional. So, if I start out with code such as: Select * from mytable myt where myt.date between '1/1/2010' and '12/31/2010' and year(myt.date) = '2010' and myt.partnumber ...

SQL Query for getting the difference between a value from one row with a value another row

I have data as shown in the table below |ItemNo| Date |Value| -------------------------- | 101 | 201002 | 5 | | 102 | 201002 | 3 | | 201 | 201002 | 7 | | 202 | 201002 | 2 | | 101 | 201003 | 6 | | 102 | 201003 | 3 | | 201 | 201003 | 7 | | 202 | 201003 | 1 | Result Expected |ItemNo| Date...

What is the most optimal way of concatenating string values from column to single string? (T-SQL)

Say I have a table MemberMoto ( MemberID int, Moto nvarchar(100) ) I need to build a proc that will return coma separated list of Moto values per user. I am looking for a way to accomplish this without using CURSOR/FETCH for performance reasons Thank you! ...

No way to use TSQL Output with normal foreign key constraints?

Following snippet fails with error, "The target table 'dbo.forn' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_forn_prim'." I can only use output by disabling foreign key constraints? Surely I'm missing something... IF OBJECT_ID ('dbo.forn') IS NOT NULL...

tsql identity insert without column name list

I am going to dump some data from one db to another one. I am using set identity_insert MyTable on GO INSERT INTO MyTable SELECT * FROM sourceDB.dbo.MyTable GO set identity_insert MyTable off Is anyway to get this to work? There's 30 tables and it will be time consuming to add the list of column names to the insert statement. I am usi...

How to delete completely duplicate rows

Hello, Say i have duplicate rows in my table and well my database design is of 3rd class :- Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap'); Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap'); Insert Into tblProd...

SQL Date Compare by only using Date not Time

This was originally going to be a question about how to implement this because I was stuck at a certain part but I am now curious as to why this was happening in the first place. I needed to compare only the dates not the time which wouldn't have been a problem if the times didn't differ. The code below shows the query I was originally t...

Select Timeout With 2 Parameters

I've got the following View, called ViewGoods: SELECT G.Gid, SI.[$Id] AS FirstSiteInId, SI.Date AS FirstSiteInDate, SI.Comments AS FirstSiteInComments, S.[$Id] AS FirstSiteId, S.[$Refex] AS FirstSiteRefex, SI.Client AS ClientId, C.[$Refex] AS ClientRefex, CASE WHEN SI.Contract IS NULL THEN (SELECT Contract.[$Id] ...

How to fetch entries starting with the given string from a SQL Server database?

Hi I have a database with a lot of words to be used in a tag system. I have created the necessary code for an autocomplete box, but I am not sure of how to fetch the matching entries from the database in the most efficient way. I know of the LIKE command, but it seems to me that it is more of an EQUAL command. I get only the words that...

Excel pivot table Storing in SQL 2008 / Cross tabbed tables

Let's say I have a table with X and Y values like: x-1 x-2 x-3 y-1 V=1 v=4 v=6 y-2 V=1 v=4 v=67 y-3 V=2 v=0 v=9 y-4 V=4 v=5 v=62 where the value for x-1, y-1 is 1 and so on. I need to store all the x value field names, all the y value field names, and all values. ...

Select a nullable bit with a default value

I need to select a nullable bit column in a view, but use a default value of FALSE whenever the value is NULL. (For other reasons, I can't add the default value on the source table itself.) Here is what I am doing. CAST ( CASE WHEN bit_column IS NULL THEN 0 ELSE bit_column END AS BIT ) AS bit_column, ... I ha...