tsql

How to filter nvarchar greater than numeric value?

I have a MS SQL table McTable with column BigMacs nvarchar(255). I would like to get rows with BigMacs value greater than 5. What I do is: select * from ( select BigMacs BigMacsS, CAST(BigMacs as Binary) BigMacsB, CAST(BigMacs as int) BigMacsL from McTable where BigMacs Like '%[0-9]%' ...

What's the impact of 'select *' from a view on building the execution plan?

I've heard using 'select *' will add to the time SQL Server takes to build the query execution plan as it has to find out which columns are present on the referenced tables. Does the same apply for a query based on a view instead of a table? ...

How do I combine these two SQL statements into one, in a manner compatible with both MSSQL 2000 and 2005?

My end goal is to accomplish something like: CREATE FOREIGN KEY IF NOT EXISTS FOREIGN KEY Since that statement doesn't appear to exist, I'm attempting to sort of kludge it together. I have a statement that will return the FK name if it exists: SELECT f.name AS ForeignKey FROM sys.foreign_keys AS f WHERE OBJECT_NAME(f.parent_object_...

T-SQL EXEC and scope

Let's say I have a stored procedure with this in its body: EXEC 'INSERT INTO ' + quotename(@table) ' blah...' SELECT IDENT_CURRENT('' + @table + '') Is IDENT_CURRENT() guaranteed to get the identity of that row INSERTed in the EXEC? IDENT_CURRENT() "returns the last identity value generated for a specific table in any session and any ...

Table design

I was wondering is this a good design, assuming the tables as follows ADDRESS(id, address, city_fk, stateFK, countryFK), CITY(id, name, stateFK, countryFK), STATE(id, name, countryFK), COUNTRY(id, name) Notice how country fk is repeated in 3 tables? and state fk repeated in 2 tables? Can anyone tell me if this is a good design? If s...

A Simple Sql Select Query

I know I am sounding dumb but I really need help on this. I have a Table (let's say Meeting) which Contains a column Participants. The Participants dataType is varchar(Max) and it stores Participant's Ids in comma separated form like 1,2. Now my problem is I am passing a parameter called @ParticipantsID in my Stored Procedure and want ...

Select Most Recent States From History Table

I have inherited a table with a structure something like this: ID Name Timestamp Data ---------------------------- 1 A 40 ... 2 A 30 ... 3 A 20 ... 4 B 40 ... 5 B 20 ... 6 C 30 ... 7 C 20 ... 8 C 10 ...

Add record/s from two tables to a third table: MS SQL Server 2005

Hi, I want to add a record/s from two tables to a third table. It is something like the following: table1: pln taskName plnHec pinDate(mm/dd/yyyy) xx 10 3/1/2008 yy 20 4/1/2008 zz 10 3/1/2008 zz 10 4/1/2008 xx 10 4/1/2008 tabl...

Use "<>" or "!=", or does it really even matter

Duplicate of: Should I use != or <> for not equal in TSQL Within SQL Server's T-SQL should I use "<>" or "!=", or does it really even matter? ...

Trim first two characters from year of GETDATE()

Hello group, I am trying to create a WHERE clause that says WHERE column_1 = TRIM(LEADING '20' FROM(DATEPART(year, GETDATE()))) Basically column_1 contains the fiscal year but the fiscal year is in the database as 8 or 9 not 2008 or 2009. So I need to figure a way to trim off at least the '20' so that the query will run correctly.....

Dynamic SQL Server question

I want to create dymamic SQL code to automatically create a table-replication. I'm stuck on how to make the following code dynamic, so that it's possible to pass in SQL variables and use them in the code. I help myself at the moment with search and replacing the 'TODO:' parts, which is not very nice... Here is the code: DECLARE @sql VA...

Multiple Counts in SQL Query

Give the following simple table structure: Departments PK - DeptID DeptName -------------------------- 1 Department 1 2 Department 2 3 Department 3 4 Department 4 Groups PK - GroupdID DeptID -------------------------- 1 1 2 1 3 3 4 ...

Using stored procedures results in update statement

I've got a table with two columns(among others): id and created_in_variant and a stored procedure that calculates the created_in_variant value basing on the id. I'd like to do something like this: UPDATE [dbo].[alerts] SET [created_in_variant] = find_root [id] Is there a nice way to do it? ...

How to Export SSIS data on SQL Standard and Import with Bulk Insert on SQL Express?

I would like to use SSIS to create database table flat file exports then import them in to variously distributed SQL Express installs. As you may guess, SSIS is not available in the Express version. When I do the bulk insert, it errors with: Msg 4866, Level 16, State 8, Line 1 The bulk load failed. The column is too long in the data f...

Print result set without cursor

I'm using T-SQL and I want to print out a result set. This is just a ~2x6 (dynamic size) set but I'm not entirely sure how I can do this without using a CURSOR. Is there a nice way I can print these to console/email/wherever? ...

Temporary function or stored procedure in T-SQL

Hello is there any chance to create temporary stored procedure or function on MS SQL 2005? I would like to use this stored procedure only in my query so after execution it will be gone. I have a query I would like to EXEC against some data. But for every table I will process this command I need to change some parts of it. So i thought I...

Differentiate empty from NULL query results

I've got a table with measurement data in SQL Server 2005, one value per person and year, if available. My TSQL code fetches these values in a loop and processes them: ... SET @val = (SELECT measurement FROM tbl_data WHERE persid = @curpersid AND yr = @curyear) ... Now, for a certain person and year, the table can contain (i) a valid ...

How to disable primary key constraint programmatically?

I have a table with primary key in my MS SQL Server 2005 table. I would like to disable it. Now i get error: Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'. I would like this error not to occur and to work with PRIMARY KEY like with normal column without constraint and than restore this...

How to control order of Update query execution?

I have a table in MS SQL 2005. And would like to do: update Table set ID = ID + 1 where ID > 5 And the problem is that ID is primary key and when I do this I have an error, because when this query comes to row with ID 8 it tries to change the value to 9, but there is old row in this table with value 9 and there is constraint violation...

Comma separated values in a database field

I have a products table. Each row in that table corresponds to a single product and it's identified by a unique Id. Now each product can have multiple "codes" associated with that product. For example: Id | Code ---------------------- 0001 | IN,ON,ME,OH 0002 | ON,VI,AC,ZO 0003 | QA,PS,OO,ME What I'm trying to do ...