INSERT INTO SELECT With INDEX

Jan 16, 2007

for MS SQL 2000

the following will work if I want to have UNIQUE Users.Name >>
INSERT INTO [Users] (Name)
SELECT Names FROM OtherUsers
where OtherUsers.Names not in (select Name from Users)

but if I have an UNIQUE INDEX on Users
CREATE UNIQUE INDEX [IX_Users] ON [Users] ([Name],[Category]) ON [PRIMARY]
how can I do it ?

INSERT INTO [Users] (Name, Category)
SELECT Names,Categories FROM OtherUsers
where OtherUsers.Names + OtherUsers.Categories not in (select Name, Category from Users) ?

how can I insert it wih an index on 2 or 3 columns ?

thank you for helping

View 1 Replies


ADVERTISEMENT

SQL Server 2014 :: Error On Unique Index - Duplicate Key When Insert / Select

Aug 4, 2014

I have an issue where I am getting an error on an unique index.

I know why I am getting the error but not sure how to get around it.

The query does a check on whether a unique value exists in the Insert/Select. If I run it one record at a time (SELECT TOP 1...) it works fine and just won't update it if the record exists.

But if I do it in a batch, I get the error. I assume this is because it does the checking on the file before records are written out and then writes out the records one at a time from a temporary table.

It thinks all the records are unique because it compares the records one at a time to the original table (where there would be no duplicates). But it doesn't check the records against each other. Then when it actually writes out the record, the duplicate is there.

How do I do a batch where the Insert/Select would write out the records without the duplicates as it does when I do it one record at a time.

CREATE TABLE #TestTable
(
Name varchar(50),
Email varchar (40)
)
Insert #TestTable (Name,Email) Values('Tom', 'tom@aol.com')

[Code] .....

View 1 Replies View Related

Index Table1 And Select For 647.600 Records.. It Is So Slow.. But I Have No Index :)???

Jun 20, 2008

hello friends
i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :)
i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :)
cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :)
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1")
End If
If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2")
End If
If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3")
End If
End If
Dim searchword As String
If Request("TextBox3") = "" And Request("TextBox2") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'"
End If
If Request("TextBox3") = "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'"
End If
If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then
searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'"
End If
SqlDataSource1.SelectCommand = searchword
End Sub

View 11 Replies View Related

Index/performance Index For SELECT.... IN Statement

Sep 10, 2007



Hi All,

I 'm working to improve some sql performance.


One of the major syntax inside the SELECT statment is ..

WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND
WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND
WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND
WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND
WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND
WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')

(It's to compare the field content with some user input parameter inside a parameter table... )

I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.

Do any one know if still any chance to improvide the performance like this?

Much Thanks,

Andy

View 14 Replies View Related

Index Then Insert Or Insert Then Index

Mar 3, 2001

I have to create a table with a clustered PK on col1, col2, col3 and insert 5.6 million rows. There are only 2 other columns in the table. Does it matter if I create table, insert, create PK or create table with PK then insert?

View 3 Replies View Related

Index And Insert Statement

Nov 30, 2001

Clearly, a non clustered index can improve the performance of a SELECT statement. Is the same true with an INSERT statement? My contention is that the use of a non clustered index will hurt the performace of an insert statement.

Ideas??

View 2 Replies View Related

Insert A Column At Particular Index

Nov 20, 2013

I am using SQL Server2012. Is it possible to insert a new_column to table at 10th of it's column_index ? Ofcourse I can select, but I wish to insert at particular index...Is it possible? Alter table Table1 add New_Column varchar(20) at 10 ??????

View 4 Replies View Related

Clustered Index Insert

Jan 22, 2008

Hi, I am working on performance issues. We have a database defined as the following. And I would like to know why I am getting the clustered index insert in showplan in profiler for a query?

I have a table with field a,b,c,d,e,f,g,h,,etc. The primary key consists
of a combination of field a and field b, which is a clustered index and
unique.

There is a computed column that is defined as field k001 that
consists of field a and field b combined. This computed
column is not persisted. It is not defined as unique even though
it is unique.

There is another computed column that is defined as field k005 it
consists of field d,e,a,b combined in that order. It is not persisted.
It is also not defined as unique, even though it is unique.

There is a non-clustered index on field k001 called k001_c.
There is a non-clustered index on field k005 called k005_C.

The following cursor is declared.
EXEC SQL
DECLARE C_RANGE CURSOR FOR
SELECT "A"
,"B"
,"C"
,"D"
,"E"
,"F"
,"G"
,"H"
,"I"
,"J"
FROM "TABLE_TEST"
WITH (INDEX(K005_C))
WHERE "K005" >= :K1--AREA
AND "K005" <= :K2--AREA
ORDER BY "K005" ASC
END-EXEC

My question is this. This statement when looked at in profiler does a
clustered index insert when each row is fetched. It shows in the
showplan.xml that this has 60% of the cost involved.

I thought maybe this was because the columns were not persisted.
So I persisted these two columns, (by the way just for other information
there are other computed columns in this table that are not persisted,
and use some of the same original fields). I then defined a temp table
using the same table defination for the original table. I persisted the
two columns, then did a select into the temp table( with identity insert off).
I then deleted the original table, and renamed the temp table to the original table.

I seemed to still get the clustered index insert. I do not remember if
the cost showed up the same.
My question is this. Why am I getting the clustered index insert on
a query?

Thanks, Kathy

View 1 Replies View Related

Unique Index Conflict On Insert Into

Jun 18, 2008

I get this message when doing an insert into :

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.

Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?

If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?

Thanks a lot for any suggestion !

Regards,
Fabianus

my favorit hoster is ASPnix : www.aspnix.com !

View 3 Replies View Related

How To Get The Index Of Each Row From A SELECT Query In SQL ?

Aug 11, 2005

Hi,I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.ie, I want to get a dataset like :"0", "dataCol1", "dataCol2" for the first line"1", "dataCol1", "dataCol2" for the second line"2", "dataCol1", "dataCol2" for the third lineetc.Anyone knows if there is a sql statement that does it ?ThanksJohann

View 2 Replies View Related

Should The INDEX Name Be Used In SELECT Statement

Apr 19, 2007

I am new to SQL, especially use SQL in VC++ 6.0 framework.

I am told that creating INDEX on field(s) could speed up a query.

if I create a INDEX like the following
<code>
CREATE nonclustered INDEX IX_XYZ on TableA.field1
</code>
Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.

<code>
SELECT * FROM TableA WHERE field1 = xxx
</code>

View 4 Replies View Related

Add Index To Select Result

Mar 20, 2007

Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?

thank you
eliko"

View 1 Replies View Related

INSERT-SELECT Depending On The Select:ed Order

Aug 15, 2006

I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.

However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?

Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).

View 3 Replies View Related

SQLDataSource - Using The Latest Index From An Insert Elsewhere On A Page

May 22, 2006

I have a page that has a drop down control that is used to control the items in a FormView.  The drop down is bound to one SQLDatasource while the FormView is bound to another SQLDataSource.  I've got everything working great.  Selecting an item in the drop down reveals the details in the form view.  the Form can be opened for editing and deleting.  I even have the Insert working.  But there lies my problem.  Once the Insert is executed I'd like to take the Index value of the recently submitted item and set it to the SelectedIndex of my dropdown.  Here is the code for each of the SQL events  Sub FormViewUpdated(ByVal sender As Object, ByVal e As FormViewUpdatedEventArgs) Handles FormView1.ItemUpdated
confirmation.Visible = True
confirmation.Text = "<img src=""../img/smiley36.gif"">&nbsp;Your record has been updated."

Dim index As Integer = Me.DropDownList1.SelectedIndex
DropDownList1.DataBind()
Me.DropDownList1.SelectedIndex = index
End Sub


