how to continue on error

How can I cause my insert statement to skip over (without failing) rows
where there's a primary key constraint violation?

I've got a case where daily I insert >500k rows of daily data, where
the data is date and time stamped. So, for example, I have an insert
statement with constraint: WHERE date >= '5/20/05' AND date <
'5/21/05'. That takes care of one day's data (5/20).

However, the next day's data (5/21) will still have some time stamps
from the previous day. Therefore the statement needs to be something
like WHERE date >= '5/20/05' AND date <= '5/21/05'. The 5/20 data is
already loaded but I need to take the 5/21 data which just happens to
contain just a few rows of data marked 5/20 and insert it without
generating a primary key error from all the other 5/20 rows that are
already inserted.

-DaveINSERT INTO TargetTable (key_col, ...)
SELECT S.key_col, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL
AND S.date >= '20050520' AND date < '20050522'

--
David Portas
SQL Server MVP
--|||The easy way is to limit the insert query to 11:59 59 of the previous
day. Then you tell your users, "this report contains all the data from
yesterday" In fact, if you're doing a report of some kind, this is
really the best way to do it because otherwise, you have incomplete
(and therefore bad) data for the current day.

Another way is to delete yesterday's data right before you run the
insert.|||Should the join run very slowly? If I do the insert with a standard
insert query it takes about 7 minutes. With the join query it runs and
doesn't seem to be able to finish. If I run the query on dates with no
data it finishes ok. Is my join incorrect since I can't use S.keyrow?

insert into final(keyRow, cell, recordDate, high_set )

SELECT CONVERT(CHAR(16),dateadd(hh,datepart(hh, .access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id) AS keyRow,
(CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)) AS cell,
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)as recordDate, SUM(S.high_set_int) AS high_set

from SourceTable AS S
LEFT JOIN TargetTable AS T
ON keyRow = T.keyRow

WHERE T.keyRow IS NULL
AND S.record_date >= '5/06/2005' AND S.record_date < '5/07/2005' AND
convert (char(8), S.access_time,108) != '00:00:00'

GROUP BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+
CONVERT(CHAR(3), S.bts_id)+'-'+CONVERT(CHAR(1),
S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id)

ORDER BY CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20),
CONVERT(CHAR(16),dateadd(hh,datepart(hh, S.access_time),
S.record_date),20)+'|'+CONVERT(CHAR(3), S.bts_id)+'-'+
CONVERT(CHAR(1), S.sector_id)+'-'+CONVERT(CHAR(3), S.carrier_id),
S.cell|||
christopher.secord@.gmail.com wrote:
> The easy way is to limit the insert query to 11:59 59 of the previous
> day. Then you tell your users, "this report contains all the data from
> yesterday" In fact, if you're doing a report of some kind, this is
> really the best way to do it because otherwise, you have incomplete
> (and therefore bad) data for the current day.

Yes, I agree but the way the data is generated results in "today's"
data flat file containing some of yesterday's data. So although 99% of
yesterday's data is already in the db, the last little bit needs be
added for completeness. It's not that the nearly all users can't use
the 99% data for their purposes but still the missing 1% needs to be
added for later complete, accurate reports.

> Another way is to delete yesterday's data right before you run the
> insert.

This puts the problem back 1 day because I would still need to add
yesterday's data which is in its own flat file which contains data from
the day before yesterday.

-David|||David Portas wrote:
> INSERT INTO TargetTable (key_col, ...)
> SELECT S.key_col, ...
> FROM SourceTable AS S
> LEFT JOIN TargetTable AS T
> ON S.key_col = T.key_col
> WHERE T.key_col IS NULL
> AND S.date >= '20050520' AND date < '20050522'

I'm thinking maybe the best thing to do is add another column to my
table that uniquely identifies the data from a particular day. Some of
the data from the particular flat file will be from the day before but
it won't matter because I'll use the new field in the where criteria
instead of the actual record dates.

Also thought about using NOT EXISTS somehow.

-Dave|||Make sure you have indexes on the columns that are being joined.|||(wireless200@.yahoo.com) writes:
> Should the join run very slowly? If I do the insert with a standard
> insert query it takes about 7 minutes. With the join query it runs and
> doesn't seem to be able to finish. If I run the query on dates with no
> data it finishes ok. Is my join incorrect since I can't use S.keyrow?

I don't understand that last question. What do you mean, you cannot
use S.keyrow?

A clustered index on S.record_date would be a good thing.

I would also replace the LEFT JOIN with NOT EXISTS. Not because this
is faster, but because expresses what you mean.

Does the target table have an IDENTITY column? Else there is no reason at
all to have the ORDER BY clause. Removing that could also gain some
performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to construct the query in Stored Proc

