|
I have a C# program that has a connection to 2 different databases across 2 different SQL servers.
How can I easily copy a table and all its contents from one database to another using SQL statements in C#
I do not want to use the management studio to perform this and want to do it in my C# program.
Any help would be appreciated.
Thanks
|
|
|
|
|
|
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
Niladri Biswas
|
|
|
|
|
|
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!
|
|
|
|
|
How is this different from your question yesterday about triggers in a certain time period?
|
|
|
|
|
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
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Hi Bob,
Thank you for your help.
When I use single quotes i get an error
Line 16: Incorrect syntax near 'SI'.
G
|
|
|
|
|
Try
and TransType =
''SI''
Niladri Biswas
|
|
|
|
|
Hello,
Generally here, apostrophe is considered as escape sequence character
You may execute as
TransType = ''SI''
This may solve you problem
|
|
|
|
|
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
|
|
|
|
|
Eventually got it
''''SI'''' Solved the problem.
Many Thanks
G.
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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...!!
|
|
|
|
|
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'
|
|
|
|
|
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
Niladri Biswas
|
|
|
|
|
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.
|
|
|
|
|
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
Niladri Biswas
|
|
|
|
|
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...
|
|
|
|
|
Got the solution.. below query will be workout
DELETE T1
FROM BugsDB_DefectVolume T1, BugsDB_DefectVolume T2
WHERE T1.[Product ID] = T2.[Product ID]
and T1.BugDate = T2.BugDate and T1.Timestamps < T2.Timestamps
|
|
|
|
|
Hi all,
I have two tables A(a1,a2,a3), B(b1,b2,b3) and a period of time Delta t [t1,t2].
When it is between t1 - t2 of time, if there is a row inserted in table A, the field b1 of table B will be updated such as b1=b1+1.
Moreover, I also have a web application which can assign value for t1, t2 through an aspx page.
Could you tell me how to do that?
Thank kiu so much.
|
|
|
|
|
If you are using SQL Server, you could set up a trigger on table a, so when a row is inserted it checks if the time is between t1 and t2, and if so update table b.
|
|
|
|