Database

Re: INSTEAD OF trigger [modified]

8-Dec-09 21:36

Solution 1:

Take the help of

SSIS

for the task. It is handy and simple.

Solution 2:

Using

SMO class

, you can create a backup of your database and restore it also.

There are plenty of articles in the net explaining how to do the same using SMO.

Please have a look

Backup and restore SQL databases[^]

Hope this helps
Smile | :)

Niladri Biswas

Re: SQL Copy Table in C#

Paul Voicu10-Dec-09 2:06

Paul Voicu

10-Dec-09 2:06

Try using SqlBulkCopy - google for that and check out the example here http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server[^]

INSTEAD OF trigger

Member 47041438-Dec-09 13:34

Member 4704143

8-Dec-09 13:34

Hi all,

I want to create a trigger relating to table Demo and table UpdateInfo. UpdateInfo is created as following:

CREATE TABLE UpdateInfo
(ID				int			primary key,
new				int,
click			int)

The task for this trigger is whenever there is a row inserted to Demo, there is an update in field [new] of UpdateInfo ( new = new +1) , but it wll just work, for example, from 1:00 am to 6:00 am.

If this has been done, I then want to customize the time from an aspx page.

I think I should start with INSTEAD OF trigger but I am lacking in experiences about it.

Could you give me any suggestion?

Thank you so much!

Re: INSTEAD OF trigger

David Skelly8-Dec-09 23:43

David Skelly

8-Dec-09 23:43

How is this different from your question yesterday about triggers in a certain time period?

Member 47041439-Dec-09 0:30

Member 4704143

9-Dec-09 0:30

yep,

But the answer I got yesterday is still dubious. I am still having no ideas about how to check time & check by which syntax.

The question today is one in my effort to find out solution for the question of yesterday, but in a different form Big Grin | :-D

I think INSTEAD OF trigger can help me to sovle prolem. That is the reason for the question today ^^

However, if you think just a standard trigger is quite enough to solve my problem, could you give me a more specific example?

Thanks for your help!

modified on Wednesday, December 9, 2009 8:36 AM

sqlserver apostrophe problem.

gerrybrennan7-Dec-09 20:54

gerrybrennan

7-Dec-09 20:54

Hi
I have a stored procedure schemacreate.sql and below is a snippet.

I am having problems with creating the below procedure. More specifically

and TransType = "SI"

Can any one help me with this.

Thank you in advance.

G

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF



IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[TransCheckifCurrentExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[TransCheckifCurrentExists]

as 
declare @SQLstr nvarchar(1000)
declare @value int;



set @SQLstr = ''select  @value=count(*) from Trans where TransState = 1 and TransType = "SI"  ''


exec sp_executesql @SQLstr, N''@value int out'', @value out
return


'
END

Ashfield7-Dec-09 20:56

Ashfield

7-Dec-09 20:56

Double quotes are not valid to enclose literals, you need single quotes. I would suugest before executing your sql string you print it (at least for debug) as it soon shows any syntax errors.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

gerrybrennan7-Dec-09 21:14

gerrybrennan

7-Dec-09 21:14

Hi Bob,

Thank you for your help.

When I use single quotes i get an error

Line 16: Incorrect syntax near 'SI'.

G

Niladri_Biswas7-Dec-09 21:42

7-Dec-09 21:42

Try

and TransType =

''SI''

Niladri Biswas

Nilesh.Kuvar7-Dec-09 23:28

Nilesh.Kuvar

7-Dec-09 23:28

Hello,

Generally here, apostrophe is considered as escape sequence character
You may execute as

TransType = ''SI''

This may solve you problem
Thumbs Up | :thumbsup:

Blue_Boy8-Dec-09 0:22

Blue_Boy

8-Dec-09 0:22

Use CHAR(39) for single apostrophe.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

gerrybrennan8-Dec-09 5:41

gerrybrennan

8-Dec-09 5:41

Eventually got it

''''SI'''' Solved the problem.

Many Thanks

G.

How to remove the duplicate records with the old timestamp.

Blue_Boy8-Dec-09 6:09

Blue_Boy

8-Dec-09 6:09

Good,but when you have complex query and in which you use multiple times single apostrophe then you can become confused by writing much '''' in query.

Regards.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

chakran7-Dec-09 20:00

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 20:00

Hi,

How to remove the duplicate records with the old timestamp. Below is the table, contains 2 columns Bugdate and timestamp. I wanted to remove the duplicate records with the old time stamp. ie first three records. It would be pleasure if any one send me the query.

BugDate----     Timestamp
4/14/2009     12/8/2009 12:54.611
4/19/2009     12/8/2009 12:54.611
4/26/2009     12/8/2009 12:54.611

4/14/2009     12/8/2009 12:58.623
4/19/2009     12/8/2009 12:58.623
4/26/2009     12/8/2009 12:58.623

Thanks.

dan!sh 7-Dec-09 20:58

dan!sh

7-Dec-09 20:58

Some threads below, people have told how t osearch duplicate records from a table. Check that out. Once you have the records, just find the ones with older timestamp and delete them.

I believe it should be a mere customization of the same query.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: How to remove the duplicate records with the old timestamp.

chakran7-Dec-09 21:16

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 21:16

I know the logic for how to get duplicate rows but I need to delete them. Below is the table and my logic for getting duplicate records with old timestamp. But I need to delete these records. Can you help by doing little modification in my code.

ProductID   BugDate     Bug     Timestamp
111           2009-04-12     1     2009-12-08 12:18:07.453
111           2009-04-14     2     2009-12-08 12:18:07.453
111           2009-04-19     1     2009-12-08 12:18:07.453

111           2009-04-12     1     2009-12-08 12:29:07.453
111           2009-04-14     3     2009-12-08 12:29:07.453
111           2009-04-19     2     2009-12-08 12:29:07.453

SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
   SELECT [Product ID] AS B, MIN([Timestamp]) AS S
   FROM BugsDB_DefectVolume
   GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111'

Niladri_Biswas7-Dec-09 21:35

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 21:35

Try this.

First I am giving a sample demo and then I will modify your query

Sample demo

declare @t table(bugdate varchar(10),timestamps datetime)
insert into @t 
	select '4/14/2009','12/8/2009 12:54.611' union all
	select '4/19/2009', '12/8/2009 12:54.611' union all
	select '4/26/2009', '12/8/2009 12:54.611' union all
	select '4/14/2009','12/8/2009 12:58.623' union all
	select '4/19/2009','12/8/2009 12:58.623' union all
	select '4/26/2009','12/8/2009 12:58.623'

Query:

delete from @t where timestamps in(
select MIN(timestamps) from @t
group by bugdate
having (COUNT(timestamps)>1))

select * from @t

Output:

bugdate	timestamps
4/14/2009	2009-12-08 12:58:00.623
4/19/2009	2009-12-08 12:58:00.623
4/26/2009	2009-12-08 12:58:00.623

Coming to your code:

Try this

delete from BugsDB_DefectVolume where [Timestamp] in(

select [Timestamp] from (

SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
   SELECT [Product ID] AS B, MIN([Timestamp]) AS S
   FROM BugsDB_DefectVolume
   GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111' )X)Y

Hope this helps
Smile | :)

Niladri Biswas

chakran7-Dec-09 22:12

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 22:12

but have one problem. if there are duplicate records that first time its working fine but when we execute second time all records were deleted from the table. I think we need to put a check some where in the logic.

Niladri_Biswas7-Dec-09 22:34

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 22:34

I guess, you can do 1 thing.

Put a check like if the timestamp field is not equal to getdate()

like

select

case when (timestamp = getdate())

then call the delete operation

end as checking

Hope this might help you
Smile | :)

Niladri Biswas

chakran7-Dec-09 22:41

Re: How to remove the duplicate records with the old timestamp.

7-Dec-09 22:41

it will not work out. but the example and query you sent in the previous reply is working fine even we executed query many times.

delete from @t where timestamps in(select MIN(timestamps) from @tgroup by bugdate having (COUNT(timestamps)>1))..

As you have used having clause I think we need to put the same thing in my query. I tired but getting syntax errors... Can you think once again...

chakran7-Dec-09 23:22