Hi,
In the past I build up the query string within VB.NET page and easy to add the filtering statements in the SQL statement since it is just a string. For example, if user selected an option then include it in the filter, otherwise, just return all rows from table:
Codes in ASPX.VB:
Dim SQL as String="SELECT * From Table1"
IF UserOption <> Null then
SQL = SQL & " WHERE Column1=" UserOption"
End if

Now, since I have a complicated page which need to use Stored Procedure to manapulate a temporary table before the final result. But I found when I want to add some user options similar to above, I found I don't know how to do it in Stored Procedure. In the Stored Procedure Property screen, I can't insert a IF..THEN statement within a SELECT statement. Seems I can only check the user option first and then determine the SELECT statement to use. That is: IF UserOption THEN SELECT statement 1 ELSE SELECT statement 2.
But it is impossible for me to do this way since I'm not only one user option on the page. User usually can have several filters/selections on his screen. So if check which user option(s) are selected and write a static SELECT statement for it, I will have to program a complicated store procedure to cater all combinations for all user options (where some options may be null).
Hope you can understanding what my mean and give me advices.
Regards,
Raymond

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

|||

I n SQL Server you can use the CASE statement, try the links below for sample code. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.4guysfromrolla.com/webtech/102704-1.shtml

|||

Thanks a lot! I will study it. I also find this structure may help, which introducedd in another thread in this forum:

AND ([DD1 Industry Code] LIKE @.FilterIndustries OR @.FilterIndustries ='')

|||

scu wrote:

Hi,
you can use in Store Proc query dinamic creation :
CREATE PROCEDURE XXXX

@.OPTION1 INT

AS

DECLARE @.MYQUERY VARCHAR(1000)
DECLARE @.MYFILTER VARCHAR(1000)

SET @.MYQUERY = 'SELECT * FROM Table1 WHERE WHERE ID <> 0'

IF @.OPTION1 <> ''
BEGIN
SET @.MYFILTER = ' AND NegozioNome = ' + CONVERT(VARCHAR(20),@.OPTION1)
END

EXEC (@.MYQUERY + @.MYFILTER)

Will it affect the performance when the query is built up in a dynamic string rather than build up within stored procedure using CASE/IF..THEN..ELSE/COALESCE statements? It is important concern to the effectiveness when the store procedure contains complicated queries.|||Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.
|||

scu wrote:

Hi,
I've used many times dinamic queries in Store Proc.
The performance's difference between dinamic queries and equivalent 'static' storec proc is imperceptible.

Thanks for the kindly sharing from all of you!|||You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick|||

nick-w wrote:

You will get better performance out of a dynamic SQL statement than you would using CASE statements.
See this link for more information on dynamic T-SQL and different ways to handle your query:
http://www.sommarskog.se/dyn-search.html

Nick

Great article! Thanks Nick!

How to construct SQL SELECT statement

I want to search an entire table for a particular keyword but i'm not sure how, if the keyword was TEST then I want to return rows where any of the fields contain TEST, THIS IS A TEST, PLEASE TEST THIS etc etc i.e. the keyword can be anywhere in the fields value

I believe I need to use the LIKE clause but i'm not sure how.

Thanks

BenSelect * From Table Where Field Like '%' + sSearch.Replace("'","''") + '%'|||Hi Ben,

You're right, you can use the LIKE clause,
E.g.
SELECT * FROM Students WHERE StudentName LIKE '%NEW%'

Then the matched records include: HAPPY NEW YEAR and ASP.NEW :)

Regards,|||Hi Colt, thanks for that

If I wanted to apply the search to more than one fields could I use

SELECT * From Students WHERE StudentName, Field2, Field3, Field4 LIKE '%NEW%'

Is that the correct syntax?

Ben|||Hi,

You may try:

SELECT *
From Students
WHERE StudentName LIKE '%NEW%' OR
 Field2 LIKE '%NEW%' OR
 Field3 LIKE '%NEW%' OR
 Field4 LIKE '%NEW%'

Regards,

How to configure Smo.Scripter to get desired sprocs

Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true).

When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements?

Example:

private void CreateScripter() {
if( this._SprocSscripter == null ) {
this._SprocSscripter = new Scripter();
this._SprocSscripter.Server = this._tables.Parent.Parent;
this._SprocSscripter.Options.IncludeHeaders = true;
this._SprocSscripter.Options.IncludeIfNotExists = true;
this._SprocSscripter.Options.DdlBodyOnly = false;
this._SprocSscripter.Options.DdlHeaderOnly = false;
this._SprocSscripter.Options.ExtendedProperties = true;
this._SprocSscripter.Options.ScriptDrops = true;
}
}

OUTPUT:

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectCategories]

OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;)

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories]
@.CategoryID [int]
AS
SELECT [CategoryID],
[CategoryName],
[Description],
[Picture]
FROM [dbo].[Categories]
WHERE [CategoryID] = @.CategoryID
'
END

If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;)
The output looks like this:

