Insert Into Table-Primary Key Error

Jul 20, 2005

I'm trying to do multiple insert statements. The table looks like
this:

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 constraint

My 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 same
CNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Vio
lation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicate
key in obje
ct 'event_CNTY_CNTRY_CD'. (SQL-23000)
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated
.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)

Why is it looking for unique in just the one column instead of
referencing both? What do I need to do to get this to work? Help!

View 1 Replies


ADVERTISEMENT

Primary Key Error With INSERT INTO

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

How To Insert Into A Table With A Uniqueidentifier As Primary Key?

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

INSERT Data Into Table That Maybe Have That Primary Key Already

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

Catching Primary Key Violation On Insert Error

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

How Do I Copy / Insert A Primary Key Value Of One Table Into Another Table?

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

Insert Into Relational Table Primary Key From Parent

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

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 View Related

SQL Server 2012 :: Insert Foreign Key Value Into Primary Table?

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

Transact SQL :: 2012 / Insert Foreign Key Value Into Primary Table?

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

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

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

Invalid Primary Key Error During Table Linking

Dec 30, 2005

Hi,

Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data---Link Tables'. I am getting an error (no surprise!):

" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".

When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.

About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'

I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index.

I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.

Any ideas on what went wrong and what I can do to fix it???

Thanks,
Lori

View 1 Replies View Related

OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.

Mar 24, 2008

Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   
 
 

View 4 Replies View Related

Replication Invalid Syntax Error: Table Has Computed Primary Key

Sep 13, 2006

Hi,

I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.

The replication share on the distributor has all the generated DDL in it.

I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error

The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)

Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?



The ddl for the table index is as follows ( from the replication folder)



/----

CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)

GO

---/

/------ Keys ddl (.dx)

ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go


--------/

/------------

Command attempted:


create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@c1 int,@c2 varchar(80),@c3 char(2),@c4 char(3),@c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @c2
,"ISO 3166-1 ALPHA-2" = @c3
,"ISO 3166-1 ALPHA-3" = @c4
,"ISO 3166-1 NUMERIC-3" = @c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@c2
,@c3
,@c4

(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)

------------/

View 5 Replies View Related

SSMS Express: Create TABLE &&amp; INSERT Data Into Table - Error Msgs 102 &&amp; 156

May 18, 2006

Hi all,

I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.

Thanks in advance,

Scott Chang

///////////--SQLQueryCroomLabData.sql--///////////////////////////
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
VALUES (2, 'MW2', 'Dichloroethene', 1.00)
VALUES (3, 'MW2', 'Trichloroethene', 20.00)
VALUES (4, 'MW2', 'Chloroform', 1.00)
VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
VALUES (6, 'MW6S', 'Acetone', 1.00)
VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
VALUES (9, 'MW6S', 'Chloroform', 1.00)
VALUES (10, 'MW6S', 'Methylene Chloride', 1.00
VALUES (11, 'MW7', 'Acetone', 1.00)
VALUES (12, 'MW7', 'Dichloroethene', 1.00)
VALUES (13, 'MW7', 'Trichloroethene', 1.00)
VALUES (14, 'MW7', 'Chloroform', 1.00)
VALUES (15, 'MW7', 'Methylene Chloride', 1.00
VALUES (16, 'TripBlank', 'Acetone', 1.00)
VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
VALUES (19, 'TripBlank', 'Chloroform', 0.76)
VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
//////////Parse///////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.
////////////////Execute////////////////////
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '6.2'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'VALUES'.

View 7 Replies View Related

Adding Primary Key To A Table Which Has Already A Primary Key

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

Error In Insert To Sql Table

May 11, 2008

hi all when i try to insert a record to sql table i have this error
ncorrect syntax near 'japan'.
the code part iscmdInsert = New SqlCommand("insert into empbill values(" & Val(eno.Text) & ","  & contry1.Text , db)
cmdInsert.ExecuteNonQuery()
the value i entered is 123.japan
i have 2 field in  empbill (emp,cont)
what happend
 

View 2 Replies View Related

Table Insert .... Error

Jun 5, 2002

Hi - I am trying to do the following simple insert that fails...

insert into tbloutgoingobms_hold
select aOBMMessageID, nGlobalCaseID, nOBMailerID,'Food','05-JUN-2002'
from tbloutgoingobms

The table that I am inserting into is a duplicate of the table I am selecting from plus I added two new columns - thus I am inserting constant values into those two new columns: 'Food','05-JUN-2002' . This insert works in Oracle but when I run it on SQL*Server I get the following error: An explicit value for the identity column in table 'tbloutgoingobms_hold' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I used the command: SET IDENTITY_INSERT tbloutgoingobms_hold ON

The column that the 'Food' constant is going into is a varchar(10) and the date column is a datetime column.

Still no luck.... Help please - this cannot be such a difficult thing to do... :(

Thanks is advance,
Nancy

View 3 Replies View Related

Error Trying To Insert Into Table Using XML Param.

May 9, 2008

Hi all,

I also have the same error, I am trying to do two things in my Stored Proc.

1) - Insert a parent record.
2) - Insert 1 or many records in the child table using the parent ID

The child records are being passed as a XML param.

I am also getting the
'Subqueries are not allowed in this context. Only scalar expressions are allowed.'
error when I try to create the procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:pryder
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE TestProc
-- Add the parameters for the stored procedure here
@Name String ,
@bings XML
AS
BEGIN transaction
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @NewID as int
declare @err as int
-- Insert statements for procedure here
INSERT INTO PARENT
(Name)
VALUES (@Name)
SELECT @err = @@error
if @err <> 0
begin
rollback transaction
return @err
end


SELECT SCOPE_IDENTITY = @NewID


INSERT INTO Child
(ParentID,
name)

Values

((
SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)')
FROM @bings.nodes('bings/group') as ParamValues(ID)
))