Sub FormViewDeleted(ByVal sender As Object, ByVal e As FormViewDeletedEventArgs) Handles FormView1.ItemDeleted
confirmation.Visible = True
confirmation.Text = "<img src=""../img/smiley36.gif"">&nbsp;Your record has been deleted."

DropDownList1.DataBind()
FormView1.ChangeMode(FormViewMode.ReadOnly)
End Sub

Sub FormViewInserted(ByVal sender As Object, ByVal e As FormViewInsertedEventArgs) Handles FormView1.ItemInserted
confirmation.Visible = True
confirmation.Text = "<img src=""../img/smiley36.gif"">&nbsp;Your record has been inserted."
DropDownList1.DataBind()
End Sub
 I would like to do something similar to the Updated event in the Inserted sub.  Notice I have a Me.DropDownList.SelectedIndex bit...I'd like to be able to set a variable to the new ID and then set the Me.DropDownList.SelectedIndex to that variable.  My stored proc for the Insert reads like
  ALTER PROCEDURE dbo.usp_INSERTHelp_Faq
(
@FAQQuestion varchar(500),
@FAQText ntext,
@FAQIndex varchar(500),
@ModifiedBy int,
@Help_FAQID int OUTPUT
)
AS
INSERT INTO Help_FAQ (FAQQuestion, FAQText, FAQIndex,ModifiedBy, Views)
VALUES (@FAQQuestion,@FAQText,@FAQIndex,@ModifiedBy,0)

SELECT @Help_FaqID = @@Identity
RETURN
 So, the Stored Proc is set up to return the latest ID.  Also, my SQLDataSource for the FormView has an InsertParameter:  <asp:Parameter Direction="Output" Name="Help_FAQID" Type="Int32" />
 
Any thoughts on how to close the loop here?  How do I get my hands on the new Help_FAQID?

View 3 Replies View Related

How To Return Primary Unique Index Key On Insert

May 27, 2002

Hi,

I am making a program in Visual Basic .NET with SQL Server 2000.

I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...

I must do one of them, but don't know how either of them:

-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.

or

-get the command in Visual Basic that will send the Insert command with a return field ("Id")

Thanks in advance,
Sebastien Anselmo

View 6 Replies View Related

SQL 2012 :: Cost Of Clustered Index Insert Is 100%?

Sep 7, 2014

how to reduce the cost of clustered index insert ?

View 8 Replies View Related

SQL Server 2012 :: 42% Of 100% Taken In Clustered Index Insert

Dec 1, 2014

I have a query which is primarily a victim of blocking and a blocker itself for other queries. I studied the plan for this and it shows a 42% cost on CI insert operation. The insert is happening on a table (Table A) that has a PK. This PK is not a running number. It is also a business key (primary key) in another master table (Table B).

My understanding is that the cost is heavy because -

1, this PK is not an incremental number. It could be any number not in a sequence.
2. while inserting into CI, there must be a scan happening to find out the location where the index will be inserted.

How can I reduce the cost?

1. Should I go for partitioning of this table Table A? I am trying to do this but I am not able to find any suitable partition key looking at the JOINS and filter clauses where this table is being used in the applicaiton.
2. Should I introduce a surrogate key (running number) as a primary key so that CI is faster ?

View 9 Replies View Related

HELP!, How I Can Describe The Name Of Index Which I Like To Use In SELECT Statement

Sep 19, 2000

HELP
Dmitri

View 5 Replies View Related

Select A Value From Column Which Is Named Index

Dec 17, 2011

I want to Select a value from a Column which is named "Index" - and I don't want to change the name naturally. Is there a way to get a value without mentioning the Column name itself, rather the column number? "Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax."

Occurs when I try to simply..
-> SELECT Index FROM Table WHERE Name like 'xyz' <-

View 2 Replies View Related

SELECT Result Without Index Sorting

Mar 16, 2004

I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

View 14 Replies View Related

BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?

Jun 22, 2007

