Skip Foreign Key Violations During Insert Operations?
May 12, 2014
I'm trying to optimize a few batch import procedures we use in our processes.
It currently works like this:
1) Cursor loop cycles through all data to be imported from IMPORT table
2) For every record there is an attempted insert to PROD table in a TRY-CATCH check to see whether the record would pass all the primary key and foreign key constrains in PROD table
3) Only those that pass the TRY-CATCH check gets imported into PROD table
4) Every row gets logged into a separate LOG table, either with a comment like "Import OK" or "Error: foreign key violation in field 'my_id'"
The thing is, the procedure runs fine when I'm importing several thousands of records, but when it comes to hundreds of thousands, the speed becomes an issue, as I currently get 20 records per second and slowing...
There is no other code in that procedure, no queries. Just the Cursor cycle and the try-catch check.
PS: I'm using MS SQL Server 2005.
View 4 Replies
ADVERTISEMENT
Aug 25, 2004
Hello!
I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date.
What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?
Any help??
Many thanks!
View 1 Replies
View Related
Jul 20, 2005
I am trying to append records from one table to another in a db running onMSDE, knowing fullwell that some of the data in the source will beduplicates of that in the destination table's pk.What I would like to happen is to have the stored procedure plunk in allrecords that don't violate the constraintand silently let the duplicate info fall by the wayside. The trouble is SQLserver seems to abort the whole procedure ifeven a single record violates the constraint.In a regular Access mdb, an INSERT statement (append query) would do justthat. Of course it warns you of the violation but a DoCmd.SetWarnings FALSEtakes care of that.Any ideas as to what I need to do to achieve that same thing?
View 1 Replies
View Related
Jan 10, 2008
Hi, I have the code below. I need to skip the first row in the datatable as it has the headers. This works now, but my gridview gets the header row inserted as a record.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection) ' old method: Lots of INSERT statements ' first, create the insert command that we will call over and over: destConnection.Open() Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection) ins.CommandType = CommandType.Text ins.Parameters.Add("@contactdate", SqlDbType.Text) ins.Parameters.Add("@dnbnumber", SqlDbType.Text) ins.Parameters.Add("@prospectname", SqlDbType.Text) ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar) ins.Parameters.Add("@phonemeeting", SqlDbType.Text) ins.Parameters.Add("@followupcalldate2", SqlDbType.Text) ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text) ins.Parameters.Add("@followupcalldate3", SqlDbType.Text) ins.Parameters.Add("@appointmentdate", SqlDbType.Text) ins.Parameters.Add("@appointmentlocation", SqlDbType.Text) ins.Parameters.Add("@appointmentkept", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text) ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text) ins.Parameters.Add("@comments", SqlDbType.Text) ins.Parameters.Add("@newaccount", SqlDbType.Text) ins.Parameters.Add("@futureopportunity", SqlDbType.Text) ' and now, do the work: For Each r As DataRow In sourceTable.Rows For i As Integer = 0 To 16 ins.Parameters(i).Value = r(i) Next ins.ExecuteNonQuery() 'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then 'Console.WriteLine("-- copied {0} rows.", rowscopied) 'End If Next End Using destConnection.Close() End Sub
View 3 Replies
View Related
Oct 31, 2014
I used code below to do bulk insert. Since csv file first row is column name. How to skip first row?
BULK
INSERT TEST
FROM 'c: est.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
View 4 Replies
View Related
May 14, 2008
Hi Teachies,
I am using SQL Server Standard Edition with good HardWare configuration.
In one of table i am inserting around 25 millions records and that takes time around more than 3 hrs.
same thing is happening while fetching records from that table.
this database contains only single file group i.e primary
and that table contains .. Clustered as well as non clustered index.
it doesnot have any Triggers.
How do i increase this performance.
Paritioning of table cannot be use in SQL Server Standard Edition.
Or Dropping all non clustered index before insert operation will improve my performance.
Please suggest me.
Thanks
Rajesh Varma
View 17 Replies
View Related
May 21, 2007
Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?
View 2 Replies
View Related
Oct 1, 2007
Hi,
I have a data file and the contents of it are as follows
2 -- This is the header indicating the no of records in my files
1001|s1
1006|s2
The content of format file is as follows. This is to skip first column of the all the rows and get only Subs (i.e s1 and s2 )
9.0
2
1 SQLCHAR 0 100 "|" 0 ID ""
2 SQLCHAR 0 100 "
" 1 Subs ""
Here is my query to get all the Subs from my data file
SELECT * FROM OPENROWSET( BULK 'datafile.txt',
FORMATFILE = 'FormatFile.fmt',
FIRSTROW = 2 ) AS a
But this query retuns only s2 where i was expeting s1 and s2. The reason being is that the firts row i.e header doesn't follow the format
Can any one please let me know how to skip the first line in the data file and get the result as required
~Mohan
View 6 Replies
View Related
Mar 18, 2014
how to create package upsert industry and want to do update and insert operations from database.
View 4 Replies
View Related
May 27, 2007
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
View 1 Replies
View Related
Oct 10, 2007
Hi,
I have a data file which consists of data as below,
4
PPU_FFA7485E0D||
T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH
WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
Can any one help on this.
Thanks,
-Badri
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jul 26, 2007
I have a Merge Replication Problem and I'm not sure how to proceed.
Given the following:
Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue
The replication is configured to resolve to the greater integer value.
Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)
One of the inserts will get to the Central Server first.
The other will form a conflict.
A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.
So now I have two different value fields and a conflict that cannot be resolved.
Is this fixable?
Do I need to make changes at athe Architechture level?
View 2 Replies
View Related
Jul 5, 2001
Hi,
I want to upsize my Access backend to SQL Server, and there is a simple query which works fine in Access but not in SQL Server:
There are two tables: Person and ArtificialPerson (a more specific Person). They are linked through the Person_ID attribute (Primary key in Person and Foreign key in ArtificialPerson).
I have a form in Access frontend having as recordsource a Select statement based on the join between these two tables. (Although this relationship is defined as 1-n in Access, I use it just as 1-1 in my form, so after a adding a new entry in the form I have a new entry in both tables)
The problem is the following:
Using SQL Server as backend, when I try to insert a new entry in the form (or in query used as recordsource for the form) the Person_ID isn't automatically inserted as foreign key.
My question is why using Access tables the Foreign key is automatically inserted and using SQL Server it does not work ?
Thanks in advance
View 2 Replies
View Related
Dec 2, 2006
INSERT INTO X ...... ( A, B, C )INSERT INTO Y ...... ( J, K, L )If Y has a foreign key M which is the primary key D of X,is there an easy and/or efficient way to have SQL Serverassign D, and tell us what it is so we can add M to thelist for Y ?I know I can select D where A, B, C but I wondered aboutother tricks.--Wes GroleauAlive and Wellhttp://freepages.religions.rootsweb.com/~wgroleau/
View 1 Replies
View Related
Jan 5, 2001
Hello,
I am transferring data from one SQL table to another. The first table has a PK on the unique id only, the second table has PK on five fields (the idea being to reject duplicate records etc. etc.). I am using a DTS package to do this, but when run it will fail when it hits a PK violation. How do I getround this??????? What simple thing am i missing??
TIA DVNC
View 4 Replies
View Related
Dec 12, 2006
I am getting this error occasionally on my multi-user web app with the data stored in SQL Server. The key is an identity field incremented by 1 - like Access autonumber. It seems that when two people are writing to the table at the same time they try and use the same key. I didn't think this could happen in sql server ?
It is a one to many table situation and the error occurs in the one table
What is the solution please.
INSERT statement conflicted with the FOREIGN KEY constraint
View 16 Replies
View Related
Feb 12, 2008
I have a Brokers table and Trans (transactions) table. When I insert new transactions, I run a stored procedure or trigger to update the Brokers table since a transaction may have a new Broker. Is there an simpler way to do this than my query below?
INSERT INTO Brokers (Brokers.broker_id)
SELECT X.broker_id
FROM (SELECT DISTINCT broker_id FROM Trans) As X
WHERE NOT EXISTS (SELECT Brokers.broker_id FROM Brokers WHERE Brokers.broker_id = X.broker_id)
View 7 Replies
View Related
Nov 21, 2006
there have a exception when I trying to run a insert query
the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..
why? and how to resolve it?
thank you
View 1 Replies
View Related
Mar 7, 2008
Hello Friends
How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like which is describe below:
Error:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters, CommandType commandType) at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters) at LCO.pname.DAL.BLL.AccountInfo.funName(Parameters)
In above mentioned error, foreign key is availabe in child table. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance.
Regards,
View 1 Replies
View Related
May 21, 2008
i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.
View 5 Replies
View Related
Feb 25, 2008
Hi. i am updating a field on a table i have called tblNavproductId. when doing the insert, i get the error message that you know, it is is violation of a foreign key constraint on NavId. i see the foreign key, so i know it is there and i understand that. i read the books online and here is the article;
http://msdn2.microsoft.com/en-us/library/ms186247.aspx
does anyone have a good example of this type of query? i am putting 990k+ records in at once, so would this be a bulk insert? Thanks.
View 3 Replies
View Related
Feb 28, 2008
Hi,
I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.
I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.
I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?
Your reply would be highly appreciated. TIA.
View 6 Replies
View Related
Jan 15, 2008
Hi all,
I have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:
The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.
An insert statement for the above would look something like the following:
INSERT INTO tblPackages (PkgNo, PkgName, PkgEngineerID, PkgContrAdmin, PkgRemark)VALUES (1234, 'My Package', NULL, NULL, 'My Package remark')
And the create table statements are:
USE [PASSQL]GO/****** Object: Table [dbo].[tblPackages] Script Date: 01/15/2008 23:25:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblPackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PkgNo] [nvarchar](20) NULL, [PkgName] [nvarchar](255) NULL, [PkgEngineerID] [int] NULL, [PkgContrAdmin] [int] NULL, [PkgRemark] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblPackages_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees] FOREIGN KEY([PkgEngineerID])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees1] FOREIGN KEY([PkgContrAdmin])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees1]
And:
USE [PASSQL]GO/****** Object: Table [dbo].[tblEmployees] Script Date: 01/15/2008 23:28:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblEmployees]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](255) NULL, [EmpShort] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblEmployees_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Any ideas on how to accomplish this would be great!
Thanks in advance!
View 4 Replies
View Related
Mar 27, 2008
Hi,
I am having 'INSERT STATEMENT CONFLICTED' error since I try to configure sql server 2005 Full Text Search for my database. Before everything was going fine but after doing some changings I mess up my all DB's. None of my database is working. The error only occur while inserting the records using web form. But I can insert directly from Management Studio interface. here is part of my error:
ERROR:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Dept_LegalLaw_Dept_LegalMinistries1". The conflict occurred in database "LegalDB", table "dbo.Dept_LegalMinistries", column 'RegID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
...............AND SO ON
INSERT QUERY:
INSERT INTO Dept_LegalLaw (
Agreement,Amendment,Attachment,Html,ID,IssueNo,LawDate,LawID,LawNo,Mistakes,Name,NameSearch,Pages,RegID,Scanned,SerialNo,Path) VALUES (
'1',NULL,NULL,'1','C0667','2666','5/26/2004 12:00:00 AM','2','22',NULL,N'تعميم بشأن عطلة ذكرى المولد النبوي الشريÙ?',N'مرسوم اميري رقم 29 لسنة 2001 بانشاء لجنة الاسكان والاعمار','1','RAC,',NULL,'5556','RCAB4505.htm')
--RETURN THE NEW IDENTITY VALUE
SELECT SCOPE_IDENTITY() MediaID
I really need urgent help. that how i can reverse this thing back.
Thanks
View 1 Replies
View Related
Jul 23, 2004
Hi,
I am a SQL DBA involved in accomplishing various activities using Enterprise Manager by regestering servers of various countries.
Now as the NT Team has gone for auditing implementation in all the servers in OS lever, they find Access violations from the workstation from where I connect to the server. This does not happens if I unregester the Servers from Enterprise manager and use only query analyzer.
Note: We have our own users in OS as wel as in SQL SERVER .
Its Urgent!!! Please help!!!
View 4 Replies
View Related
Feb 1, 2007
Hi~,
Before implementing memory based bulk copy insert with IRowsetFastLoad interface of SQL Server 2005 OLE DB provider, I want to know some considerations.
- performance : compared with T-SQL's "BULK INSERT ..." and bcp utility
- SQL Server's resource usage : when running memory based bulk copy, server resource's influence
- server side action(behavior) : when server is busy, delayed-update means IRowsetFastLoad::Commit(true) method can insert right after?
- row-count : The rowcount limitation can be inserted by IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit
- any other guide lines
View 1 Replies
View Related
Mar 16, 2007
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
View 8 Replies
View Related
Jan 11, 2008
Hi,A conflict error occured when I insert data using Formview.The INSERT statement conflicted with the FOREIGN KEY constraint "FK_personal_personal_category". The conflict occurred in database "E:INETPUBWWWROOTWEBSITEAPP_DATADATABASE.MDF", table "dbo.personal_category", column 'cat_id'.The statement has been terminated. ----------------------------------------------------Personal TablepID (primary)pNamecat_id (foreign key from personal_category, which has a relationship with it's primary key cat_id)pID | pName | cat_id1 | Scott | 112 | John | 122 | Mick | 13-----------------------------------------------------Personal_Category Tablecat_id (primary)cat_Descriptioncat_id | cat_description11 | Dean12 | Student13 | Professor-----------------------------------------------------I'm inserting a new data on Personal Table while choosing a category usinga DropdownList which is binded to another DataSource (LinqDataSource2).... <asp:FormView ID="FormView1" runat="server" DataKeyNames="tut_id" DataSourceID="LinqDataSource1" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DefaultMode="Insert" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <InsertItemTemplate> <asp:TextBox ID="pName" runat="server" Text='<%# Bind("pName") %>' /> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="LinqDataSource2" DataTextField="cat_desc" DataValueField="cat_id" DataSource='<%# Bind("tut_cat") %>' > </asp:DropDownList> </InsertItemTemplate> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> </asp:FormView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="PersonalsDataContext" TableName="Personal" EnableInsert = "true"> </asp:LinqDataSource> <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="PersonalCategoriesDataContext" TableName="Personal_categories" EnableInsert="false" > </asp:LinqDataSource>... Hope you could help.cheers,imperialx
View 2 Replies
View Related
Feb 18, 2006
Hi there,
I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.
This is the error i get:
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "
Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.
Thank you
Melanie
View 13 Replies
View Related
Feb 17, 2015
An SSIS task imports data from a flat file and inserts the data into a staging table. The staging table holds the data in its raw form. A second process then selects the data from the staging table, looking up the foreign key id's for raw data values, and then inserts the data into the live table.
SQL - Only key columns shown for clarity
-- Staging Table
CREATE TABLE Staging
(Information VARCHAR(10),
MachineName VARCHAR(10),
Status VARCHAR(10))
[code]...
The insert into the live table should look up the id for machine 1, and the id of status success and insert the foreign key values into the live table for the row.There could be 1000's of rows for the output of machine 1 all with different status's - (all pre set in the Status table, i.e success, failure, rerun) and the same for lots of other machines held in the machine table.
What is the best to insert this data all in one go, rather than reading each row of the staging table one by one, looking up the foreign key values depending on the machine and status values, then inserting the data.
I was thinking along the lines of:
INSERT INTO dbo.LiveTable
(Information, MachineID, StatusId)
SELECT Staging.Information, dbo.Machine.MachineId, dbo.Status.StatusId
FROM dbo.Staging
JOIN Machine ON Machine.MachineName = Staging.MachineName
JOIN STATUS ON Status.Status = Staging.Status But I notice the problem with this is, it doubles up the inserts!
View 2 Replies
View Related
Oct 2, 2015
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] NULL
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[code]....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables?
View 7 Replies
View Related
Oct 2, 2015
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:
CREATE TABLE [dbo].[Locker](
 [lockerID] [int] IDENTITY(1,1) NOT NULL,
 [schoolID] [int] NOT NULL,
 [number] [varchar](10) NOT NULL,Â
 [lockID] [int] NULLÂ
 CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[Code] ....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
 FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
View 7 Replies
View Related