Insert Missing Primary Key Utilized By Another Table
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
ADVERTISEMENT
Jun 18, 2007
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
View 5 Replies
View Related
Nov 17, 2005
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
View 3 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
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 29, 2015
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
View 10 Replies
View Related
Dec 10, 2007
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in
the User profile Table)
Review_Details
Thanks
Odxsigma
View 3 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
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
Apr 24, 2014
I have table A (EmployeeNumber, Grouping, Stages)
and
Table B (Grouping, Stages)
Table A could look like the following where the multiple employees could have multiple types and multiple stages.
EmployeeNumber, Type, Stages
100, 1, Stage1
100, 1, Stage2
100, 2, Stage1
100, 2, Stage2
200, 1, Stage1
200, 2, Stage2
Table B is a list of requirements that each employee must have. So every employee must have a type 1 and 2 and the associated stages listed below.
Type, Stage
1, Stage1
1, Stage2
2, Stage1
2, Stage2
2, Stage3
2, Stage4
So I know that each employee should have 2 Type 1's and 4 Type 2's. I hope that makes sense, I'm trying to change my data because ours is very proprietary.
I need to identify employees who do not have all their stages and list the stages they are missing. The final report should only have employees and the associated missing types and stages.
I do a count by employee to see how many types they have to identify the ones that don't have all the types and stages.
My count would look something like this:
EmployeeNumber Type Total
100, 1, 2
100, 2, 2
200, 1, 1
200 1, 2
So I know that employee 100 should have 2 more Type 2's and employee 200 should have 1 more Type 1 and 2 more Type 2's based on the required list.
The problem I'm having is taking that required list and joining to my list of employees with missing data and pulling from it the types and stages that are missing by employee. I thought I could get a list of the employees that are missing information and right join it to the required list where the missing records would be nulls. But, that doesn't work because some employees do have the required information and so I'm not getting any nulls returned.
View 9 Replies
View Related
Jun 3, 2015
SQL Server 2008 R2
Issue: In the globalts I have
<Expansion>
<sub>Multi-lan</sub>
<sub>Multilan</sub>
</expansion>
my where clause.. is similar to.'where Contains(Word, ''formsof(thesaurus,"'+@curWord+'") OR "'+@curWord+'*"'')'When i pass in Multi-Lan, i get results for Multi-LAN and MultiLAN.. however.. when i pass in Multilan.. i dont get results for Multilan nor Multi-lan.I have double checked my formats, and i have checked to see if this word was used in a previous expansion set.. everything checked out. I also have many other expansions that function without issue. BT and Bluetooth are interchangeable.
View 2 Replies
View Related
Jun 24, 2004
HI Folks ,
I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help
Burner
View 3 Replies
View Related
Jan 28, 2008
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
View 6 Replies
View Related
Jan 17, 2008
Hi everyone,
I'm trying to configure log shipping on a 2005 sql server. I follow the wizard's instructions (see http://technet.microsoft.com/en-us/library/ms190640.aspx) and everything looks right except for the backup job that somehow is not being created on the primary server. Secondary server contains copy, restore and alert jobs.
No errors reported on job monitor.
Any ideas?
Regards,
View 9 Replies
View Related
May 29, 2012
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
View 4 Replies
View Related
Nov 23, 2006
Table 1
Code
Quarter
500002
26
500002
27
500002
28
500002
28.5
500002
29
Table 2
Code
Qtr
500002
26
500002
27
I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS
Can anybody help me with how to compare the two tables to find the records not present in Table 2
That is i need this result
Code
Quarter
500002
28
500002
28.5
500002
29
I have come up with this solution
select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)
i need to know if there is some other way of doing the same
Thanks in Advance
Jacx
View 3 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Aug 7, 2006
Hello
Iam using:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
I am using BULK INSERT to import some pipe-delimited flat files into a database.
I am firstly converting the file using VB.NET, to ensure each line of the file has a carriage return (by using streamwriter.writeline), and I am also ensuring there is no blank line at the end of the file (by using streamwriter.write).
Once I have done this, my BULK INSERT command appears to work OK. This is how I am using the statement:
BULK INSERT
tempHISTORY
FROM 'C:TEMPHISTORY.TXT'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '
'
)
NB: The first row in the file is a header row.
This appears to work OK, however, I have found that certain files seem to miss the final line of the file! I have analysed these files incase they have an inconsistant number of columns but they don't.
I have also found that if I knock off the last column of the tempHISTORY table, the correct number of rows are imported. However, of course, I can't just discard one of the columns from the file, I need to import the entire file.
I cannot understand why BULK INSERT is choosing to miss the final line in the file, when the schema of the destination table matches the structure of the file.
View 2 Replies
View Related
Oct 17, 2007
Hi
I'm running transactional repl with updateable subscribtions - queued updating. Running SQL 2005 sp2, win 2003 sp1
I have 18 odd subscribers at the moment, my publisher and disttribution is on the same machine with push subscriptions.
The questions I have
nr 1.
While trying to initialize new subscribers I get loads of deadlocks even after I stop dist cleanup agent. This *I think* cause some other unexpected problems.
nr2.
The queue reader would fail saing it cannot find the "insert" proc on the publisher, although it exists.
I have changed anything on the publication so I'm not sure how this happens or why.
nr3.
I replicate a varbinary(max) column and on the odd occasion get the "Length of LOB data" errors which I then set with sp_configure. The catch here is that the length never exceeds a "len()" of 4000, thus the reported LOB and my calculation doesn't tie up.
Help is appreciated.
Thanks
View 3 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
Sep 29, 2015
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
View 5 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
Jul 23, 2005
Hi,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 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