All,Just want to make sure that I understand what's going on here.I have a table with IGNORE_DUP_KEY set on a unique, multi-columnindex.What I'm seeing is this:1) When performing a BULK INSERT, the UNIQUE index is not beingrespected and rows which violate the unique index are inserted.2) When performing a regular INSERT, the UNIQUE index is beingrespected and rows which violate the unique index ARE NOT inserted.Is this expected behavior.Also, I have some questions, given the index described.Q1) Will a regular INSERT that attempts to insert duplicate data getan error back or just a warning?Q2) How can I set things up so that a BULK INSERT would NOT allowduplicates to be entered into the table?Thanks,Wes Gamble

View 2 Replies View Related

SELECT Columns By Column-index NOT By Columnname!

Nov 20, 2005

Hello all,

how can I select one or more columns from a table by column-index and NOT by columnname?

e.g.:

SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1

and NOT like this:

SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1

Is that possible in MS-SQL 2000?

Thanks a lot in advance

kind regards

Otto

View 11 Replies View Related

Select Col42 From Tbl With (NOT INDEX (myidx)) - Hint Saying Do Not Use Possible?

Nov 28, 2007



Hello!
I would like when I compare query plans to be able to compare
2 queries where the 1. is the "normal" version and the 2. the version where I forced compiler
not to use a specific index (i don't want to force at table scan, so hint index(0) can't be used).

The only way I see how I could achieve something similar is to to drop the index and compare response time
before and after. But building index could be time consuming...

Is it possible?

select col42 from tbl;
select col42 from tbl with...

pressing CTRL+L

Greetings
Bjørn

View 1 Replies View Related

Difference Between Index Seek &&amp; Index Scan &&amp; Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'

Feb 28, 2007

I have 1 client who keeps running into the following error on the subscriber and merge agents >

€œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?

Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.

Thank you in advanced,
Pauly C

View 6 Replies View Related

Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.

Aug 22, 2007

Hi.

I have been recently redesigning my tables - creating FK
relationships from child tables to the PK userid in the Users table.
The specifics of what I did and why can be seen here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1

But, now I am getting the below error:

Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'.
The statement has been terminated.

Or, for that matter, SavedSearches or any other table where I need to
insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and
a few of my other tables, the same user may account for multiple rows.

Any suggestions as to where I messed up and how to deal with this?

Thanks.


DBO.USERS






Code Snippet

CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [varchar](10) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [varchar](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/
SET ANSI_NULLS ON


DBO.SAVEDSEARCHES


CREATE TABLE [dbo].[savedsearches](
[savedsearchesid] [int] IDENTITY(1,1) NOT NULL,
[searchname] [varchar](50) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NULL,
[isdefault] [bit] NULL,
[gender] [char](10) NULL,
[startyear] [varchar](50) NULL,
[endyear] [varchar](50) NULL,
[country] [varchar](50) NULL,
[miles] [int] NULL,
[pictures] [varchar](50) NULL,
[postal] [int] NULL,
[sort] [tinyint] NULL,
[photostring] [varchar](50) NULL,
[orderby] [tinyint] NULL,
CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED
(
[userid] ASC,
[searchname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users]
GO
SET QUOTED_IDENTIFIER ON
GO




The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.

INSERT INTO lastlogin
VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109))

DBO.LASTLOGIN





Code Snippet


CREATE TABLE [dbo].[lastlogin](
[lastloginid] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)),
[activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()),
[online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end),
CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]

View 1 Replies View Related

INSERT INTO... SELECT... Cannot Insert Duplicate Key...

Jul 20, 2005

I want to add the content of a table into anotherI tried to copy all fields, except the primary key:INSERT INTO table2(field2, field3, field4, ...)SELECT field2, field3, field4, ...FROM anotherDB.dbo.table1gives the following error:Violation of UNIQUE KEY constraint...Cannot insert duplicate key...Why?I didn't ask him to copy the key column; Isn't the SQL Server supposedto know how to increment the key ?

