some issues with inserting data in tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

some issues with inserting data in tables

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-25 : 05:41:30

HI ALL,

i am trying insert data into four tables.... input data is coming in xml format... in first two table sits getting inserted
1)FIS_Journal_Sections
2)FIS_Journal_DFS

in this two tables data is not getting inserted if i insert multiple data its geting inserted into other tables
its not throwing error... please help to solve this issue... i am attaching code below

ALTER PROCEDURE [dbo].[FSP_Journal_Insert_Copy]--
@XML NVARCHAR(MAX),
@ErrorID int=Null OUTPUT
As
BEGIN
DECLARE @idoc INT,@ERR INT,@Journal_ID int,@ID1 INT,@Tier_ID int,
@Modified_By int,@Modified_Date datetime, @Created_By int,@Login_User_ID int
CREATE TABLE #Journal
(
Journal_Type_ID int,
Pub_Type_ID int,
Journal_Name nvarchar(100),
Abbreviation nvarchar(100),
Short_Name nvarchar(100),
ISSN nvarchar(100),
ISSN_Electronic nvarchar(100),
NLMID nvarchar(50),
Mission_Statement nvarchar(4000),
About nvarchar(4000),
Start_Year int,
Lang_ID int,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int,
Tier_ID int,
)

CREATE TABLE #Journal_DFS
(
Tier_ID int,
Tax_ID int,
Domain_ID int,
Field_ID int,
Speciality_ID int,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int
)

CREATE TABLE #Journal_Section
(
Section_ID int,
Journal_ID int,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int
)

CREATE TABLE #Journal_ArticleType
(
Journal_ID int,
Article_Type_ID int,
[Status] nchar,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime
)

CREATE TABLE #Journal_Repository
(
Journal_ID int,
Repository_ID int,
[Status] nchar,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int
)

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRANSACTION
-- Insert statements for procedure here
EXEC SP_XML_PREPAREDOCUMENT @idoc OUTPUT,@XML
INSERT INTO #Journal
(
Journal_Type_ID,
Pub_Type_ID,
Journal_Name,
Abbreviation,
Short_Name,
ISSN,
ISSN_Electronic,
NLMID,
Mission_Statement,
About,
Start_Year,
Lang_ID,
Modified_By,
Modified_Date,
Created_By,
Login_User_ID,
Tier_ID
)

SELECT * FROM OPENXML(@idoc,'Journal/FIS_Journal')
WITH
(
Journal_Type_ID int,
Pub_Type_ID int,
Journal_Name nvarchar(100),
Abbreviation nvarchar(100),
Short_Name nvarchar(100),
ISSN nvarchar(100),
ISSN_Electronic nvarchar(100),
NLMID nvarchar(50),
Mission_Statement nvarchar(4000),
About nvarchar(4000),
Start_Year int,
Lang_ID int,
Modified_By int,
Modifed_Date datetime,
Created_By int,
Login_User_ID int,
Tier_ID int
)

SELECT @Tier_ID= Tier_ID,@Modified_By=Modified_By,@Modified_Date=Modified_Date,@Created_By=Created_By,@Login_User_ID=Login_User_ID FROM #Journal

INSERT INTO #Journal_DFS
(
Tier_ID,
Tax_ID,
Modified_By,
Modified_Date,
Created_By,
Login_User_ID
)

SELECT
@Tier_ID,
Tax_ID,
@Modified_By,
@Modified_Date,
@Created_By,
@Login_User_ID
FROM OPENXML(@idoc,'Journal/FIS_Journal_DFS')
WITH
(
Tier_ID int,
Tax_ID int,
Modified_By int,
Modified_Date datetime,
Created_By int,
Login_User_ID int
)

UPDATE #Journal_DFS SET #Journal_DFS.Domain_ID= FIS_Domain.Domain_ID
FROM FIS_Domain INNER JOIN #Journal_DFS ON FIS_Domain.Domain_ID = #Journal_DFS.Tax_ID AND #Journal_DFS.Tier_ID =3

UPDATE #Journal_DFS SET #Journal_DFS.Domain_ID= FIS_Domain_Fields.Domain_ID,#Journal_DFS.Field_ID= FIS_Domain_Fields.Field_ID
FROM FIS_Domain_Fields INNER JOIN #Journal_DFS ON FIS_Domain_Fields.Domain_Field_ID = #Journal_DFS.Tax_ID AND #Journal_DFS.Tier_ID =2

UPDATE #Journal_DFS SET #Journal_DFS.Domain_ID= FIS_Domain_Field_Speciality.Domain_ID,#Journal_DFS.Field_ID= FIS_Domain_Field_Speciality.Field_ID ,#Journal_DFS.Speciality_ID=FIS_Domain_Field_Speciality.Speciality_ID
FROM FIS_Domain_Field_Speciality INNER JOIN #Journal_DFS ON FIS_Domain_Field_Speciality.Domain_Field_Spl_ID = #Journal_DFS.Tax_ID AND #Journal_DFS.Tier_ID =1

INSERT INTO #Journal_Section
(
Section_ID,
Journal_ID,
Modified_By,
Modified_Date,
Created_By,
Login_User_ID
)

SELECT
Section_ID,
@Journal_ID,
@Modified_By,
@Modified_Date,
@Created_By,
@Login_User_ID
FROM OPENXML(@idoc,'Journal/FIS_Journal_Sections')
WITH
(
Section_ID int,
Journal_ID int,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int
)

INSERT INTO #Journal_ArticleType
(
Journal_ID,
Article_Type_ID,
[Status],
Modified_By,
Modified_Date,
Created_By
)

