February 10, 2011 at 2:38 am
I am newbie to sql programming please help me solving this problem of mine
This is code
1.Created two tables
CREATE TABLE CONTACT(
CONTACTID INT,
FIRSTNAME VARCHAR(100),
MIDDELNAME VARCHAR(100),
LASTNAME VARCHAR(100),
CONSTRAINT pk_CONTACT_pid PRIMARY KEY(CONTACTID)
)
GO
CREATE TABLE EMPLOYEE(
CONTACTID INT,
EMPLOYEEID INT,
NationalIDNumber NVARCHAR(30),
LoginID nvarchar(512),
Title nvarchar(100)
CONSTRAINT pk_EMPLOYEE_sid PRIMARY KEY(EMPLOYEEID),
CONSTRAINT fk_EMPLOYEE_pid FOREIGN KEY(CONTACTID)REFERENCES CONTACT(CONTACTID)
)
GO
there is foreign key constraint on employee table
trying insert two tables with one insert statement with identity and condition
IT SHOULD INSERT DATA CONTAINING FIRST NAME 'HENRY' IN TO CONTACT TABLE AND CORRESPONDING LINKED DATA IN EMPLOYEE TABLE
IT SHOULD INSERT
DECLARE @CONTACTID INT
DECLARE @FIRST VARCHAR(100)
SET @FIRST='HENRY%'
INSERT INTO CONTACT(CONTACTID,FIRSTNAME,MIDDELNAME,LASTNAME)
SELECT ContactID,FirstName,MiddleName,LastName WHERE FIRSTNAME=@FIRST
FROM AdventureWorks.Person.Contact
SET @CONTACTID=@@IDENTITY
INSERT INTO EMPLOYEE(CONTACTID,EMPLOYEEID,NationalIDNumber,LoginID,Title)
SELECT CONTACTID,EMPLOYEEID,NationalIDNumber,LoginID,Title
FROM AdventureWorks.HumanResources.Employee
PLEASE HELP ME FOR SOLVING THIS PBM
February 10, 2011 at 2:58 am
Hey Praveen,
SET @FIRST='HENRY%' - it will not select any records from AdventureWorks.Person.Contact for your query
"SELECT ContactID,FirstName,MiddleName,LastName WHERE FIRSTNAME=@FIRST
FROM AdventureWorks.Person.Contact"
So no record id inserted to CONTACT as such it cannot insert any record to foriegn key table "EMPLOYEE".
And no @@IDENTITY will be generated as well.
Try this: -
----------------------------------------------------------------------------
DECLARE @CONTACTID INT
DECLARE @FIRST VARCHAR(100)
SET @FIRST='HENRY'
INSERT INTO CONTACT(CONTACTID,FIRSTNAME,MIDDELNAME,LASTNAME)
SELECT ContactID,FirstName,MiddleName,LastName WHERE FIRSTNAME=@FIRST
FROM AdventureWorks.Person.Contact
SET @CONTACTID=@@IDENTITY
INSERT INTO EMPLOYEE(CONTACTID,EMPLOYEEID,NationalIDNumber,LoginID,Title)
SELECT @CONTACTID,EMPLOYEEID,NationalIDNumber,LoginID,Title
FROM AdventureWorks.HumanResources.Employee
------------------------------------------------------------------------------
February 10, 2011 at 3:02 am
The answer is quite simple. Your ContactID column in table Contact is not an identity column.
N 56°04'39.16"
E 12°55'05.25"
February 10, 2011 at 3:06 am
THANKS I GOT IT
February 10, 2011 at 3:12 am
BUT IN EMPLOYEE TABLE WHEN I CHECK INSERTED DATA AFTER EXECUTING
CONTACT COLUMN SHOWS NULL
Praveen
February 10, 2011 at 3:20 am
Two things:
1. you CONTACTID column is not an IDENTITY;
2. the best way to go here is not to use @@IDENTITY. If your first INSERT...SELECT returns more than 1 row your logic will fail. If more then 1 'HENRY%' exists. You should INNER JOIN the Contacts table in you second statement and set "WHERE ... LIKE 'HENRY%'" or place the affected rows in a table variable and use in both statements. Let me know if you need a sample code.
DV
February 10, 2011 at 4:32 am
HEY
COULD YOU PLEASE SHARE SAMPLE SAMPLE CODE.
THANKS
February 10, 2011 at 4:37 am
Please don't yell!
-- This table is a MUST! Do not remove!
DECLARE@ID TABLE (CtcID INT PRIMARY KEY CLUSTERED)
INSERTdbo.CONTACT
(
CONTACTID,
FIRSTNAME,
MIDDELNAME,
LASTNAME
)
OUTPUTinserted.CONTACTID
INTO@ID
SELECTContactID,
FirstName,
MiddleName,
LastName
FROMAdventureWorks.Person.Contact
WHEREFIRSTNAME LIKE @FIRST
INSERTdbo.EMPLOYEE
(
CONTACTID,
EMPLOYEEID,
NationalIDNumber,
LoginID,
Title
)
SELECTCONTACTID,
EMPLOYEEID,
NationalIDNumber,
LoginID,
Title
FROMAdventureWorks.HumanResources.Employee
INNER JOIN@ID ON ctcID = ContactID
N 56°04'39.16"
E 12°55'05.25"
February 10, 2011 at 4:42 am
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply