Insert without duplicates

i have a table that contains a list of musical artist and an index. I want to insert artists into that table however, i dont want to insert any name that already exists. How do i do that?

Here is my statement:
INSERT INTO [Artists Table] (ArtistName) VALUES (@.ArtistName)

Create EITHER a Primary Key or a Unique Index on ArtistName

|||

In addition to the primary key or unique constraint if you alter your insert to the following, you will avoid getting PRIMARY KEY VIOLATION errors:

if not exists
( select 0 from [Artists Table] where artistName = @.artistName )
INSERT INTO [Artists Table] (ArtistName) VALUES (@.ArtistName)

Dave

|||

Hi,

or if you want to do this within one step rather than a batch

INSERT INTO [Artists Table] (ArtistName)
SELECT @.ArtistName
WHERE NOT EXISTS
(
select * from [Artists Table] where artistName = @.artistName
)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!. Thats works for 1 variable, however how do i do it for multiple.... For example:

INSERT INTO [Recordings] (RecordingTitle, ArtistName = @.ArtistName)
SELECT @.RecordingTitle
WHERE NOT EXISTS
(
select * from [Recordings] where RecordingTitle = @.RecordingTitle
)

I want to pass in the ArtistName, but not have it verify that because it is a foreign key.
|||I think i figured it out:

INSERT INTO Recordings
(RecordingTitle, ArtistName)
SELECT @.RecordingTitle AS Expr1, @.ArtistName AS Expr2
WHERE (NOT EXISTS
(SELECT RecordingID, RecordingTitle, ArtistName
FROM Recordings AS Recordings_1
WHERE (RecordingTitle = @.RecordingTitle))) AND (@.ArtistName = @.ArtistName)|||Good, I have same question, actually Jens K. Suessmeyer help me figure this out yesterday. Thanks Jens.

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP Job
This sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>sql

Insert with Index VS Insert without Index

Hi all,
Environment: MSSQL 2000 SP3 on Windows 2000.
In our application, there is a table with 600+ columns and
50+ indexes. As a process we truncate the table and insert
records using INSERT into .. SELECT. While we do a huge
insert(1Million records), we tried two approaches.
A.)Creating indexes on this empty table and then inserted
1M records.
B.)Also we tried inserting 1M records without indexes and
created indexes later.
Approach A took less time than Approach B. The time
difference is more than one hour.
While all RDBMS suggests to drop indexes before doing a
big insert, I would like to know how MSSQL 2000 manages to
do the inserts with indexes with in a resonable time.
Regards,
JP JobThis sounds abnormal. Usually SQL Server recommends dropping index then
recreate, too. I assume there was no concurrent activity in the
server/machine during the runs. I need to get more information in order to
diagnose this further. Can you provide the following information:
1. The plan used in the insert with index case
2. Machine info including # CPU, CPU speed, CPU usage during the two
approaches, physical memory size
3. Is there any ordering of the data inserted? Since the data was selected
from anothet table, did it happen to be sorted on some column? If so, did
that sort order match any index key order?
Thanks.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:11b2201c4420b$a6d6b960$a001280a@.phx
.gbl...
> Hi all,
> Environment: MSSQL 2000 SP3 on Windows 2000.
> In our application, there is a table with 600+ columns and
> 50+ indexes. As a process we truncate the table and insert
> records using INSERT into .. SELECT. While we do a huge
> insert(1Million records), we tried two approaches.
> A.)Creating indexes on this empty table and then inserted
> 1M records.
> B.)Also we tried inserting 1M records without indexes and
> created indexes later.
> Approach A took less time than Approach B. The time
> difference is more than one hour.
> While all RDBMS suggests to drop indexes before doing a
> big insert, I would like to know how MSSQL 2000 manages to
> do the inserts with indexes with in a resonable time.
>
> Regards,
> JP Job
>

Insert statement

Hi
When I try to insert a row in table(without any index) why does it display
READS count as 1 in profiler trace for every insert statement executed
against tht table?
It also dispalys WRITE count but that is expected but why READ?
Table structure is normal only two int and char columns without any default
or identity defined.
Thanks in advance.
Manu
Profiler includes some reading meta-data like verifying permissions and translating object name to
object id. My guess is that this is what you see...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any default
> or identity defined.
> Thanks in advance.
> Manu
>
>
|||Hmmm. Perhaps it has to READ the page so that it can put the data in it
before the page WRITEs back.
The reads and writes are, by the way logical reads and writes because the
actual physical reads and writes are managed by caching and buffers.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any
> default
> or identity defined.
> Thanks in advance.
> Manu
>
>

Insert statement

Hi
When I try to insert a row in table(without any index) why does it display
READS count as 1 in profiler trace for every insert statement executed
against tht table?
It also dispalys WRITE count but that is expected but why READ?
Table structure is normal only two int and char columns without any default
or identity defined.
Thanks in advance.
ManuProfiler includes some reading meta-data like verifying permissions and tran
slating object name to
object id. My guess is that this is what you see...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any defaul
t
> or identity defined.
> Thanks in advance.
> Manu
>
>|||Hmmm. Perhaps it has to READ the page so that it can put the data in it
before the page WRITEs back.
The reads and writes are, by the way logical reads and writes because the
actual physical reads and writes are managed by caching and buffers.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any
> default
> or identity defined.
> Thanks in advance.
> Manu
>
>

Insert statement

Hi
When I try to insert a row in table(without any index) why does it display
READS count as 1 in profiler trace for every insert statement executed
against tht table?
It also dispalys WRITE count but that is expected but why READ?
Table structure is normal only two int and char columns without any default
or identity defined.
Thanks in advance.
ManuProfiler includes some reading meta-data like verifying permissions and translating object name to
object id. My guess is that this is what you see...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any default
> or identity defined.
> Thanks in advance.
> Manu
>
>|||Hmmm. Perhaps it has to READ the page so that it can put the data in it
before the page WRITEs back.
The reads and writes are, by the way logical reads and writes because the
actual physical reads and writes are managed by caching and buffers.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:1E3FC2FF-ED8B-4430-9508-5D84F9654BC0@.microsoft.com...
> Hi
> When I try to insert a row in table(without any index) why does it display
> READS count as 1 in profiler trace for every insert statement executed
> against tht table?
> It also dispalys WRITE count but that is expected but why READ?
> Table structure is normal only two int and char columns without any
> default
> or identity defined.
> Thanks in advance.
> Manu
>
>