SELECT
@Journal_ID,
Article_Type_ID,
[Status],
@Modified_By,
@Modified_Date,
@Created_By
FROM OPENXML(@idoc,'Journal/FIS_Journal_ArticleType')
WITH
(
Journal_ID int,
Article_Type_ID int,
[Status] nchar,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime
)

INSERT INTO #Journal_Repository
(
Journal_ID,
Repository_ID,
[Status],
Modified_By,
Modified_Date,
Created_By,
Login_User_ID
)

SELECT
@Journal_ID,
Repository_ID,
[Status],
@Modified_By,
@Modified_Date,
@Created_By,
@Login_User_ID
FROM OPENXML(@idoc,'Journal/FIS_Journal_Repository')
WITH
(
Journal_ID int,
Repository_ID int,
[Status] nchar,
Modified_By int,
Modified_Date datetime,
Created_By int,
Created_Date datetime,
Login_User_ID int
)

EXEC SP_XML_REMOVEDOCUMENT @idoc

--SELECT * INTO #tmp
--FROM #Journal_DFS WHERE 0=(SELECT COUNT(FIS_Journal_DFS.Journal_DFS_ID) FROM FIS_Journal_DFS WITH(NOLOCK)
-- WHERE FIS_Journal_DFS.Domain_ID=#Journal_DFS.Domain_ID
-- AND FIS_Journal_DFS.Field_ID=#Journal_DFS.Field_ID
-- AND FIS_Journal_DFS.Speciality_ID=#Journal_DFS.Speciality_ID )

--IF (SELECT COUNT(*) FROM #tmp)>0
IF(SELECT COUNT(Journal_DFS_ID) FROM FIS_Journal_DFS)>0
BEGIN
--BEGIN TRY
/*Journal Table*/

INSERT INTO [dbo].[FIS_Journal]
(
Journal_Type_ID,
Pub_Type_ID,
Journal_Name,
Abbreviation,
Short_Name,
ISSN,
ISSN_Electronic,
NLMID,
Mission_Statement,
About,
Start_Year,
Lang_ID,
Modified_By,
Modifed_Date,
Created_By,
Created_Date,
Login_User_ID
)
SELECT
Journal_Type_ID,
Pub_Type_ID,
Journal_Name,
Abbreviation,
Short_Name,
ISSN,
ISSN_Electronic,
NLMID,
Mission_Statement,
About,
Start_Year,
Lang_ID,
Modified_By,
Modified_Date,
Created_By,
GETDATE(),
Login_User_ID
FROM #Journal WITH(NOLOCK)

SET @Journal_ID =SCOPE_IDENTITY ()
IF @@ERROR<>0
BEGIN
/*Journal DFS */
INSERT INTO [dbo].[FIS_Journal_DFS]
(
Journal_ID,
Tier_ID,
Tax_ID,
Domain_ID,
Field_ID,
Speciality_ID,
Modified_By,
Modified_Date,
Created_By,
Created_Date,
Login_User_ID
)

SELECT
@Journal_ID,
Tier_ID,
Tax_ID,
Domain_ID,
Field_ID,
Speciality_ID,
Modified_By,
Modified_Date,
Created_By,
GETDATE(),
Login_User_ID
AffSeq_No
FROM #tmp WITH(NOLOCK)
IF @@ERROR<>0
BEGIN
/*Journal Sections */
INSERT INTO [dbo].[FIS_Journal_Sections]
(
Section_ID,
Journal_ID,
Modified_By,
Modified_Date,
Created_By,
Created_Date,
Login_User_ID
)

SELECT
Section_ID,
Journal_ID,
Modified_By,
Modified_Date,
Created_By,
GETDATE(),
Login_User_ID
FROM #Journal_Section WITH(NOLOCK)

/*Journal ArticleType*/
INSERT INTO [dbo].[FIS_Journal_ArticleType]
(
Journal_ID,
Article_Type_ID,
[Status],
Modified_By,
Modified_Date,
Created_By,
Created_Date
)

SELECT
@Journal_ID,
Article_Type_ID,
[Status],
Modified_By,
Modified_Date,
Created_By,
GETDATE()
FROM #Journal_ArticleType WITH(NOLOCK)

/*Journal Repository*/
INSERT INTO [dbo].[FIS_Journal_Repository]
(
Journal_ID,
Repository_ID,
[Status],
Modified_By,
Modified_Date,
Created_By,
Created_Date,
Login_User_ID
)

SELECT
@Journal_ID,
Repository_ID,
[Status],
Modified_By,
Modified_Date,
Created_By,
GETDATE(),
Login_User_ID
FROM #Journal_Repository WITH(NOLOCK)
END
END
COMMIT TRANSACTION
SELECT 1
IF @@ERROR=0
BEGIN
ROLLBACK TRANSACTION
SELECT 0
END
--END TRY
--BEGIN CATCH
-- ROLLBACK TRANSACTION
-- SELECT 0
--END CATCH
END

END

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:06:54

can you check if below is > 0? thats what you've given as condition for inserts

SELECT COUNT(Journal_DFS_ID) FROM FIS_Journal_DFS

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-25 : 08:00:53

ya it has values .. the count o/p is 25

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 08:05:23

what does this return?

SELECT
Journal_Type_ID,
Pub_Type_ID,
Journal_Name,
Abbreviation,
Short_Name,
ISSN,
ISSN_Electronic,
NLMID,
Mission_Statement,
About,
Start_Year,
Lang_ID,
Modified_By,
Modified_Date,
Created_By,
GETDATE(),
Login_User_ID
FROM #Journal WITH(NOLOCK)

divyaram
Posting Yak Master

180 Posts

Posted - 2010-01-25 : 08:14:06

2 1 tuytu tuytru truyrtu 2010 1 5 2010-01-25 09:16:01.240 5 NULL 5 2

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 09:39:20

still its not getting inserted to destination table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources