tsql

How to write the following in Linq or Sql

Hi, Appreciate if I can get some help writing a LINQ that will get ALL FIELDS from table A, and those fields for which profile 1 has a value in table AB, show the value, otherwise if profile 1 has no entry in table AB, then show value as null. Table A AID Field ----------- 1 OneField 2 TwoField 3 ThreeField Table ...

Hierarchial SQL Query

Hi, I have a self-referencing table (Customers) and a table that will link to one record in that table (Companies) i.e. Customers Companies ********* ********* ID ID ManagerID --> DirectorID ManagerID refers to another record in the Customer table. I need to perform a query where by given a specific customer I...

Create SQL identity as primary key?

create table ImagenesUsuario { idImagen int primary key not null IDENTITY } This doesn't work. How can I do this? ...

SQL Server 2005 compare dates

I'm having a problem with some T-SQL in a SP on SQLServer 2005 comparing dates. I'm running the stored procedure from c# with ADO.Net and passing a the native c# datetime datatype(could this be my issues as I know the ranges are slightly different). I do the following to compare 2 DateTime values in my SP. CREATE PROCEDURE [dbo].[spGetL...

How to UPDATE TOP 400 ?

I would like to update the top 400 rows in a database table. The pseudo SQL is below, how can I do this? UPDATE top (400) db.dbo.tbl SET column1 = 2 WHERE column2 = 1 AND column1 is null ...

What is the most effient way to write a select statement with a "not in" subquery?

What is the most efficient way to write a select statement similar to the below. SELECT * FROM Orders WHERE Orders.Order_ID not in (Select Order_ID FROM HeldOrders) The gist is you want the records from one table when the item is not in another table. ...

Is there a way to return multiple results with a subquery?

I have need to return multiple results from a subquery and have been unable to figure it out. The end result will produce the persons name across the vertical axis, various actions based on an action category across the horizontal axis. So the end result looking like: ---------- **NAME CATEGORY 1 CATEGORY 2** Smi...

tsql, picking out value-pairs

I have a column that has the following data: PersonId="315618" LetterId="43" MailingGroupId="1" EntityId="551723" trackedObjectId="9538" EmailAddress="[email protected]" Is there any good, clean tsql syntax to grab the 551723 (the value associated with EntityId). The combination of Substring and Patindex I'm using seems quite unwie...

Looks simple, but I'm stumped!

This should be so simple, but I'm drawing a blank. I have two tables. Table:article_tag_pvt colum: article_id column: tag_id Table: tag column: tag_id column: name The article_tag_pvt table is a many-to-many pivot table. The problem is, I need a query which given a list of tag names, will retrieves article Ids which ONLY match tho...

insert into sql server table and get back primary key

i have a primary key set up to auto increment. i am doing multiple queries and i need to retrieve that primary key to use as a foreign key in another table (is identity = true) is there any elegant way to get back the primary key when i do an insert query. right now i am requerying and getting the highest value in that column which se...

Passing many parameters into SQL and SSRS

hi, i am trying to create a report from a row of data with about 60 columns. The workflow is as follows. 1] user selects the fields they want to view in the report from within c# 2] c# calls a sp in sql and passes the fields (expecting between 1 and 5 fields per report) 3] ssrs displays these fields on a pie chart in reporting services....

How to insert into a table with just one IDENTITY column (SQL Express)

Pretty much the same as this question. But I can't seem to get this to work with SQL Server Express in Visual Studio 2008. Same Table layout, One column with identity and primary key. ...

Code Length vs Organization

I have several nested insert commands. Some of the nested loops share redundant code. Should I make the redundant code its own loop, or create separate instances of the same code within each loop? EXAMPLE (edited for clarification): --Questions 32<->37 SET @index=0 SET @values = 'at your primary grocery store^at WalMart or Sam''s Clu...

Tricky Update Problem

Hi guys, I have the following table structure as follows: (please ignore the blackbox. was formatting the question in Excel) What I need to do is to transform the SessionGUID data into the following using TSQL: This is the user sessions table for my website and we screwed up the SessionGUID's in that it was generating a NEWID() on ...

How do you drop a default value from a column in a table?

How do you alter a column to remove the default value? The column was created with: ALTER table sometable Add somecolumn nchar(1) NOT NULL DEFAULT 'N' And then altered with: alter table sometable alter column somecolumn nchar(1) null That allows nulls, but the default value remains. How can you remove it? ...

best way to migrate tables from MS access to sql server and consolidate

I have a ms access database that has one table for each photo album with the name of the table as the albumname tablename = "Trips" fields: picID, comment i am migrating to sql server and i wanted to fix this normalization issue and just have One table called Albums with albumID and albumName One table called pictures with picID, alb...

Get Updated Row

I have a query that updates one record, and only one record. Is there are way to get the Id updated in the same query such as Select ScopeIdentity when inserting. UPDATE Task SET MyTime = GetDate(), MyUserId = @userid FROM (select top 1 table where SomeStuff) Select Lastrow that just got updated. ...

how do I select a column based on condition?

Hello, I have a variable called @status which I set before this select statement: Select ordr_num as num, ordr_date as date, ordr_ship_with as shipwith From order where ordr_num = @ordrNum I only want to select ordr_ship_with column if @status <> 'Cancelled', otherwise I want to select null for shipwith. How do I accomplish this? ...

How do I reference a table-valued function in a common table expression?

Note: This issue appears to be limited to SQL Server 2005 SP2 I have a common table expression that iterates over a series of dates. I need to reference each of these dates in a call to a table-valued function. Something like this below, which gives a syntax error around the second parameter of the call to GetMyData. If I use another va...

list of tables without indexes in sql 2008

How do I list tables without indexes in my SQL 2008 database? Edit I want the Schema name and the Table name. ...