INSERT Violates Primary Key. Which Way Out?
Jul 23, 2005
Hi,
newbie question. I have an plain INSERT INTO clause:
BEGIN TRAN
INSERT INTO BILLS
SELECT
BillCode,
MyUNID
FROM DPA_BILLS
WHERE ErrorCode IS NULL
COMMIT TRAN
The original DPA_BILLS table can hold (and actually holds) rows with
non-unique values of BillCode, which is primary key in the destination
BILLS table. An acceptable behaviour would be to update the existing
row. Given that these constraints have to be kept, which is the best
way to act? Shall I process in advance my source table, resolving
foreing key conflicts, or shall I rely on some error handling in the
INSERT clause?
Thanx
View 2 Replies
ADVERTISEMENT
Jan 29, 2008
Hi,
I use a table variable to store some records which are going to be inserted into a table later. When records are being inserted, I get the error saying 'Violation of PRIMARY KEY constraint 'PK_Device'. Cannot insert duplicate key in object 'dbo.device'.' But this simple error message doesn't help to locate which record duplicates. So, is there any way to get the more info to find out which one violate the constraint?
Thanks!
View 4 Replies
View Related
Oct 4, 2004
Hi All,
I am inserting into a table that hold several foreign keys from several tables.
I'm performing this via a client (VB) and I only how to capture the error, but unable to determine which column/field is the one that violates referential integrity.
Any one can shed some light here? Many thanks!
Cyherus
View 7 Replies
View Related
Sep 8, 2006
Resolved - thank you.
View 1 Replies
View Related
Feb 1, 2007
i have the following code in visual studio 2005 using VB
it is running an insert query - this works fine but i want to know how can i get the primaty key value(which is auto generated) of the row that i just inserted...
Dim conn As New SqlConnection(My.Settings.connStr)
conn.Open()
Dim sql As String = "INSERT INTO tblProspect (Prspct_FirstName, Prspct_LastName, Prspct_PropIDPrimary, Prspct_PropIDSecondary, Prspct_ApplicationStatus, Prspct_DateSubmittedOn, Prspct_PrimaryRent, Prspct_SecondaryRent, Prspct_MoveInDate) VALUES ('" & Me.txtFName.Text & "','" & Me.txtLName.Text & "','" & Me.cmbPrimary.SelectedValue & "','" & Me.cmbSecondary.SelectedValue & "','Pending','" & Now & "','" & Me.txtPrimRent.Text & "','" & Me.txtSecRent.Text & "','" & Me.dtMoveIn.Value & "')"
Dim cmd As New SqlCommand(sql, conn)
cmd.ExecuteNonQuery()
i want to get the Prspct_Id which is the primary key of the row that i just inserted..
thanks
View 5 Replies
View Related
Jul 10, 2006
I am using C# and ADO.NET
After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @@IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @@IDENTITY'."}. does anyone know what to do?
View 8 Replies
View Related
Apr 28, 2007
Hi,I've got a stored procedure that's inserting data into a sql database fine. The only problem is that I'm not sure how to read back the value of the auto increment field that was just generated by the insert (e.g the id field). Any help appreciated.
View 1 Replies
View Related
Jan 12, 2008
Hi
I have a table in sql server with a numeric field as Primary Key. When i insert a new record i need that primary key increments automatic (like access auto increment) because i want avoid the possibility of duplicate Primary Keys.
Is that possible?
Thank you
View 2 Replies
View Related
Mar 3, 2005
Im trying to add a record to the DB and then get the primary key for that record. Im doing this but is obviously wrong....
Code:
// set the prepared statement
String sql="INSERT INTO Client(username, country, clientIP, browser, os) VALUES(?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, inUserName);
pstmt.setString(2, inCountry);
pstmt.setString(3, inClientIP);
pstmt.setString(4, inBrowser);
pstmt.setString(5, inOS);
// Insert the row
pstmt.executeUpdate();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SCOPE_IDENTITY()");
System.out.println("Result "+rs);
Any advice??
View 2 Replies
View Related
Apr 25, 2014
I am using the bulk insert statement below to import data from a csv and excel files. As I am running 64bit versions of windows, and could not find any sql import statements.
However I noticed it does not allow any primary key, hence after importing to the temp table, I then import in sql to the main table.Is there anyway to fix the temp table ? ie add a PK value
BULK INSERT CSV_TESTING
FROM 'd:MAM-NAP.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
)
View 2 Replies
View Related
Nov 7, 2007
Hi,
I am new to SSIS and still trying to design my first package.
My package doesn't seem to want to complete when I have set an indentity field with an identity seed in my table as a primary key.
If the field is not a primary key, the package runs fine.
Any ideas how to insert records when there is a primary key in the table?
In another instance, I am actually trying to fill the primary key with a value from flat file and that also does not work.
Any ideas would be appreciated. Thanks.
View 7 Replies
View Related
Sep 12, 2006
Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.
i want an unique identifier for each row.
heres my stored procedure:
CREATE PROCEDURE composeMessage
-- Add the parameters for the stored procedure here
@username varchar(24),
@sender varchar(24),
@date dateTime,
@subject varchar(255),
@message varchar(2500)
AS
BEGIN
insert into Messages(
"Username",
"Sender",
"Date",
"Subject",
"Message"
)
values (
@username,
@sender,
@date,
@subject,
@message
)
END
GO
heres my sqlcreate table:
USE [Messenger]
GO
/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Messages](
[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[ID] [int] NOT NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
As primary keycan't be null, what do i put for primary key for my insert to work?
hope you understand what i mean?
View 3 Replies
View Related
Oct 18, 2007
Hi there,
I wrote a script to copy data from server1(epxress) to server2(2005 standard)
SET identity_insert results on
GO
insert [server2].TEST.dbo.results
select * from [server1].TEST.dbo.results
results table are identical with primary keys.
Problem is when run this code it gives an error
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'dbo.results' can only be specified when a column list is used and IDENTITY_INSERT is ON.
I need a help to set it so when it tries to copy the existing row (same primary key), it ignores it and moves on to the next row..
Thanks guys!
View 11 Replies
View Related
Sep 10, 2007
Using the following t-sql statement on table with a primary key [DateTime], I get a primary key violation. How can I avoid adding duplicate records?
INSERT INTO [destSchema].[destTable]
SELECT t2.*
FROM [srcSchema].[srcTable] t2
LEFT JOIN [destSchema].[destTable] t1
ON t2.[DateTime] = t1.[DateTime]
WHERE (t1.[DateTime] IS NULL) AND (t1.[DateTime] <> t2.[DateTime])
ORDER BY t1.[DateTime];
View 14 Replies
View Related
Jun 28, 2006
I would like to insert into a table with a primary key that has a uniqueidentifier. I would like it to go up by one each time I execute this insert statement. It would be used as my ReportId
My VB code is this.
Protected Sub btncreate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btncreate.Click
'set connection string
Dim errstr As String = ""
Dim conn = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")
'set parameters for SP
Dim cmdcommand = New SqlCommand("sprocInsertNewReport", conn)
cmdcommand.commandtype = CommandType.StoredProcedure
cmdcommand.parameters.add("@UserName", Session("UserName"))
cmdcommand.parameters.add("@Week", vbNull)
cmdcommand.parameters.add("@Date", vbDate)
cmdcommand.parameters.add("@StartTime", vbNull)
cmdcommand.parameters.add("@EndTime", vbNull)
cmdcommand.parameters.add("@HeatTicket", vbNull)
cmdcommand.parameters.add("@Description", vbNull)
cmdcommand.parameters.add("@TakenAs", vbNull)
cmdcommand.parameters.add("@Dinner", vbNull)
cmdcommand.parameters.add("@Hours", vbNull)
cmdcommand.parameters.add("@Rate", vbNull)
cmdcommand.parameters.add("@PayPeriod", vbNull)
cmdcommand.parameters.add("@LastSave", vbNull)
cmdcommand.parameters.add("@Submitted", vbNull)
cmdcommand.parameters.add("@Approved", vbNull)
cmdcommand.parameters.add("@PagerDays", vbNull)
cmdcommand.parameters.add("@ReportEnd", vbNull)
Try
'open connection here
conn.Open()
'Execute stored proc
cmdcommand.ExecuteNonQuery()
Catch ex As Exception
errstr = ""
'An exception occured during processing.
'Print message to log file.
errstr = "Exception: " & ex.Message
Finally
'close the connection immediately
conn.Close()
End Try
If errstr = "" Then
Server.Transfer("TimeSheetEntry.aspx")
End If
My SP looks like this
ALTER PROCEDURE sprocInsertNewReport
@UserName nvarchar(256),
@Week Int,
@Date Datetime,
@StartTime Datetime,
@EndTime DateTime,
@HeatTicket int,
@Description nvarchar(max),
@TakenAs nchar(10),
@Dinner Nchar(10),
@Hours Float,
@Rate Float,
@PayPeriod int,
@LastSave Datetime,
@Submitted Datetime,
@Approved DateTime,
@PagerDays int,
@ReportEnd DateTime
AS
INSERT INTO
ReportDetails
(
rpUserName,
rpWeek,
rpDate,
rpStartTime,
rpEndTime,
rpHeatTicket,
rpTicketDescription,
rpTakenAs,
rpDinnerPremium,
rpHours,
rpRate,
rpPayPeriod,
rpLastSaveDate,
rpSubmittedDate,
rpApprovedDate,
rpPagerDays,
rpReportDueDate
)
VALUES
(
@Username,
@Week,
@Date,
@StartTime,
@EndTime,
@HeatTicket,
@Description,
@TakenAs,
@Dinner,
@Hours,
@Rate,
@PayPeriod,
@LastSave,
@Submitted,
@Approved,
@PagerDays,
@ReportEnd
)
RETURN
Any Ideas?
thx!
View 7 Replies
View Related
May 12, 2007
Hi, I'm not user to inserting data into databases, usually I just read the data. So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names. I set the primary key to be the columns longitude and latitude. I have a method that generates the user's location and the mentioned data. So I want to only insert the new data into the database if it is new and unique. currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information. Do I need to query the database to see if the data record already exists? or is there a way to insert the record only if it is "new"?? Thanks for the help!!
View 2 Replies
View Related
Nov 27, 2007
I am trying to run an insert query off of a sql datasource and I am erroring out. My code and stored procedure as of now are listed below. You will notice the section of the stored procedure that is pulling the value for facility_ID (primary key). I have also tried to pull these and pass the parameter from a label, but that does not work, giving an error that the stored procedure expects the parameter @Facility_ID which was not supplied. One other odd thing is that stepping through the code, I watched the parameter count total 21, but when running the insert command, the insert parameter count shows 20. With the code below (my current project), I get an error that null values can not be entered for facility_ID. Please help. CODE: Dim myConnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FacilitiesBuild").ConnectionString) 'open myconnection myConnection.Open() Dim myCommand As New Data.SqlClient.SqlCommand(SqlDataSourceFac.InsertCommand, myConnection) myCommand.CommandType = Data.CommandType.StoredProcedure myCommand.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50).Value = CType(Me.DetailsView1.FindControl("Textbox5"), TextBox).Text myCommand.Parameters.Add("@Address1", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox3"), TextBox).Text myCommand.Parameters.Add("@Address2", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox4"), TextBox).Text myCommand.Parameters.Add("@State", Data.SqlDbType.VarChar, 2).Value = CType(Me.DetailsView1.FindControl("Textbox17"), TextBox).Text myCommand.Parameters.Add("@Zip", Data.SqlDbType.VarChar, 10).Value = CType(Me.DetailsView1.FindControl("Textbox6"), TextBox).Text myCommand.Parameters.Add("@Phone", Data.SqlDbType.VarChar, 14).Value = CType(Me.DetailsView1.FindControl("Textbox7"), TextBox).Text myCommand.Parameters.Add("@Admin_Name", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox8"), TextBox).Text myCommand.Parameters.Add("@Comments", Data.SqlDbType.VarChar, 250).Value = CType(Me.DetailsView1.FindControl("Textbox10"), TextBox).Text myCommand.Parameters.Add("@Owner", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeOwnerInsert"), Label).Text myCommand.Parameters.Add("@Beds", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("BedsInsert"), Label).Text myCommand.Parameters.Add("@Population", Data.SqlDbType.NText).Value = CType(Me.DetailsView1.FindControl("PopulationInsert"), Label).Text myCommand.Parameters.Add("@Type_Facility", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeFacilityInsert"), Label).Text myCommand.Parameters.Add("@Type_Other", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("TypeOtherInsert"), Label).Text myCommand.Parameters.Add("@Profit", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ProfitInsert"), Label).Text myCommand.Parameters.Add("@Religious", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ReligiousInsert"), Label).Text myCommand.Parameters.Add("@Licensed", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("LicensedInsert"), Label).Text myCommand.Parameters.Add("@Active", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ActiveInsert"), Label).Text myCommand.Parameters.Add("@City_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertCityLabel"), Label).Text myCommand.Parameters.Add("@Agency_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertAgencyLabel"), Label).Text myCommand.Parameters.Add("@County", Data.SqlDbType.NVarChar, 3).Value = CType(Me.DetailsView1.FindControl("InsertCountyLabel"), Label).Text myCommand.Parameters.Add("@Facility_ID", Data.SqlDbType.VarChar, 6).Value = CType(Me.DetailsView1.FindControl("InsertFacilityLabel"), Label).Text If CType(Me.DetailsView1.FindControl("DropDownList1"), DropDownList).SelectedItem.Text = "Please Select One" Then MsgBox("You must select an agency") Else : SqlDataSourceFac.Insert() End If STORED PROCEDURE: ALTER PROCEDURE [dbo].[SP_OMBFacilityAddDOTNET] @Name varchar(50), @Address1 varchar(40), @Address2 varchar(40), @State varchar(2), @Zip varchar(10), @Phone varchar(14), @Admin_Name varchar(40), @Comments varchar(250), @Owner char(1), @Beds int, @Population numeric(10,0), @Type_Facility char(1), @Type_Other varchar(40), @Profit char(1), @Religious char(1), @Licensed char(1), @Active char(1), @City_ID int, @Agency_ID int, @County nvarchar(3)ASBEGINDECLARE @Facility_ID varchar(6) DECLARE @nextID varchar(3) /* get next facilityID */ SELECT @nextID = MAX(RIGHT(Facility_ID, LEN(Facility_ID)-(CHARINDEX('-', Facility_ID)))) + 1 From OMBFacility Where Agency_ID = @Agency_ID SELECT @Facility_ID = CAST(@Agency_ID AS varchar(2)) + '-' + RIGHT('000' + RTRIM(@nextID), 3) INSERT INTO [AIMS].[dbo].[OMBFacility] ([Name], [Address1], [Address2], [State], [Zip], [Phone], [Admin_Name], [Comments], [Owner], [Beds], [Population], [Type_Facility], [Type_Other], [Profit], [Religious], [Licensed], [Active], [City_ID], [Agency_ID], [County], [Facility_ID]) VALUES (@Name ,@Address1 ,@Address2 ,@State ,@Zip ,@Phone ,@Admin_Name ,@Comments ,@Owner ,@Beds ,@Population ,@Type_Facility ,@Type_Other ,@Profit ,@Religious ,@Licensed ,@Active ,@City_ID ,@Agency_ID ,@County ,@Facility_ID)END
View 11 Replies
View Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related
Mar 15, 2005
Hi,
via VBScript, I am inserting data into one table as below:
Code:
conn.Source = "INSERT INTO img (imageDesc,imageName,imageDate,imageUser,imageIP) VALUES ('"& ni &"','"& fn &"','"& Now() &"',"& Session("MM_UserID") &",'"& Request.ServerVariables("REMOTE_HOST") &"')"
In another table, I want to insert the primary key (imageID) of this newly inserted row into a new table called "t_image_Site" along with another value in another column.
Any advice/tutorials... this can be done with a trigger if I'm not mistaken?
JJ
View 4 Replies
View Related
May 22, 2014
since ten years perhpas the society has a database with some table (person, country, and so on)since last years , the project manager decides to create a new database with some new design because before some information was save in one table well, the problem is when we pass all application to the new model we've notice that some id are not the same !
sample :
table Language old model
-------------------------
id frenchDescription english description
----------------------------------------
3 Anglais English
.....
......
table Language new model
id frenchDescription english description
-----------------------------------------
5 Anglais English
.....
.....
Alright , you can understand that the new model must be the same id => 3
my question is how to modify id on the table ?
View 4 Replies
View Related
Jan 14, 2015
I am trying to come up with one statement that will INSERT a row if the primary key does not exist but APPEND if it does
Would the following statement be correct?
INSERT INTO Products (a,b,c) VALUES (2, Cotton Socks, $12) ON DUPLICATE KEY (b=Cotton Socks), (C=$12)
where a is the primary key?
View 3 Replies
View Related
Feb 23, 2006
Hi to all,
I'm a new member here and i would like to ask for some help regarding my problem. first i ahve an incremental primary key with format to something like this: 001-01-001, 001-01-002, 001-01-003, etc. My problem is that i want to insert (supply) the 'missing' or 'gaps' in my primary key field like for example: ..., 001-01-067, 001-01-068, 001-01-070. i want to insert the value 001-01-069 after the record 001-01-068. I have several gaps some ranging from several numbers like 005-04-007,005-04-020 which has a 13 records gap. Is there a way for stored procedure to solve this one?Thanks in advance.
View 8 Replies
View Related
Jul 20, 2005
I'm trying to do multiple insert statements. The table looks likethis:CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2),CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1),HOME_CNTRY_IND char(1),CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST))I'm using 2 fields for the primary key constraintMy insert statement looks like this:INSERT INTO $table(CNTY_CNTRY_CD,ST,CNTY_CNTRY_DESCR)VALUES(?,?,?)I've been through the list of values and none have both the sameCNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQLServer]Violation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicatekey in object 'event_CNTY_CNTRY_CD'. (SQL-23000)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has beenterminated.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)Why is it looking for unique in just the one column instead ofreferencing both? What do I need to do to get this to work? Help!
View 1 Replies
View Related
Jul 20, 2005
I am inserting a record into a table that automatically generatesunique ids (i.e. Primary Key). Is there anyway to return this id. As Iam using this on ASP.net page and I really need the ID to update thepage with the new details.I think on mysql there is something called LAST_INSERT_ID which doesthis.
View 10 Replies
View Related
May 15, 2006
Hi,
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Thanks in advance for any reply.
View 1 Replies
View Related
Aug 9, 2007
I've read a few different articticles wrt how the handle this error gracefully. I am thinking of wrapping my sql insert statement in a try catch and have the catch be something likeIF ( e.ToString() LIKE '% System.Data.SqlClient.SqlException:
Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key
in object %'){lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate" } Is there a better way?TIA Dan
View 4 Replies
View Related
Sep 22, 2007
Hello, I have a Stored Procedure which insterts into Orders table. Orders table is the parent table, with primary key OrdersID. I also have a child table, Client, with foreign key OrdersID. I want it to insert the data into the orders table, and at the same time insert the OrdersID into the FK of the child table. Any info would be appreciated. I have no idea how to do it.
My SP is as follows:ALTER PROCEDURE dbo.jobInsert
@ClientFileNumber varchar(50),@Identity int OUT
ASINSERT Orders(ClientFileNumber, DateTimeReceived) VALUES(@ClientFileNumber, GetDate())
SET @Identity = SCOPE_IDENTITY()
RETURN
View 4 Replies
View Related
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
May 6, 2015
I am converting an old Access/Forms application to a .Net/SQL intranet site. The data was imported and most CRUD features are working. But, I have some data issues to contend with.For this example I have two tables, Cases and Parties. The Parties table includes a Column ReportID, which happens to reference the primary key of some unique Case. Lets say Bob, Sally, and Mary are all associated with ReportID = 2.Looking at the Case table, I see indexes (ReportID) 1,3,4,5, such that Bob, Sally, and Mary are orphaned with out a case to reference.
1. Is it possible to bypass the auto-increment for the primary key of Cases and manually inject the missing Case (ReportID) with a specific primary key of 2? See my image to fill in some of the details.It is too early to tell but perhaps 10% of the Parties are orphan without an associated ReportID in the Case table. This fact is one of the reasons it was decided to rebuild the application, which was also never able to produce any reports.
2. What is an easy way to find all the orphan Parties?
I suppose I could export the Cases table to excel, add the missing Rows, and then overwrite the defective Cases table data with the corrected data. I would rather learn to fix the data issues using the responses from this request.
View 7 Replies
View Related
Jul 6, 2015
I have a table Employee which has following values:
Employee ID Employee Name
1 A
2 B
3 D
Now I have a excel file which will have the new data and I have to write a stored procedure to insert data from Excel file to this table.
Where Employee Id should be auto calculated and the value shud insert from 4.
View 9 Replies
View Related
Sep 1, 2006
Hi ,
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
Jegan.T
View 8 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