DROP PROCEDURE [dbo].[usp_selectCategories]

That's it-- no sproc body.. what gives?

Bill Graziano did a great job of documenting how to do this at his SQLTeam.com web site - here's the link to the article:

http://www.sqlteam.com/item.asp?ItemID=23185

|||Excellent. Thank you. Too bad it's a bug, but learning that it is has restored some of my sanity :-)|||

|||

How to configure Smo.Scripter to get desired sprocs

Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true).

When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements?

Example:

private void CreateScripter() {
if( this._SprocSscripter == null ) {
this._SprocSscripter = new Scripter();
this._SprocSscripter.Server = this._tables.Parent.Parent;
this._SprocSscripter.Options.IncludeHeaders = true;
this._SprocSscripter.Options.IncludeIfNotExists = true;
this._SprocSscripter.Options.DdlBodyOnly = false;
this._SprocSscripter.Options.DdlHeaderOnly = false;
this._SprocSscripter.Options.ExtendedProperties = true;
this._SprocSscripter.Options.ScriptDrops = true;
}
}

OUTPUT:

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectCategories]

OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;)

/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories]
@.CategoryID [int]
AS
SELECT [CategoryID],
[CategoryName],
[Description],
[Picture]
FROM [dbo].[Categories]
WHERE [CategoryID] = @.CategoryID
'
END

If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;)
The output looks like this:

DROP PROCEDURE [dbo].[usp_selectCategories]

That's it-- no sproc body.. what gives?

Bill Graziano did a great job of documenting how to do this at his SQLTeam.com web site - here's the link to the article:

http://www.sqlteam.com/item.asp?ItemID=23185

|||Excellent. Thank you. Too bad it's a bug, but learning that it is has restored some of my sanity :-)

How to concatenate constants and expressions in report field

I was receiving [BC30205] End of statement expected Error
when I tryed to concatenate:

=INT(AVG(Fields!HT.Value)/360) ":" &INT(AVG(Fields!HT.Value)/60)

Please hlp!
Thank you.

You must convert to a string type.

= CSTR(INT(AVG(Fields!HT.Value)/360))+":"+CSTR(INT(AVG(Fields!HT.Value)/60))

how to concat/pivot rows to column?

hello,

I'm wondering how it's possible to have a select statement resultant rows concatenated into one row and column.
For example:
select letter from alphabet_table
a
b
c
d
e
...
26 rows returned.

Other than a cursor, how would I write a query to return the following:
row1: abcdefghijkl...

thanks in advance!There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP|||Originally posted by Pat Phelan
There are a number of ways, none of which is truly generic (ie there isn't a "one size fits all" choice). Without understanding both what lead you to want to concatenate these values (and what rules you use to concatenate them), and what you will do with the concatenated result, I can't give you much useful advice.

-PatP

PatP, thanks for your reply. After posting I realize I should have included more information.
Here's more specifics:
CREATE TABLE [elements] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[description] [varchar] (50) NULL ,
[code] [varchar] (5000) NOT NULL ,
[ord] [int] NOT NULL
) ON [PRIMARY]
GO

elements.code contains html tags, such as table, tr, td. I am using a stored procedure to build html code based on an input parameter. The parameter matches the 'name' column.
so to build a table, i would select the code and order by the ord column. the result is similar to the following:
<table width="100%" border="0">
<tr>
<td>
</td>
<td>
</td>
<td>
</td>
</tr>
</table>
(10 rows).
I would like to query the table based on the parameter passed to return the same results, except in one record:
<table width="100%" border="0"><tr><td></td><td></td><td></td></tr></table>
(1 row).

hope this helps clear it up|||That helps a bunch. The biggest problem that I see is that you can't allow your html table definition to exceed 4000 characters if you use 16 bit characters (aka UTF-8), or 8000 characters if you use 8 bit (OEM) characters. This could be a real problem for complex pages.

With that said, I'd start with:CREATE FUNCTION dbo.tableDef(@.name AS VARCHAR(50) RETURNS VARCHAR(8000) AS BEGIN
DECLARE
@.c VARCHAR(5000)
, @.r VARCHAR(8000)

SELECT @.r = ''
DECLARE z1 CURSOR FOR SELECT [code]
FROM [elements]
WHERE name = @.name
ORDER BY ord

OPEN z1
FETCH z1 INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z1 INTO @.c
END

CLOSE z1
DEALLOCATE z1

RETURN @.r
END-PatP|||Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP|||Originally posted by Pat Phelan
Oh yeah, usage would help, wouldn't it ? Sorry!SELECT [name], dbo.tableDef([name])
FROM [elements]
GROUP BY [name]-PatP

many thanks, Pat. i was hoping there was a 'simpler' method of reaching this goal. sometimes i wish i could rewrite ms's implementation of the ansi select to include special tricks.
like: select + * from blah would concat results. ;)

