Compare Triggers Values

Hi.
How to create a trigger before insert that if a new row is giong to be
inserted in a table the trigger compares a value of a column from the
table with the value of a column in the record to be inserted...
Regards
Muhammad Bilal
<bilal_4x@.hotmail.com>
*** Sent via Developersdex http://www.examnotes.net ***Use of inserted table and deleted table in the trigger can help you.
According to BOL
two special tables are used in trigger statements: the deleted table
and the inserted table. SQL Server 2000 automatically creates and
manages these tables. You can use these temporary, memory-resident
tables to test the effects of certain data modifications and to set
conditions for trigger actions;
Look at books online for more details and samples.
Or
Post your table with DDL and data and describe your problem.
Regards
Amish

Compare records of two tables?

Hi

I am trying for compare two tables records and if which records are not match insert in to both table and at end both table records will be same.

using stored procedure & I need also pass server name database name.

thanks

I did not know how to use store procedures but i did use TableDiff.exe to compare both tables.

Here is the link:

http://www.replicationanswers.com/TableDiff2005.asp

Have a nice day

|||

Check out SQL Data Compare from RedGate.

Martin

compare merge and insert

I have 3 tables in a database,
Table 1 4 fields
ID = incremental
datetime = datetime
from = numeric
pin = numeric
Table 2 7 fields
ID = incremental
address1 = text
address2 = text
town = text
county = text
postcode = text
pin = numeric
Table 3 many fields selected a few
ID = incremental
address1 = text
address2 = text
town = text
county = text
postcode = text
datetime = datetime
what I need is a query that as a new record is updated in table 1 it goes to
table 2 matches the columns 'pin' and then creates a new entry in table 3
using the address fields.
Anyone help?
Thanks in advance
SimonSimon Gare wrote:
> I have 3 tables in a database,
> Table 1 4 fields
> ID = incremental
> datetime = datetime
> from = numeric
> pin = numeric
> Table 2 7 fields
> ID = incremental
> address1 = text
> address2 = text
> town = text
> county = text
> postcode = text
> pin = numeric
> Table 3 many fields selected a few
> ID = incremental
> address1 = text
> address2 = text
> town = text
> county = text
> postcode = text
> datetime = datetime
>
> what I need is a query that as a new record is updated in table 1
"New" record "updated"? Do you mean inserted?
it goes to
> table 2 matches the columns 'pin'
with what?
and then creates a new entry in table 3
> using the address fields.
Why are you duplicating data in your database?
Why are you using reserved words for your field names? (datetime)
Are you really using the "text" datatype for the fields above?
Mike Brind|||Use trigger should do the job.
Put a insert trigger on table 1 that insert table 2. On table 2 you
put another insert trigger that insert table 3.
Is it what you want?
Mel

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.

Sincerely,
-Lawrence

I don't know anything about the transactions logs.

Bulk insert is generally much faster.

Jonathan

|||

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. You can start with the links below for more information:

http://msdn2.microsoft.com/en-us/library/ms190421.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:
> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSERT
> vs INSERT? Performance speaking, which operations is generally faster given
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provider
in the OpenRowset function. But I have not tested the performance difference,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
> > Hello,
> > I am wondering is the Transaction Log logged differently between BULK INSERT
> > vs INSERT? Performance speaking, which operations is generally faster given
> > the same amout of data inserted.
> >
> > Sincerely,
> > -Lawrence
> >|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.
> Sincerely,
> -Lawrence
>

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:

> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSE
RT
> vs INSERT? Performance speaking, which operations is generally faster giv
en
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provide
r
in the OpenRowset function. But I have not tested the performance difference
,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
>|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.

> Sincerely,
> -Lawrence
>

Commit a loop of inserting

I use loop to insert few record into a table:
But the for_Loop only loop once and throw an error:
"The variable name '@.res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."
What should i do to get this fix?

Code:

Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim DataSources1 As New SqlDataSource()
DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

DataSources1.InsertCommandType = SqlDataSourceCommandType.Text
DataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@.res_name, @.my_menu)"

Dim c As Integer
For c = 0 To selectListBox.Items.Count - 1 Step +2

DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)
DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)
DataSources1.Insert()
Next
End Sub

your loop is trying the re-add the same parameters on each pass.

For c = 0To selectListBox.Items.Count - 1Step +2 DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text) DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text) DataSources1.Insert() DataSources1.InsertParameters.Clear()'remove the previous parametersNext

you can clear them after you perform the insert so they can then be re-added with new values on the next pass through the loop

|||thanks, now the loop works fine.

Comments in RDL

Hi
Is there a way to insert comments into the RDL, in such a way as that they
are persistent (i.e. they don't get lost when making changes to the report
and saving)?
Thanks,
MarkYou might want to check-out the <Custom> element. The contents of this
element is never interpreted by Reporting Services, but exposed to custom
rendering extensions, if you ever want to write your own extension and have
some semantics associated with your comments in the Custom element. When
using the Custom element you should place the custom properties under a
single subelement of Custom, defining a namespace for that node.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSRDL/htm/rsp_ref_rdl_elements_ae_3k38.asp
The drawback of the Custom element is that it is not exposed through report
designer, so would need to add the comments directly into the RDL (XML)
file.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"MCC" <cmc_za@.hotmail.com> wrote in message
news:%232wKyy5VEHA.1128@.TK2MSFTNGP10.phx.gbl...
> Hi
> Is there a way to insert comments into the RDL, in such a way as that they
> are persistent (i.e. they don't get lost when making changes to the report
> and saving)?
> Thanks,
> Mark
>

