tsql

Transforming Table into different Table

I have a table like this: RowID | ProductDescription1 ----------------------------------------------------- 1 | 0296620300-0296620399; 2 | 0296620400-0296620499;0296620500-0296620599; 3 | 0296620600-0296620699;0296620700-0296620799; I want to become like this: NewRowID | Start | End | SourceRowID ------------...

Extracting yyyy/MM/dd formated date from datetime datatype in T-SQL

I have my date values in postdate column in articles table in sql database table. It stores all date as well as time. I would like to get the date part only. i.e from 2010/07/01 12:45:12 i would likfe to get 2010/07/01 in my view ...

Combining INSERT INTO and WITH/CTE

Hi there I have a very complex CTE and I would like to insert the result into physical table. Is this allowed: INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo, APartyNo, SourceRowID) WITH tab ( bla bla ) SELECT * FROM tab I am thinking of using a fu...

Timing compare in SQL Server 2008

Hi All. How to compare Indian time with world's other country times in SQL Server 2008? Means i want to know if in India its mid night 1 am what will be timing in other countries through SQL Server 2008 ...

Select records with order of IN clause

I have SELECT * FROM Table1 WHERE Col1 IN(4,2,6) I want to select and return the records with the specified order which i indicate in the IN clause (first display record with Col1=4, Col1=2, ...) I can use SELECT * FROM Table1 WHERE Col1 = 4 UNION ALL SELECT * FROM Table1 WHERE Col1 = 6 , ..... but I don't want to use that, cause ...

How to translate lambda expressions to the TSQL statement

e.g. x => x.UserId > 5 && x.Name.StartWith("S") && x.Name.EndWith("D") && x.Name.Contains("x") to it.UserId > 5 And it.Name like 'S%' And it.Name like '%D' And it.Name like '%x%' ...

How do I decide , When to use Right Join/ Left Join or Inner Join or How to determine which table is on which side?

Hi , I know the usage of joins but sometimes i came across such a situation when i was not able to decide which join will be suitable a left or right. Please show me some light. Here is the query where i am stucked SELECT count(ImageId) as [IndividualRemaining], userMaster.empName AS ID#, ...

T-SQL cursor problem

GO SET NOCOUNT ON; DECLARE @idAdvertisements int, @Name nvarchar(255), @Description nvarchar(500), @DepartureDate datetime, @Cities_idCities int, @Areas_idAreas int, @Countries_idCountries int, @Agencies_idAgencies int, @Url nvarchar(1000), @Price decimal(6, 2), @HollidayDuration int, @BookingDate datetime; DECLARE ad_cursor CURSOR ...

GETDATE() in a T-SQL Job Step Command

I can't seem to get the GETDATE() syntax to work in a Job Step of type Transact-Sql Script. I put in the command as: execute insertMostRecentUpdate @Data='Data', @Date=GETDATE()-1 But I get an "incorrect syntax near ')'" error when parsing or trying to run it. Any thoughts? ...

SQL Server 2005, how to copy a Database Diagram to another server

Is there a way to copy a SQL Server Database Diagram to another server? I found this and modified it sightly to copy only one diagram: INSERT INTO dbB.dbo.sysdiagrams SELECT [name],[principal_id],[version],[definition] FROM dbA.dbo.sysdiagrams Where name = 'MyDiagramName' But I need to copy it to another Server (Development to Produ...

GROUP BY problem with varchar

There are links stored in a DB as varchars. With these links I want to use GROUP BY. http://example.com http://example.com http://example.com SQL over that data: SELECT COUNT(*) c, Url FROM Advertisements GROUP BY Url I want this output: c Url 3 http://example.com But instead I get this three times: c Url 1 http://exa...

Create SQL job that verifies daily entry of data into a table?

Writing my first SQL query to run specifically as a SQL Job and I'm a little out of my depth. I have a table within a SQL Server 2005 Database which is populated each day with data from various buildings. To monitor the system better, I am attempting to write a SQL Job that will run a query (or stored procedure) to verify the following: ...

SQL Server syntax question

USE AdventureWorks; GO IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL DROP TABLE dbo.AWBuildVersion; GO Whats the N symbolize in N'dbo.AWBuildVersion' & N'U' ? ...

Type Comparisons in SQL

I've got the following bit of code as part of a SQL Query: INSERT INTO [Database] SELECT DISTINCT @ssId FROM [Document_Map] WHERE (LabelId IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)) The code works fine as long as @SortValue is an integer, (LabelId is an int as well) or integers separat...

Compare Every Record from Two Tables

Assuming I have two SQL tables consisting of a single column, i.e. Table 1 Table 2 a1 a2 b1 b2 c1 c2 Is there a succinct SQL command to compare each record in one table against each record in the other? (and return true if any record from table 1 matches any recor...

Elegant way to delete rows which are not referenced by other table

Hi, I have two tables (Tasks and Timeentries), which are connected by a foreign key (TimeEntries.TaskID references Tasks.ID) Now I'd like to delete all rows from Tasks which are not referenced by the TimeEntries table. I thought that this should work: DELETE FROM Tasks WHERE ID not IN (SELECT TaskID FROM TimeEntries) But it affects ...

XQuery in Linq To Entities for Sql Server XML Data Type

I am trying to figure out how to do a Linq To Entities query on a SQL Server Database which contains an XML datatype: I want to do a database level query that returns only a specified XML node that contains over 2000 Characters Is this possible in Linq To Entities? Update well i want to do something like this in linq-to-entities bu...

SQL schema Table compare

I'm looking for an app that will compare the layout of 2 SQL tables. When developing in a DEV environment, I need a tool that will make the production tables exactly like the DEV tables (the layout). ...

Form a query to find duplicate zipcodes

Hello, I have a zipcode table. I want to look through this table and find every zipcode that is in it twice. The table does have a primary key set on it(zipcode_rid). How would I do this? Also, I am aware that there are official multi-state/city/county zipcodes. That is what I'm wishing to find. My table structure is like so: zipcod...

Is it possible to retrieve the line number from an OleDbException caused by calling ExecuteNonQuery()?

We are calling ExecuteNonQuery on all files in a certain folder for version scripting and if there is a syntax error an exception is raised. I have been scanning MSDN for a way to get the line number but haven't been able to find anything yet. There is a 'SqlException' class which does contain the line number but the shared base class 'D...