How can I prevent from inserting duplicate data?

I have a table storing only 2 FKs, let's say PID, MID

Is there any way that I can check distinct data before row is added to this table?

For example, current data is

PID MID
----
100 2001
100 2005
101 3002
102 1009
102 7523
102 2449

If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue?

Thanks.

sql server allows you to add an index containing those two columns and set that index as a unique constraint.

this will prevent duplicates.

|||

I would rather do it in a stored proc:

IF NOT EXISTS( SELECT * FROM yourTable WHERE PID = @.PID AND MID = @.MID)

BEGIN

--do the insert

END

How can I get modified data using timestamp columns

I am putting together an SQL script that is pulling recently modified data from 3 tables and INSERTing that data into another table.

All 3 of my input tables have a timestamp column and I have the previous values for these 3 timestamp columns at the time my SQL script was run previously. So, using the timestamp column values that I had from the previous run of my SQL script and the current timestamp columns that exist in my 3 tables, I am able to derive any recently modified rows.

So, here are my 3 input tables:

Items (has a timestamp column) and has several million rows.

Attributes1 (has a timestamp column) and has a million rows.

Attributes2 (has a timnestamp column) and has a million rows.

The Attributes1 and Attributes2 tables have attributes that describe the items in the Items table. I want to INSERT the Items rows with all of their attributes into a fourth table (that doesn't need a timestamp column).

The kicker is if any attribute changes in the Attributes1 and/or Attributes2 tables, I want to completely resummarize the entire item in the fourth table.

So, I have 3 INSERT/SELECTs in my SQL Script so that I can pickup any combination of modified data in my 3 input tables.

INSERT INTO Table4

.......

SELECT

.....

FROM Items

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Items.TimestampColumn BETWEEN a AND b

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes1.TimestampColumn BETWEEN c AND d

AND (the Items row is not already in Table4)

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes2

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE Attributes2.TimestampColumn BETWEEN e AND f

AND (the Items row is not already in Table4)

This SQL takes a whole long time to run (more than an hour).

I would like to consense my SQL into a single INSERT/SELECT.

Does anybody know of an SQL technique that I haven't thought of...

TIA

Will this work:

INSERT INTO Table4

.......

SELECT

.....

FROM Items, Attributes1

LEFT OUTER JOIN Attributes1 ...

LEFT OUTER JOIN Attributes2 ...

WHERE (Attributes1.TimestampColumn BETWEEN a AND b

OR Attributes1.TimestampColumn BETWEEN c AND d

OR Attributes1.TimestampColumn BETWEEN e AND f)

AND (the Items row is not already in Table4)

Alternatively, you could try doing a UNION on the select statements to get them into one derived table (if the items table is empty before the first query is run, you can drop the NOT EXISTS as the UNION will remove duplicate rows), and then insert into the table in one go from the derived table (UNION statement). The latter may be quicker if the items table is empty to begin with.

How Can I get Identitiy field from database while inserting new row in sql server 2005 compact e

How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.

Ex:

I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....

Any solution for it.

In the standard sql server edition you would use scope_identity() but for compact edition I only find @.@.identity

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

Directly after a successful insert select @.@.identity and read its value to get the ID of the inserted row.

|||

this is ok when we have to select the identity filed individually.... my question is

if i fire a sql query [insert into tb1(...) values(...)] through executenonquery this will insert a record in table but i would like to take a value of identity field just inserted with this query. As this value will be inserted in child table..

I hope u r getting me..

Thankx

|||As Andreas said, just do a "SELECT @.@.IDENTITY" immediately after the insert, and you will get the value inserted.

|||

You can prefetch that information using the INFORMATION_SCHEMA:

QA: How do I get IDENTITY information on a SQL Mobile database?

The technique is simple: you query the INFORMATION_SCHEMA and calculate the next IDENTITY based on the values you retrieved.

You can also use a different technique through SqlCeResultSet:

How to retrieve the last inserted IDENTITY in .NET CF

|||One alternate way, instead of using an autoincrementing integer as your identity field, use a GUID. Set the data type to uniqueidentifier, set it as the identity. Then use System.Guid.NewGuid() to create the primary key prior to inserting it into the database. That way you know the primary key before it's ever written out, and saves you from having to dig around the database to determine it.

Arcane
|||

Dear ArcaneCode,

Thankyou for your kind suggestion. I think this is more better then using identity field

Once Again Thanx

|||Using System.Guid.NewGuid() on mobile devices is slow. Please read Peter Foot's comments on this here.

How Can I get Identitiy field from database while inserting new row in sql server 2005 compact e

How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.

Ex:

I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....

Any solution for it.

In the standard sql server edition you would use scope_identity() but for compact edition I only find @.@.identity

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

Directly after a successful insert select @.@.identity and read its value to get the ID of the inserted row.

|||

this is ok when we have to select the identity filed individually.... my question is

if i fire a sql query [insert into tb1(...) values(...)] through executenonquery this will insert a record in table but i would like to take a value of identity field just inserted with this query. As this value will be inserted in child table..

I hope u r getting me..

Thankx

|||As Andreas said, just do a "SELECT @.@.IDENTITY" immediately after the insert, and you will get the value inserted.

|||

You can prefetch that information using the INFORMATION_SCHEMA:

QA: How do I get IDENTITY information on a SQL Mobile database?

The technique is simple: you query the INFORMATION_SCHEMA and calculate the next IDENTITY based on the values you retrieved.

You can also use a different technique through SqlCeResultSet:

How to retrieve the last inserted IDENTITY in .NET CF

|||One alternate way, instead of using an autoincrementing integer as your identity field, use a GUID. Set the data type to uniqueidentifier, set it as the identity. Then use System.Guid.NewGuid() to create the primary key prior to inserting it into the database. That way you know the primary key before it's ever written out, and saves you from having to dig around the database to determine it.

Arcane
|||

Dear ArcaneCode,

Thankyou for your kind suggestion. I think this is more better then using identity field

Once Again Thanx

|||Using System.Guid.NewGuid() on mobile devices is slow. Please read Peter Foot's comments on this here.