i'll let you know how it works, i'm not too worried about the 4/8k character limit, i can always have a couple of columns.

thanks again.

How to Combine two column in one table using SQL statement ?

Could you write the simple SQL statement from 'Combine two column in one table '?

I try to use 'Union' which combine two column in two table . thxYou can use the concatention operator (+):

SELECT
 column1 + column2 AS myColumn
FROM
 myTable

Terri|||If the columns are numeric then "+" will sum the column values.
If you do not desire this, you should use str() function

Check the below statement,

select ltrim(str(column1)) + ltrim(str(column2)) from mytable

How to combine to boolean collumns ?

Hi, my name is Wilhelm and I'm new to this forum.

I having problems with a SQL SELECT statement. I'm trying to combine two boolean columns in ms sql server 2000.

I tried this:

SELECT (Bol1 OR Bol2) as Bol

Anyone have any suggestion why this don't work ?
I think i have done something similar in Orcale ones.SELECT (Bol1 | Bol2) as Bol|||I just found out. Thanks anyway :)

How to combine MAx statement with specific search?

Hello everyone!

I've got a table (of "Persons") with 2 columns: "Age" and "Name".
I make a procedure that get the MAX of "Age" of the table, like this:

SELECT
MAX (p.Age) MaxAge
FROM
Persons p

But I'll like to add a new column to my result table. That column corresponds to the "Age" of a person 'X'. 'X' is its 'Name' and i will pass it by parameter to the procedure.
Something like this:

SELECT

MAX (p.Age) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X

FROM

Persons p

That should returns a single row result table with the max of all 'Age" and the 'Age' of the person 'X'. Anyone can help me to do this?

Thank you to everyon, and a happy new year

Jonathan

You can use a subquery, a derived table or store the result in a variable.

i.e. the sub query one looks like this

select (select max(age) from person) as maxAge, age

from person

where name = @.name

|||

You can use the following query,

SELECT
(Selecct MAX (Age) From Persons) MaxAge, --The highest age of the table
p.Age ConcretPersonAge -- The age of the person X
FROM
Persons P

|||Tahnk you very much. But I think I've simplified too much my query problem. I've got a third column. That its' cities. The original table is actually "Age", "Person","City". I need the maximum age and the age of the person X in each city (multiple rows result table). Could you help me?

Thank you evryone, specially:Mani and Simon.|||

In which case a derived table is the best option

select maxAge.age

, person.age

, city

from (select max(age) age, city from person where name = @.name group by city ) maxage

join person on person.city = maxage.city

where name = @.name

how to code sql comments within sql statement

I want to write a query like this:

select ' select count(*) '+ '/*' + table_name + '*/' + char(10) + ' FROM '+table_name...

The result should looks like this:

--
select count(*) /* - emp_name */
FROM emp_name

query result:

COUNT(*)-EMP_NAME
-
3

-
But, now I can only got

COUNT(*)
-
3

What's wrong with my original code?Actually, I am getting

- as the result, I think I just need to find out how to turn headings on.
3

Thanks|||

Well... you are comment out whatever is written. Comment out means that it will be ignored, it will not show up in the results, etc.

It looks like you want to create column headers for aggregrated columns. Fortunately, you can use an ALIAS for that task.

For example:

Code Snippet

SELECT
count(*) AS 'count(*) --Emp_Name'
FROM MyTable

Of course, whatever is typed as the ALIAS has to be know in advance, it will not dynamically put in the column or table name.

|||
Thanks, Arnie:

I got it now.

how to check whether sql statement is executed

Hi,
I have a few sql statements in asp. How do I check that the first sql statement is executed before the second one? If the first sql statement does not execute, then the second one will not execute too.Varierty of ways...
What queries are we talking about... can you post them? Are we talking SELECTs or UPDATE/DELETEs ?|||Originally posted by autumn6
Hi,

I have a few sql statements in asp. How do I check that the first sql statement is executed before the second one? If the first sql statement does not execute, then the second one will not execute too.

May be @.@.ROWCOUNT (Returns the number of rows affected by the last statement) will help you to find decision. You can get value from ADO.|||how about a stored procedure that has all the statements with an IF after the first one?|||Originally posted by LFN
Varierty of ways...
What queries are we talking about... can you post them? Are we talking SELECTs or UPDATE/DELETEs ?

It's a few INSERT statements.|||Its my understading that an INSERT operation will either succeed - or fail with an error (not necessarily a fatal error though)

So if its just INSERTS youre doing then check for a non-fatal error. Obviously a fatal error will abort your code anyway.

You might want to consider using a transaction... just to make sure they all go - or none as required...

Then after each INSERT do the error check...

IF (@.@.ERROR <> 0) ... [ take action - maybe a goto error routine ].....

That will catch any non-fatal errors - if there wasnt one the INSERT worked.

LFN