Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Inserts into table that has a Primary key/Unique constraint

We do a lot of inserts into a table that has a primary key on an identity
column. Also the key is defined as a unique constraint.
Theory would call for some additional latency as it has to check for
uniqueness, but since its an identity column, can we safely remove that
unique constraint and that way, we can speed up the inserts ?
You may ignore this thread. I guess the primary key has a unique constraint
to it
"Hassan" <hassan@.test.com> wrote in message
news:ur1FBJpQIHA.2208@.TK2MSFTNGP06.phx.gbl...
> We do a lot of inserts into a table that has a primary key on an identity
> column. Also the key is defined as a unique constraint.
> Theory would call for some additional latency as it has to check for
> uniqueness, but since its an identity column, can we safely remove that
> unique constraint and that way, we can speed up the inserts ?
>

Inserting unique values into a different tables if they don''t exists already.

Hi

I am trying to insert values into a table that doesn't exist there yet from another table, my problem is that because it is joined to the other table it keeps on selecting more values that i don't want.

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT M.MemberID, '6', CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM Members M

INNER JOIN _MemberProfileLookupValues ML

ON M.MemberID = ML.MemberID

WHERE M.Active = 1

AND OptionID <> 6

When i execute that code it returns all the values, let say OptionID = 3 is smoking already exists in the MemberProfileLookupValues table then it is going to select that persons memberID

I want to insert only members values that aren't already in the _MemberProfileLookupValues from the Members table (I think that it is because of the join statement that is in my code, but i don't know how i am going to select members that aren't in the table, because i have a few other queries that are very similar that are inserting different values, so ultimately

ONLY INSERT THE MemberID the values 6 and the statusID of X if it is not in the table already.

Any ideas / help will be greatly appreciated. Please help.

Kind Regards

Carel Greaves

The following query insert the new members who option id 6 is not exist in the MemberProfileLookupValues table,

Code Snippet

SET NOCOUNT ON

INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)

SELECT

M.MemberID

, '6'

, CASE M.MaritalStatusID WHEN 1 THEN '7'

WHEN 2 THEN '8'

WHEN 3 THEN '9'

WHEN 4 THEN '10'

END

FROM

Members M

Where

NOT EXISTS

(

Select 1 From _MemberProfileLookupValues ML

Where M.MemberID = ML.MemberID And OptionID = 6

)

And M.Active = 1

|||

Thanks a lot.

That is exactly what i was looking for.

Kind Regards

Carel Greaves

Inserting Unique Records

Hello,

I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.

How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?

Does my request make sense? Please let me know if you have questions.

Thank you for your help!

CSDunnThe one way that I know to do this would be to use MAX on all but the key fields in the Select statement of the source table, and group by the key fields.

Is there another way?|||Are you simply trying to ID (in order to eliminate) the one record that is a duplicate?

You might try:

SELECT
col1
, col2
, col3
, col4
, col5
FROM
dbo.MyTable
GROUP BY
col1
, col2
, col3
, col4
, col5
HAVING COUNT(*) > 1

Then copy 1 row with the duplicate data to another table (identically defined with no primary key). Delete the duplicate records from the source table and then re-import the one record from the export table.

Otherwise, yes, you could use MAX for all but the five primary key columns to insert into your other table. Just be sure that it's MAX that you want and not MIN (or some other function).

Regards,

hmscott|||Thanks for your help!

cdun2

Inserting Specific Value in Query Result

I have a table where I need to insert a unique USERID based on the
USERNAME. Seems pretty simple to me, however I am a novice writing SQL
statements.
My knowledge is very limited, so when responding please do not assume I
know basic code.
I have taken on this task in an emergency situation.
Thanks in advance for your help and please let me know if further
information is needed.DK13 (DericK@.sklarcorp.com) writes:
> I have a table where I need to insert a unique USERID based on the
> USERNAME. Seems pretty simple to me, however I am a novice writing SQL
> statements.
> My knowledge is very limited, so when responding please do not assume I
> know basic code.
> I have taken on this task in an emergency situation.
If it's an emergencty I am puzzled by the fact that you did not provide
more information. The standard recommendation for assistance is that
you post:
o CREATE TABLE statement(s) for the tables involved.
o INSERT statements with sample data.
o The desired result given the sample.
This permits anyone who wants to answer your question to easily
copy-and-paste into a query and developer a tested solution.
I realise that you if don't know basic code, this may go over your
head. But you should at least be able to produce some sample input,
and the output from it. "A unique USERID based on the USERNAME" is
quite ambiguous. And supposedly there is a requirement that these
user id follows some pattern. It would also be interesting to know
how these usernames look like. Furthermore, it is also unclear whether
you want the userid added to the query result, or a column added
to the table. (Your subject lines says the former, but the latter
makes more sense.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||OK, let's see if this helps.
The query I ran:
select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
The result set looks like this(I've left out several columns):
USERID USERNAME ORIGINALDATE
<NULL> Cruz, Jane 2/6/2006 12:00:05 AM
<NULL> Cruz, Jane 2/6/2006 11:25:00 PM
Now from this result I would like to Insert a userid, such as
'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
that I need to do this for.
Let me know if this is more useful info|||DK13 (DericK@.sklarcorp.com) writes:
> OK, let's see if this helps.
> The query I ran:
> select* from sysdba.HISTORY where USERNAME = ('Cruz, Jane')
> The result set looks like this(I've left out several columns):
> USERID USERNAME ORIGINALDATE
><NULL> Cruz, Jane 2/6/2006 12:00:05 AM
><NULL> Cruz, Jane 2/6/2006 11:25:00 PM
> Now from this result I would like to Insert a userid, such as
> 'U6UJ9A00003J' where the USERID above = "<NULL>". I have several users
> that I need to do this for.
> Let me know if this is more useful info
You could do:
UPDATE tbl
SET USERID = dbo.createuseridfromname(USERNAME)
WHERE USERID IS NULL
All that remains is to write the user-defined function. Unfortunately,
I cannot do that, because I don't know the rules.
Or are the user ids in fact already defined in a table somewhere?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you, I did some other research and found the same function to be
helpful, much appreciated!!