How To Insert A Primary Key???
Sep 8, 2006Resolved - thank you.
View 1 Replies
ADVERTISEMENT
Getting Primary Key Id On Insert
Feb 1, 2007i 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
After An Insert, How Do I Get The Primary Key Of The New Row?
Jul 10, 2006I 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?
Returning Primary Key From Insert Sp?
Apr 28, 2007Hi,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 RelatedInsert New Record - Primary Key
Jan 12, 2008Hi
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
Insert And Return The Primary Key
Mar 3, 2005Im 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??
Bulk Insert Does Not Allow Primary Key
Apr 25, 2014I 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 = '
)
Package Insert Primary Key
Nov 7, 2007Hi,
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.
INSERT Violates Primary Key. Which Way Out?
Jul 23, 2005Hi,newbie question. I have an plain INSERT INTO clause:BEGIN TRANINSERT INTO BILLSSELECTBillCode,MyUNIDFROM DPA_BILLSWHERE ErrorCode IS NULLCOMMIT TRANThe original DPA_BILLS table can hold (and actually holds) rows withnon-unique values of BillCode, which is primary key in the destinationBILLS table. An acceptable behaviour would be to update the existingrow. Given that these constraints have to be kept, which is the bestway to act? Shall I process in advance my source table, resolvingforeing key conflicts, or shall I rely on some error handling in theINSERT clause?Thanx
View 2 Replies View RelatedHelp With Simple Insert, How To Use Primary Key?
Sep 12, 2006Ive 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?
Insert Problem With Primary Key
Oct 18, 2007Hi 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!
Primary Key Error With INSERT INTO
Sep 10, 2007Using 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];
How To Insert Into A Table With A Uniqueidentifier As Primary Key?
Jun 28, 2006I 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!
INSERT Data Into Table That Maybe Have That Primary Key Already
May 12, 2007Hi, 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 RelatedProblems With Insert Query And Primary Key
Nov 27, 2007I 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 RelatedHow To Insert Primary Keys Without Using Identity
Nov 17, 2003I 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
Trigger : Insert Maximum Primary ID
Mar 15, 2005Hi,
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
T-SQL (SS2K8) :: How To Insert Or Update Primary Key (int)
May 22, 2014since 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 ?
INSERT A Row If Primary Key Does Not Exist But APPEND If It Does
Jan 14, 2015I 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?
Insert Records Into Gaps In Primary Key
Feb 23, 2006Hi 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.
Insert Into Table-Primary Key Error
Jul 20, 2005I'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 RelatedReturn Primary Key On INSERT Statement
Jul 20, 2005I 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 RelatedInsert Values In Primary Key Field
May 15, 2006Hi,
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.
Catching Primary Key Violation On Insert Error
Aug 9, 2007I'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
Insert Into Relational Table Primary Key From Parent
Sep 22, 2007Hello, 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
How To Return Primary Unique Index Key On Insert
May 27, 2002Hi,
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
Insert Missing Primary Key Utilized By Another Table
May 6, 2015I 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.
Transact SQL :: Incremental Primary Key Insert From Excel To DB?
Jul 6, 2015I 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.
Insert Or Update SSIS For Composite Primary Key
Sep 1, 2006Hi ,
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
SQL Server 2012 :: Insert Foreign Key Value Into Primary Table?
Oct 2, 2015In 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?
Transact SQL :: 2012 / Insert Foreign Key Value Into Primary Table?
Oct 2, 2015In 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?
Stuck Between Cannot Insert The Value NULL Into Column 'ID' And Violation Of PRIMARY KEY Constraint
Jan 20, 2004Cannot find an answer to this in previous posting, though there are similar topics.
My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error:
Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails.
However, trying to stuff that field with a recordCount+1 value (or any value), I get this error:
Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'Primary Key'. Cannot insert duplicate key in object 'News'.
Cannot figure this one out. The value I'm providing for that field is known to be unique, but the SQL Server spits it out each time.
Is there a way to have the ID primary field automatically update with a new value when a new record is generated? This is how I used to do it in Access, but cannot find a similar feature in SQL Server. I'm sure I'm missing something simple, but right now I'm stuck in this "Catch-22" situation.
Please help!
SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?
Oct 31, 2015Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.