Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Tuesday, March 27, 2012
Best Strategy to backup the system db
Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate
Best Strategy to backup the system db
Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate
Monday, March 19, 2012
Best Practice when copy table from srv to srv
Hi!
My first post in this great forum. :)
Here goes:
I need some feedback on best practice (or just possible practice!) on creating a copy of a table from one SQLserver to another SQLserver.
I have a stored proc that loops some srv/databases/table-names and need to copy a specific table out to them all.
It works ok on the local server, but when i want to go across to another server trouble starts.
I have tried various approaches.
1) Linked server followed by "Insert into remotesrv.remotedb.dbo.tabel..."
result: cant run ALTER query in remote srv. SELECT statements works fine though.
2) Replication/Subscription
result: Works in general, but it only syncronizes alike tabels. Cant alter structure of table on remote.
3) DTS
result: Works fine, but not generic enough (variable tablenames needed).
What do you guys use in these situations?Ok no replys :)
For future reference I chose the following:
If fact 2) Replication/Subscription are open for alterations of tabel stucture.(I just needed to refresh my snapshot-file in the test)
The copy of tables are therefore done via replication triggerede by a stored procedure.
Thursday, March 8, 2012
best practice analyzer "copy report" but no "print report" -more info
I reinstalled the software and I also installed the software on another machine. The problem still exists. One machine is W2K sever sp4 and the other is XP Pro sp1
Unfortunately it is a documentation bug. Print functionality was removed
from BPA (available in the beta) but was missed from the documentation. :-(
The two possible ways to achieve print are:
1) use "copy". After clicking on it you should be able to paste in a variety
of formats, word, excel, etc.
2) use SQL Server Reporting Services. This also has many formats (pdf,
excel, html) you can use to generate & print reports.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:7AE5FBD7-3C64-4AB5-B20D-60316BF9F10D@.microsoft.com...
> I reinstalled the software and I also installed the software on another
machine. The problem still exists. One machine is W2K sever sp4 and the
other is XP Pro sp1
Unfortunately it is a documentation bug. Print functionality was removed
from BPA (available in the beta) but was missed from the documentation. :-(
The two possible ways to achieve print are:
1) use "copy". After clicking on it you should be able to paste in a variety
of formats, word, excel, etc.
2) use SQL Server Reporting Services. This also has many formats (pdf,
excel, html) you can use to generate & print reports.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:7AE5FBD7-3C64-4AB5-B20D-60316BF9F10D@.microsoft.com...
> I reinstalled the software and I also installed the software on another
machine. The problem still exists. One machine is W2K sever sp4 and the
other is XP Pro sp1
Wednesday, March 7, 2012
Best Performance
I wanna know. I have 2 ways to copy data.
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:
>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:
>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
best option when master db is not available
i want to setup my database to new server machine. i have backup as well as copy of database files (data & log), but i lost my master database from existing server.
so i have 2 ways of having my database
1. restore from backup
2. attach the data files.
which one is best option, when we start afresh on new machine with new master database.
Either method should work. I would prefer the RESTORE.
IF the file location (folder) is different, you may need to add the WITH MOVE option to the RESTORE.
Refer to Books Online, Topic: RESTORE
|||yes both options will work.......i'll advise you to go with restore db from backup but ensure that you start SQL Server in single user mode and then only you can perform restoration..........refer BOL its the best resource|||thanks for your replies. I have gone thru the BOL.
One thing i need to reconfirm before i proceed, with the loss of master database i lost all the information that the master database hold, in this situation, which of the option will be best.
|||i feel that there is some confusion in the requirement. ie Whether u r trying to resotre Master Database or User database. To restore a master database of one machine to another machine you have many restriction. OS/SQL SErver Version/Service pack and configuration (if i remember correctly) should be the same. I have also read somewhere that the backup of the same physical machine can ionly be restored(i have never tried this).
To transfer the objects from one server to other there are scripts available. You need to transfer Login/Jobs/DTS. THis is possible through Scripts.
(a) Install new instance of sql server in new machine
(b) transfer the LOgin refer : http://support.microsoft.com/default.aspx/kb/246133
(c) Make script of JOBs and run the script in the destination
(d) use save as option or file object trasfer for DTS
(e) Use Backup /restore for user databases.
Madhu
|||there should not be any confusion, i want to restore user database.
since my old master db is lost, i lost login, etc. logins i can recreate manually, but i am worried is there is thing else about my User database which is lost along with master db, which i may not recover.
|||All of the data in the users databases should be intact.
Any Jobs would be in the msdb database, so if you didn't lose msdb, you still have the jobs.
Most likely, the only thing lost is logins.
Saturday, February 25, 2012
Best indexing option?
Hi,
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECTptc.CustomerId, dc.DocumentContactId
FROMdts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?
I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECTptc.CustomerId, dc.DocumentContactId
>FROMdts.Customer AS ptc
>INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
>AND ptc.Line1 = dc.Line1
>AND ptc.Line2 = dc.Line2
>AND ptc.City = dc.City
>AND ptc.State = dc.StateName
>AND ptc.Country = dc.CountryName
>AND ptc.Email = dc.Email
>AND ptc.PhoneNumber = dc.PhoneNumber
>AND ptc.FaxNumber = dc.FaxNumber
>AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECTptc.CustomerId, dc.DocumentContactId
FROMdts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?
I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECTptc.CustomerId, dc.DocumentContactId
>FROMdts.Customer AS ptc
>INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
>AND ptc.Line1 = dc.Line1
>AND ptc.Line2 = dc.Line2
>AND ptc.City = dc.City
>AND ptc.State = dc.StateName
>AND ptc.Country = dc.CountryName
>AND ptc.Email = dc.Email
>AND ptc.PhoneNumber = dc.PhoneNumber
>AND ptc.FaxNumber = dc.FaxNumber
>AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?
Best indexing option?
Hi,
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECT ptc.CustomerId, dc.DocumentContactId
>FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?|||Andy,
If there is no index on any of the columns in the ON-clause, then
hashing is to be expected.
I suggest you experiment a bit with (compound) indexes. Start with the
column that is most selective, and add (other selective) column until
the total (compound) index has enough selectivity to be fast. I wouldn't
be surprised if it is enough to just index column Email, because it is
probably highly selective.
Just add a lot of indexes on both tables, check the query plan to see
which are used, and drop the unused indexes.
HTH,
Gert-Jan
Andy wrote:
> Hi,
> I have to copy some data from one table to another. Its billing
> information from an invoice to a Customer table.
> The requirement is that if ANY billing information for a customer
> changes, a new Customer record must be created. Currently I have this
> query to do that.
> SELECT ptc.CustomerId, dc.DocumentContactId
> FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
> This query is used by another view to get the customer id (which is
> generated later and stored in the Customer table.
> I noticed in the execution plan that there is a hash which is includes
> CompanyName, dc.Email, and I believe the last part of the join, the
> ContactName, although I'm not sure because the Execution plan
> includings [Exp1004] = [Exp1005].
> Any other suggestions to make this more performant? Or am I limited?
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECT ptc.CustomerId, dc.DocumentContactId
>FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?|||Andy,
If there is no index on any of the columns in the ON-clause, then
hashing is to be expected.
I suggest you experiment a bit with (compound) indexes. Start with the
column that is most selective, and add (other selective) column until
the total (compound) index has enough selectivity to be fast. I wouldn't
be surprised if it is enough to just index column Email, because it is
probably highly selective.
Just add a lot of indexes on both tables, check the query plan to see
which are used, and drop the unused indexes.
HTH,
Gert-Jan
Andy wrote:
> Hi,
> I have to copy some data from one table to another. Its billing
> information from an invoice to a Customer table.
> The requirement is that if ANY billing information for a customer
> changes, a new Customer record must be created. Currently I have this
> query to do that.
> SELECT ptc.CustomerId, dc.DocumentContactId
> FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
> This query is used by another view to get the customer id (which is
> generated later and stored in the Customer table.
> I noticed in the execution plan that there is a hash which is includes
> CompanyName, dc.Email, and I believe the last part of the join, the
> ContactName, although I'm not sure because the Execution plan
> includings [Exp1004] = [Exp1005].
> Any other suggestions to make this more performant? Or am I limited?
Best indexing option?
Hi,
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECT ptc.CustomerId, dc.DocumentContactId
>FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?|||Andy,
If there is no index on any of the columns in the ON-clause, then
hashing is to be expected.
I suggest you experiment a bit with (compound) indexes. Start with the
column that is most selective, and add (other selective) column until
the total (compound) index has enough selectivity to be fast. I wouldn't
be surprised if it is enough to just index column Email, because it is
probably highly selective.
Just add a lot of indexes on both tables, check the query plan to see
which are used, and drop the unused indexes.
HTH,
Gert-Jan
Andy wrote:
> Hi,
> I have to copy some data from one table to another. Its billing
> information from an invoice to a Customer table.
> The requirement is that if ANY billing information for a customer
> changes, a new Customer record must be created. Currently I have this
> query to do that.
> SELECT ptc.CustomerId, dc.DocumentContactId
> FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
> This query is used by another view to get the customer id (which is
> generated later and stored in the Customer table.
> I noticed in the execution plan that there is a hash which is includes
> CompanyName, dc.Email, and I believe the last part of the join, the
> ContactName, although I'm not sure because the Execution plan
> includings [Exp1004] = [Exp1005].
> Any other suggestions to make this more performant? Or am I limited?
I have to copy some data from one table to another. Its billing
information from an invoice to a Customer table.
The requirement is that if ANY billing information for a customer
changes, a new Customer record must be created. Currently I have this
query to do that.
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
INNER JOIN dbo.vDocumentContactDetail AS dc ON
ptc.CustomerName = dc.CompanyName
AND ptc.Line1 = dc.Line1
AND ptc.Line2 = dc.Line2
AND ptc.City = dc.City
AND ptc.State = dc.StateName
AND ptc.Country = dc.CountryName
AND ptc.Email = dc.Email
AND ptc.PhoneNumber = dc.PhoneNumber
AND ptc.FaxNumber = dc.FaxNumber
AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
This query is used by another view to get the customer id (which is
generated later and stored in the Customer table.
I noticed in the execution plan that there is a hash which is includes
CompanyName, dc.Email, and I believe the last part of the join, the
ContactName, although I'm not sure because the Execution plan
includings [Exp1004] = [Exp1005].
Any other suggestions to make this more performant? Or am I limited?I don't quite understand the requirement. From looking at the SELECT
it appears that it looks for a perfect match on everything EXCEPT the
keys, and returns the two keys. I can't say that I've ever seen
anything like that before except when merging two unrelated data
sources or resolving data redundancy problems.
Making a wild guess, I am wondering if the main thing done with
ptc.CustomerId and dc.DocumentContactId when they are returned is
compare them to see if they are the same so that some action (new
Customer record) can be taken if they are not. If so the entire
process appears to me to be inside out.
What I usually see, and write, when I want to check for changes:
SELECT ptc.CustomerId, dc.DocumentContactId
FROM dts.Customer AS ptc
JOIN dbo.vDocumentContactDetail AS dc
ON ptc.CustomerId = dc.DocumentContactId
WHERE ptc.CustomerName <> dc.CompanyName
OR ptc.Line1 <> dc.Line1
OR ptc.Line2 <> dc.Line2
OR ptc.City <> dc.City
OR ptc.State <> dc.StateName
OR ptc.Country <> dc.CountryName
OR ptc.Email <> dc.Email
OR ptc.PhoneNumber <> dc.PhoneNumber
OR ptc.FaxNumber <> dc.FaxNumber
OR dc.FirstName + ' ' + dc.LastName <> ptc.ContactName
That returns the keys that have differences. I don't know if that is
what you need, but maybe it is.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 06:35:23 -0800, Andy <andyj@.med-associates.com>
wrote:
>Hi,
>I have to copy some data from one table to another. Its billing
>information from an invoice to a Customer table.
>The requirement is that if ANY billing information for a customer
>changes, a new Customer record must be created. Currently I have this
>query to do that.
>SELECT ptc.CustomerId, dc.DocumentContactId
>FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
>This query is used by another view to get the customer id (which is
>generated later and stored in the Customer table.
>I noticed in the execution plan that there is a hash which is includes
>CompanyName, dc.Email, and I believe the last part of the join, the
>ContactName, although I'm not sure because the Execution plan
>includings [Exp1004] = [Exp1005].
>Any other suggestions to make this more performant? Or am I limited?|||Andy,
If there is no index on any of the columns in the ON-clause, then
hashing is to be expected.
I suggest you experiment a bit with (compound) indexes. Start with the
column that is most selective, and add (other selective) column until
the total (compound) index has enough selectivity to be fast. I wouldn't
be surprised if it is enough to just index column Email, because it is
probably highly selective.
Just add a lot of indexes on both tables, check the query plan to see
which are used, and drop the unused indexes.
HTH,
Gert-Jan
Andy wrote:
> Hi,
> I have to copy some data from one table to another. Its billing
> information from an invoice to a Customer table.
> The requirement is that if ANY billing information for a customer
> changes, a new Customer record must be created. Currently I have this
> query to do that.
> SELECT ptc.CustomerId, dc.DocumentContactId
> FROM dts.Customer AS ptc
> INNER JOIN dbo.vDocumentContactDetail AS dc ON
> ptc.CustomerName = dc.CompanyName
> AND ptc.Line1 = dc.Line1
> AND ptc.Line2 = dc.Line2
> AND ptc.City = dc.City
> AND ptc.State = dc.StateName
> AND ptc.Country = dc.CountryName
> AND ptc.Email = dc.Email
> AND ptc.PhoneNumber = dc.PhoneNumber
> AND ptc.FaxNumber = dc.FaxNumber
> AND dc.FirstName + ' ' + dc.LastName = ptc.ContactName
> This query is used by another view to get the customer id (which is
> generated later and stored in the Customer table.
> I noticed in the execution plan that there is a hash which is includes
> CompanyName, dc.Email, and I believe the last part of the join, the
> ContactName, although I'm not sure because the Execution plan
> includings [Exp1004] = [Exp1005].
> Any other suggestions to make this more performant? Or am I limited?
Sunday, February 12, 2012
Beginner need help ... with triger?
I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database whenever the data is inserted or updated. I assume the way to do that is thru the use of a trigger, but have no clue how to begin.
For example:
db #1 Table User has fields;
id, name, address, zip, password, date
db #2 table n_user has fields;
id, name, password
Hope thats enough information to get some assistance...use [Your active DB]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([id], [name], [password])
select [id], [name], [password]
from inserted
end|||Thanks for you help.
I made the trigger but get this error on update of one field value.
Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.
Any clue??|||Difference between history and active table is difference between picture and movie.
You cannot use the same PK in both tables.
In [Your history DB].dbo.[n_user] table:
1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.
3,Modify trigger:
use [Your active DB]
GO
alter trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
delete [Your history DB].dbo.[n_user]
declare @.x int
update dbo.[user] set @.x=1
4...This will help sometimes
Adding FingerPrint timestamp column NOT NULL
Adding CreatedDate datetime column NOT NULL with default getdate().
5,Securing history table:
use [Your history DB]
GO
Deny insert,update,delete,references on dbo.[n_user] to public|||I see parts in the code for insert and delete... what about update?
Again... Thank you very much for your help.|||/*
You wrote:
"I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
WHENEVER THE DATA IS INSERTED OR UPDATED."
So you did not specify DELETED info.
*/
/* COMMENTED CODE */
--Switching to your active DB
use [Your active DB]
GO
--Creates trigger FOR INSERT,UPDATE on dbo.[user]
--This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
--( Look at "inserted tables" topic in BOL )
if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
--Deleting test rows in HISTORY
delete [Your history DB].dbo.[n_user]
--Filling history table with previosly inserted data (prehistoric)
declare @.x int
update dbo.[user] set @.x=1|||Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly. :)
However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.
Here is the exact trigger that causes that error.
==============
CREATE trigger [TR_Players(,I,U,)]
on dbo.[players]
for insert,update
as begin
insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
Now I went ahead and edited the trigger to be this,
=============
CREATE trigger [TR_Players(,I,)]
on dbo.[players]
for Insert
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
As you might expect... this fires off correctly and both tables get their new information.
Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.
I tried to add a second trigger to the same table just for updates like such.
=============
CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?
Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?
Like always... thanks very much for your time to deal with my issues..|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end
--for more See http://dbforums.com/showthread.php?threadid=640545|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end
--for more See http://dbforums.com/showthread.php?threadid=640545|||You are like a God to me! Works great. Thank You Thank you! :)|||It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.
I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
table players with the new password. Here is what I came up with...
---------------
CREATE trigger [TR_pw(,U,)]
on [Forum].dbo.[FORUM_MEMBERS]
for Update
as begin
update pl set
pl.Ppassword = i.[M_password]
from dbo.[players] pl
join inserted i
on pl.pemail=i.m_name --PK Join - verify
end
---------------
Thanks.|||Your triggers are probably chaining, learn more about nesting
http://dbforums.com/showthread.php?threadid=640545|||you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.
Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?|||Stike that...
I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?
Thanks for your help.|||You can put "if trigger_nestlevel(@.@.procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
In most cases you do not need it.
--Ver1
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
insert B select * from inserted
GO
create trigger tiB on B for insert as
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B
--Ver2
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert B select * from inserted
GO
create trigger tiB on B for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B
Good luck !
For example:
db #1 Table User has fields;
id, name, address, zip, password, date
db #2 table n_user has fields;
id, name, password
Hope thats enough information to get some assistance...use [Your active DB]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([id], [name], [password])
select [id], [name], [password]
from inserted
end|||Thanks for you help.
I made the trigger but get this error on update of one field value.
Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.
Any clue??|||Difference between history and active table is difference between picture and movie.
You cannot use the same PK in both tables.
In [Your history DB].dbo.[n_user] table:
1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.
3,Modify trigger:
use [Your active DB]
GO
alter trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
delete [Your history DB].dbo.[n_user]
declare @.x int
update dbo.[user] set @.x=1
4...This will help sometimes
Adding FingerPrint timestamp column NOT NULL
Adding CreatedDate datetime column NOT NULL with default getdate().
5,Securing history table:
use [Your history DB]
GO
Deny insert,update,delete,references on dbo.[n_user] to public|||I see parts in the code for insert and delete... what about update?
Again... Thank you very much for your help.|||/*
You wrote:
"I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
WHENEVER THE DATA IS INSERTED OR UPDATED."
So you did not specify DELETED info.
*/
/* COMMENTED CODE */
--Switching to your active DB
use [Your active DB]
GO
--Creates trigger FOR INSERT,UPDATE on dbo.[user]
--This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
--( Look at "inserted tables" topic in BOL )
if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
--Deleting test rows in HISTORY
delete [Your history DB].dbo.[n_user]
--Filling history table with previosly inserted data (prehistoric)
declare @.x int
update dbo.[user] set @.x=1|||Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly. :)
However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.
Here is the exact trigger that causes that error.
==============
CREATE trigger [TR_Players(,I,U,)]
on dbo.[players]
for insert,update
as begin
insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
Now I went ahead and edited the trigger to be this,
=============
CREATE trigger [TR_Players(,I,)]
on dbo.[players]
for Insert
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
As you might expect... this fires off correctly and both tables get their new information.
Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.
I tried to add a second trigger to the same table just for updates like such.
=============
CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end
=============
Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?
Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?
Like always... thanks very much for your time to deal with my issues..|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end
--for more See http://dbforums.com/showthread.php?threadid=640545|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end
--for more See http://dbforums.com/showthread.php?threadid=640545|||You are like a God to me! Works great. Thank You Thank you! :)|||It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.
I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
table players with the new password. Here is what I came up with...
---------------
CREATE trigger [TR_pw(,U,)]
on [Forum].dbo.[FORUM_MEMBERS]
for Update
as begin
update pl set
pl.Ppassword = i.[M_password]
from dbo.[players] pl
join inserted i
on pl.pemail=i.m_name --PK Join - verify
end
---------------
Thanks.|||Your triggers are probably chaining, learn more about nesting
http://dbforums.com/showthread.php?threadid=640545|||you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.
Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?|||Stike that...
I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?
Thanks for your help.|||You can put "if trigger_nestlevel(@.@.procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
In most cases you do not need it.
--Ver1
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
insert B select * from inserted
GO
create trigger tiB on B for insert as
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B
--Ver2
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert B select * from inserted
GO
create trigger tiB on B for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B
Good luck !
Subscribe to:
Posts (Atom)