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 ?
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.
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
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?
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?
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.
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 ??????
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?
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?
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
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>
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?
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).
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""> 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""> 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""> 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?
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")
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 ?
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' <-
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
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...
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
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.
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:
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
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.
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]
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 ?
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
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
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.
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 )?
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
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
