Thanks for the response.

The cost on using functions on a where clause come when using the function on the column because it causes a table scan even on an indexed field. When the function is used...

Why are you creating constants when you can just use the date functions in the where clause?



SELECT sum(Salesamount)
FROM Table
WHERE salesdate BETWEEN ...

Just replace where I had @userInput with getdate() function


SELECT sum(Salesamount)FROM TableWHERE YEAR(salesdate) = YEAR(getdate())


You can do the same with the date functions example.

There are alot of ways to do this here are a couple examples.

Using Year() function

Select sum(Salesamount)
From Table
where Year(salesdate) = Year(@userInput)

If statements can not be used within a select statement. Not quite sure what you are trying to achieve here but you could sum a case statement to increment a value.


Select abc.value as 'Name',...

Why don't you insert the records into the new table with something similar to this. It will create one row per carId and fill in the appropriate property values.


Insert into newTable(CarId,...

You can't use the alias in the boolean test plus you should use a having clause to test the count(*) and not the where clause.


Select title, count(*) As Cnt
From poss_titles
Group By title...

I beleive you need to use a full outer join with a coelesce on the 3 keys to get your desired result.


SELECT coalesce(a.Recordingid,b.recordingid), coalesce(a.Connection,b.Connection),...

Did you try using wild cards.


Select * from Table1
Where Name LIKE ('%' + @Name + '%')

What are you going to use to read the file. If you are going to program something to read the file you can simply clean the data in your code and insert into the desired table once you have cleansed...

When your doing the select into #temp statement you are creating an actual temp table that is living in virtual memory and is only visible within the session it is created and is immediately dropped...

There is no For or foreach loop for sqlserver unless you are talking about using the foreach container in SSIS, which I did mention would be the best way to go about bulk inserts.

np...it happens to everyone once in awhile.

You should be able to clean it up during import with a SSIS package if you know how or else you can do a batch load into a temp table and then clean it up when inserting into maintb.

Since you...

@yangski

The user said he was using express which does not include SSIS which is used for the import wizard. The link Sknake provided has a blog on the subject and some possible solutions.

You should open a seperate topic because someone might have a different answer and they will not see the question plus it is frowned upon.

I would use a DTS/SSIS package for bulk inserts. They can...

The first question I have is why are you using a cursor just to fill a temp table when you can simply fill the temp table with your select statement.


Select emplid, name, division, payroll_sect...

Have you tried joining the subquery in the from clause instead of using the inline view.

You should use code blocks as it makes it alot easier to read.



[/code}

Did you try an inline view? something like this maybe

[code=sql]
select

CONVERT(CHAR(10),GETDATE(),110)

Will give you the format 02-05-2003


CONVERT(CHAR(10),GETDATE(),10)

Will give you 02-05-03

The MSDN library will give you all the conversions. You can...

Just insert the getdate().

If you want specific date formats look up the CONVERT function.


Insert into Table (date)
Values(getdate())

Im sure you are just missing something in the syntax,
unfortunately I have never used this object and since it is being deprecated by Micorsoft in future releases I doubt I ever will.

I don't know too much about Rule Objects but did you run

sp_bindrule to bind the rule to the your coluum.

Pee,

Sknake is one of the most helpful posters here. He asked simply that you provide him with a create statement and sample data so he can provide you with a thorough solution for your problem.
...

Natural Joins can be used in Oracle, but I have never used them. They are like using the 'on' or 'using' construct for inner joins but the construct will distinguish which fields are the same and...

I actually remember reading both of these threads and had commented on one of them but I still did not correlate them.

@sknake - thanks, I didn't realize there were multiple threads relating to this db.

I don't see where you are getting the alias 'c'

Besides that i think you are making it more difficult then it has to be.



select *
from dbo.tblLevelOneApprover a,...

@Ramy, you are right.

Not sure why I was thinking it was in Visual Studios. Its been awhile since I have used it and everything Microsoft just looks and feels the same I guess.

BIDS is part of Visual Studio not SQL Server.

If you really want an 'AND' use an 'AND'


Select Product From Table
Where Component = 'part1'
and Component ='part3'

Select Product From Table
Where Component in ('part1','part3')

Then I am missing something about your schema. What exactly is TableD.Pointer? How many rows would you expect to retrieve from the table with each pointer?

Also what is the difference between...

unless I am missing something in your table structure or what you are trying to achieve I think the following is more of what you are looking for.


Select d.pointerField, Case When...

Link82

Group by does exactly that; groups your result set by the fields provided, in the example provided by sknake.


GROUP BY tblManagers.managerID, tblManagers.managerLastName,...

I can't see any easy way to parse this string in a single select statement. I would try each individual piece at a time and then once you can parse each piece put them back together.

You might...

You should be able to create a stored procedure and try to break off a piece at a time.

If you don't need the data live maybe you can make a dts package to run daily to parse these strings and...

I have question.

How were these records imported into the table in the first place?

It would have been easier to parse them correctly when they were initially loaded.

As you mentioned in an earlier post Intersect is the way to go if you are using SQL Server 2005 or later.


select a.name,a.userid
from users a, aoi b, users_aoi c
Where a.userid = c.userid ...

I see what you mean, I am in the middle of something but i will work on a query for you.