SELECT @err = @@error
if @err <> 0 begin rollback transaction return @err end


commit transaction
return @@error
GO

View 7 Replies View Related

INSERT INTO Nested Table Error

Jan 9, 2007

I am running the DMX below and I am getting an error we I go to train my structure.  It is probably something stupid but I do not see the problem.  It seems like the SKIP isn't being recognized but I am not sure.  Here is the error:

INSERT INTO error: The '[MSA].[HospitalID]' nested table key column is not bound to an input rowset column.
DMX:
CREATE MINING STRUCTURE [Hospital_Structure] (
 [HospitalID] LONG KEY,
 [SponsorshipTypeID] LONG DISCRETE,
 [GeographicTypeID] LONG DISCRETE,
 [CaseMixIndex] DOUBLE CONTINUOUS,
 [PercentGovtPayers] DOUBLE CONTINUOUS,
 [TotalNumberInpatientCases] LONG CONTINUOUS,
 [MSA] TABLE (
  [HospitalID] LONG KEY,
  [MSAGroupMember] TEXT DISCRETE
 )
);
GO
 
ALTER MINING STRUCTURE [Hospital_Structure]
ADD MINING MODEL [Hospital_Model] (
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (
  [HospitalID],
  [MSAGroupMember]
 )
) USING Microsoft_Clustering;
GO


INSERT INTO MINING STRUCTURE [Hospital_Structure]
(
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (SKIP, [MSAGroupMember])
)
SHAPE { 
  OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [SponsorshipTypeID],
  [GeographicTypeID],
  [CaseMixIndex],
  [PercentGovtPayers],
  [TotalNumberInpatientCases]
 FROM
  [dm].[vw_HospitalClustering_Inputs]
 ORDER BY
  [HospitalID]') }
APPEND
(
  {OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [MSAGroupMember]
 FROM
  [dm].[vw_HospitalClustering_InputsNested]
 ORDER BY
  [HospitalID],
  [MSAGroupMember]')
}
RELATE [HospitalID] TO [HospitalID]
) AS [MSA]

Thanks in advance

View 1 Replies View Related

Insert Error On Existing Table

Oct 10, 2006

I am having the below issue.

Duplicate Key Values

When inserting records into a table created through a RDA Pull(), many users experience duplicate key violations. One reason for this is Identity columns. SQL Server CE RDA does not set the seed on the Identity columns when a table is pulled.

Source: Microsoft SQL Server 2000 Windows CE Edition

Native Error: 25016

HR: DB_E_INTEGRITYVIOLATION

Description: Value violated the integrity constraints for a column or table.

Interface defining error: IID_IRowsetChange

Param = 0

Param = 0

Param = 0

Param =

Param =

Param =

This error can be returned when the user attempts to insert a row with an automatically incremented Identity column. With RDA, this usually occurs when a user pulls rows from the server and attempts to insert new rows before setting the seed and increment values for the Identity column. By default, the seed and increment values are both 1.

To correct this error, set the seed to the next highest number after the table is pulled, before allowing users to enter data.



What do they mean by setting the seed to the next highest level? I this the seed to the GUID row in the pulled table? How do you correctly do this with the ALTER table statement? The database that I am pulling has 21 tables, and would be a pain to have to do this. Does anyone have any other ideas as to why this won't work properly. If I clear out the data from the tables on the publishing server, I can add data to the pull as long as I want until I do another pull later. After I do that, I keep getting the above issue.

View 1 Replies View Related

How To Insert A Primary Key???

Sep 8, 2006

Resolved - thank you.

View 1 Replies View Related

Getting Primary Key Id On Insert

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

After An Insert, How Do I Get The Primary Key Of The New Row?

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

Returning Primary Key From Insert Sp?

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

Insert New Record - Primary Key

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

Insert And Return The Primary Key

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

Bulk Insert Does Not Allow Primary Key

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

Package Insert Primary Key

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

INSERT Violates Primary Key. Which Way Out?

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

Help With Simple Insert, How To Use Primary Key?

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

Insert Problem With Primary Key

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

Problems With Insert Query And Primary Key

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







Copyrights 2005-15 www.BigResource.com, All rights reserved