commas

how do i insert a string that contains a comma?Quotes around the comma.

","

CommandText

Dim MiSQL As String = "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)"
............
cm.Parameters.Add(New SqlParameter("@.IDCentro", SqlDbType.Int, 4)).Value = 15
cm.Parameters.Add(New SqlParameter("@.Proveedor", SqlDbType.NVarChar, 50)).Value = "IBM"
cm.Parameters.Add(New SqlParameter("@.Tipo", SqlDbType.TinyInt, 1)).Value = 35

Hi friens, its possible to get the string with the vaules of parameters changed?, i mean get this string in code:

INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (15,IBM,35);

I tried with CommandText but in this string are the variables and not the values...thx a lot.

No.

You can get something similiar if you you SQL Profiler, but it'll look something like:

sp_ExecuteSQL "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)",@.IDCentruo=N'15',@.Proveedor=N'IBM',@.Tipo=N'35'

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

Commands that cannot be used with mirroring

Hi al,

Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)

There are some database DDL options (ALTER DATABASE) that are not allowed. But for normal table/index DML/DDL and the command you give there should be no problems.

|||Alter database to change the recovery model, dropping the database, detaching the database, and restoring the database are not allowed. Other than that, if it is a command that you can issue inside the database to affect anything within a database, it is compatible with Database Mirroring.|||

Mr. Hotek,

So if restoring is not allowed during mirroring?

What is a good sequence of operations to put in a script if a database is updated once a month, like a snapshot for accounting comparisions?

Thanks,

Kimball Johnson

|||

Hi Kimball...Michael was saying that you can't restore over the principal database, however you can still restore backups of the principal under a different DB name.

If you're talking about this (i.e. you want to restore the principal database), then you need to reinitialize the mirror session after you restore. You could also take alternative approaches that don't involve rebuilding the principal database alltoghether (for example, just updating all the data in the database via an ETL job for example).

If I still missed what you're trying to do, could you provide some additional information on what you're trying to achieve?

|||

Also worth noting...

If the file setup of your principal and mirror databases aren't identical (ex. principal on the h:\ drive and mirror on i:\ drive) you may encounter problems even though the command is syntactically accepted. A primary example would be altering your primary database onto another file, perhaps an .ndf on the h:\ drive. That command will be sent across to the mirror and will fail due to the differences in drive letters. It's obviously ideal to have identical setups to avoid these types of things.

command line and insert

I'm trying to insert into a table 2 values one of which is an

exec master..xp_cmdshell @.command where I have assigned @.command with a value

this statement gives me the result into a 1 col. table fine:

insert into mytable99(col1) exec master..xp_cmdshell @.command

now what I want to do is put col2 in there as well!!

ie. insert into mytable99(col1,col2) values (exec master..xp_cmdshell @.command, '123')

I get a sytax error ... on the exec ??

Could anyone help re the proper way of doing this ... thanks in advancedid'nt know U could do something like this - sounds possibly clever

Syntax wise this command expects the optional parameter no_output after the comma

xp_cmdshell {'command_string'} [, no_output]

Your probably better off with with assigning each value to a local variable then inserting the contents of the variables into the table - something like

SET @.CmdShellOutput = exec master..xp_cmdshell @.command
SET @.StringNumber = '123'

insert into mytable99(col1,col2)
values (@.CmdShellOutput , @.StringNumber)

Sorry not had time to test this syntax but hopefully U get the picture.

GW

Comma delimited file into SQL

Hello,
I have a comma delimited file where data for each field is in " ".
I use Bulk Insert to import the file into SQL table.
How do I get rid of " " ?
thank you.UPDATE MyTable SET MyCol = REPLACE(MyCol, '"', '')
I usually like to get rid of double-quotes or other legacy problems before a
BULK INSERT with a text editor or a script... Generally more efficient that
way.
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:57F89BBC-0268-439D-B55F-350617771BC5@.microsoft.com...
> Hello,
> I have a comma delimited file where data for each field is in " ".
> I use Bulk Insert to import the file into SQL table.
> How do I get rid of " " ?
> thank you.|||You can make a nice little format file to handle the quoted text identifier.
I found a great little thread for you here:
http://groups.google.com/groups?sel...2%40tkmsftngp07
Christopher Winn
Business Intelligence Engineer
Edugration Corp.
Books
"Lena" <anonymous@.discussions.microsoft.com> wrote in message
news:57F89BBC-0268-439D-B55F-350617771BC5@.microsoft.com...
> Hello,
> I have a comma delimited file where data for each field is in " ".
> I use Bulk Insert to import the file into SQL table.
> How do I get rid of " " ?
> thank you.