View 2 Replies View Related

SQL 2012 :: Minimal Logging Insert Statement On Non Clustered Index Table

Jul 9, 2014

I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...

*not replicated

*tablock is used

*table is empty

The following test seems to contradict this

In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.

The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?

CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT

[Code] ....

View 2 Replies View Related

Cannot Insert Duplicate Key Row In Object 'dbo.blabla..' With Unique Index 'Idx_blablabl'. The Statement Has Been Terminated. At

Jan 16, 2007

We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.

I have the following problem

I have applied Nonclustered index over a column on a table.

Designed a SP for insertion which caters for updation incase the criteria based on the input is met.

The logic goes like this

Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.



However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.

Can any body tell me if there is any problem with the SQL Server 2005







Thanks in advance

R Suresh, SQLDBA







View 8 Replies View Related

SQL 2012 :: Include Columns In Index That Are In Where Clause / Select List And Join

Jun 2, 2014

Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.

View 4 Replies View Related

Insert Should Aquire Only Exclusive Rowlock. Why Does Insert Block Select With ( Updlock, Rowlock )?

Mar 12, 2007

Here is the situation i am stuck with, see the example first and below explained the problem:

-- 'SESSION A

create table foo (

id integer,

pid integer,

data varchar(10)

);

begin transaction

insert into foo values ( 1, 1, 'foo' )

insert into foo values ( 2, 1, 'bar' )

insert into foo values ( 3, 1, 'bozo' )

insert into foo values ( 4, 2, 'snafu' )

insert into foo values ( 5, 2, 'rimrom' )

insert into foo values ( 6, 2, 'blark' )

insert into foo values ( 7, 3, 'smeg' )

commit transaction

create index foo_id_idx on foo ( id )

create index foo_pid_idx on foo ( pid )

begin transaction

insert into foo values ( 9, 3, 'blamo' )

-- 'SESSION B

begin transaction

select id, data from foo with ( updlock, rowlock ) where id = 5;



-- Problem:

-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.

-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?



Appreciate your help,

Rajesh.

View 5 Replies View Related

INSERT INTO SELECT FROM + Add 1 Row On INSERT

Mar 15, 2008

hi need help how to do this
INSERT INTO SELECT FROM table + add one row for evry employee (on insert)

this is table tb_test1
empid fname unit fld1 fld2 fld3 fld4 fld5
----------------------------------------------------------------------------------------
111 aaa 1 a b c d d
222 bbb 3 a c e g g
333 cccc 5 s h t u j





Code Snippet

INSERT INTO [nili].[dbo].[tb_test2]

([empid]

,[fname]

,[unit]

,[fld1]

,[fld2]

,[fld4]

,[fld5])

SELECT [empid]

,[fname]

,[unit]

,[fld1]

,[fld2]

,[fld4]

,[fld5]

FROM [nili].[dbo].[tb_test1]


i need to insert into tb_test2
and ADD one row for evry employee
val_orginal=1 (the orginal ROW)
val_orginal=2 (the extra ROW)


this is table tb_test2
after the insert
HOW TO THIS


empid fname unit fld1 fld2 fld3 fld4 fld5 val_orginal
-------------------------------------------------------------------------------------------------
111 aaa 1 a b c d d 1
111 aaa 1 - - - - - 2
222 bbb 3 a c e g g 1
222 bbb 3 - - - - - 2
333 cccc 5 s h t u j 1
333 cccc 5 - - - - - 2



and tnx for the help

View 12 Replies View Related

INSERT SELECT

Jul 7, 2006

Hallo, I am trying to insert into a table HFacility a FacilityID, HotelID FROM Facility. Using Select.Both HFacility and Facility have columns FacilityID, HotelID with same DataType.Insert INTO HFacility
Select FacilityID, HotelID
FROM FacilityI am getting this error:Server: Msg 213, Level 16, State 4, Line 1Insert Error: Column name or number of supplied values does not match table definition.Thank you for your help

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved