Insert row into another table if row doesn't exist

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert row into another table if row doesn't exist

Author

Topic

dpnadam
Starting Member

29 Posts

Posted - 2012-03-22 : 05:26:03

Hi

I realise there are lots of posts asking this question and I have read a lot of them, but still my script doesn't seem to be working. It keeps giving me a duplicate key violation error. Syntactically it looks the same to me that I have read in other posts but may be I am missing something?

Basically I have table User and table UserDefined. I'm trying to insert row from User into UserDefined if the row based on two int fields, doesn't already exist.

Tables are as follows:


CREATE TABLE [dbo].[User](
	[DiariedCourseID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED 
(
	[DiariedCourseID] ASC,
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[UserDefined](
	[DiariedCourseID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[UserDefChar1] [varchar](255) NULL,
	[UserDefChar2] [varchar](255) NULL,
	[UserDefChar3] [varchar](255) NULL,
	[UserDefChar4] [varchar](255) NULL,
	[UserDefChar5] [varchar](255) NULL
 CONSTRAINT [PK_UserDefined] PRIMARY KEY NONCLUSTERED 
(
	[DiariedCourseID] ASC,
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

My script is as follows:


INSERT INTO [dbo].[UserDefined]
                  (DiariedCourseID, EmployeeID, UserDefChar1, UserDefChar2, UserDefChar3, UserDefChar4, UserDefChar5)
	SELECT U.[DiariedCourseID], U.[EmployeeID], E.[Surname], E.[Firstname], E.[dateemploy], EJ.[Hierarchy1],
		  (RT.firstname+' '+RT.Surname) ReportsTo
	FROM [User] U
	INNER JOIN [dbo].[Employee] E ON E.[empid] = U.[EmployeeID]
	LEFT OUTER JOIN [dbo].[EmployeeJobs] EJ ON EJ.[empid] = U.[EmployeeID]
	LEFT OUTER JOIN [dbo].[Employee] RT ON RT.[empid] = EJ.[ReportsTo]
	WHERE NOT EXISTS(SELECT * FROM [dbo].[UserDefined] UD
                        WHERE UD.[EmployeeID] = U.[EmployeeID] AND UD.[DiariedCourseID] = U.[DiariedCourseID])

Any help would be greatly appreciated.

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-22 : 06:41:29

You are joining to Employee table twice, on different keys? This could be giving you your dupes.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dpnadam
Starting Member

29 Posts

Posted - 2012-03-22 : 07:06:57

quote:
Originally posted by DonAtWork

You are joining to Employee table twice, on different keys? This could be giving you your dupes.

Thanks for your reply DonAtWork.

Would you be able to provide a solution to what you've highlighted please?

I've joined it twice as a user should directly link to an employee, but also the user can report to a manager (which is an employee).

Thanks

[EDIT]

Apologies, that line actually had a typo. It should have read:

LEFT OUTER JOIN [dbo].[Employee] RT ON RT.[empid] = EJ.[ReportsTo]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-22 : 10:24:10

If you could give some sample data and expected output, possibly.
Also, the other tables you are joining to would help.

Best case, Tan or Madhi will see this and Magic 8-ball an answer for you

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dpnadam
Starting Member

29 Posts

Posted - 2012-03-26 : 05:16:22

quote:
Originally posted by DonAtWork

If you could give some sample data and expected output, possibly.
Also, the other tables you are joining to would help.

Best case, Tan or Madhi will see this and Magic 8-ball an answer for you

Hi

The scripts for creating the tables and inserting some data are below. As you can see there are 4 employees. They may or may not have a record in EmployeeJobs. (In this case they do). In the user table there is an insert for Bob Smith, Fred Bloggs and Tony Black.

In the user defined table there is a record for Bob Smith and Bill Taylor. Bill Taylor doesn't have a corresponding User record entry as this record has come from another table which doesn't matter in this case.

I basically need to get Fred Bloggs and Tony Black into the UserDefined table.


CREATE TABLE [dbo].[Employee](
	[empid] [int] NOT NULL,
	[Surname] [varchar](30) NOT NULL,
	[Firstname] [varchar](30) NOT NULL,
	[dateemploy] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[empid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[EmployeeJobs](
	[id] [int] NOT NULL,
	[empid] [int] NOT NULL,
	[ReportsTo] [int] NULL,
	[Hierarchy1] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[User](
	[DiariedCourseID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY NONCLUSTERED 
(
	[DiariedCourseID] ASC,
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[UserDefined](
	[DiariedCourseID] [int] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[UserDefChar1] [varchar](255) NULL,
	[UserDefChar2] [varchar](255) NULL,
	[UserDefChar3] [varchar](255) NULL,
	[UserDefChar4] [varchar](255) NULL,
	[UserDefChar5] [varchar](255) NULL,
 CONSTRAINT [PK_UserDefined] PRIMARY KEY NONCLUSTERED 
(
	[DiariedCourseID] ASC,
	[EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [Employee]([empid],[Surname],[Firstname], [dateemploy]) VALUES(1,'Smith', 'Bob', '01/01/01')
INSERT INTO [Employee]([empid],[Surname],[Firstname], [dateemploy]) VALUES(2,'Bloggs', 'Fred', '02/02/02')
INSERT INTO [Employee]([empid],[Surname],[Firstname], [dateemploy]) VALUES(3,'Black', 'Tony', '03/03/03')
INSERT INTO [Employee]([empid],[Surname],[Firstname], [dateemploy]) VALUES(4,'Taylor', 'Bill', '04/04/04')
GO

INSERT INTO [EmployeeJobs]([id],[empid], [ReportsTo], [Hierarchy1]) VALUES(1,1,4, 'Sales')
INSERT INTO [EmployeeJobs]([id],[empid], [ReportsTo], [Hierarchy1]) VALUES(2,2,4, 'Sales')
INSERT INTO [EmployeeJobs]([id],[empid], [ReportsTo], [Hierarchy1]) VALUES(3,3,4, 'Support')
INSERT INTO [EmployeeJobs]([id],[empid]) VALUES(4,4)
INSERT INTO [EmployeeJobs]([id],[empid], [ReportsTo], [Hierarchy1]) VALUES(5,3,2, 'Support') 
GO

INSERT INTO [User]([DiariedCourseID],[EmployeeID]) VALUES(1,1)
INSERT INTO [User]([DiariedCourseID],[EmployeeID]) VALUES(1,2)
INSERT INTO [User]([DiariedCourseID],[EmployeeID]) VALUES(2,3)
GO

INSERT INTO [UserDefined] ([DiariedCourseID],[EmployeeID],[UserDefChar1],[UserDefChar2],[UserDefChar3],[UserDefChar4],[UserDefChar5])
     VALUES(1,1,'Smith','Bob','01/01/01','Sales','Bill Taylor')
     
INSERT INTO [UserDefined] ([DiariedCourseID],[EmployeeID],[UserDefChar1],[UserDefChar2],[UserDefChar3],[UserDefChar4],[UserDefChar5])
     VALUES(1,4,'Taylor','Bill','04/04/04',NULL,NULL)
GO

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-26 : 13:36:25

so you mean to say you want to insert entire parent hierarchy of records in UserDefined if it doesnt exist already?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dpnadam
Starting Member

29 Posts

Posted - 2012-03-27 : 04:30:13

Hi

Yes basically, which is what I thought my script did, but it gives me a key violation error.

Thanks

dpnadam
Starting Member

29 Posts

Posted - 2012-03-27 : 07:35:34

I've managed to find where the duplicate is coming from.

Basically I was unaware that an Employee can have multiple entries in the EmployeeJobs table.

(Run this to insert duplicate:
INSERT INTO [EmployeeJobs]([id],[empid], [ReportsTo], [Hierarchy1]) VALUES(5,3,2, 'Support') )

So two results are being returned for the EmployeeJobs join.

How can I just take the Top returned record from this join please?

Would this work?

INSERT INTO [dbo].[UserDefined]
                  (DiariedCourseID, EmployeeID, UserDefChar1, UserDefChar2, UserDefChar3, UserDefChar4, UserDefChar5)
	SELECT U.[DiariedCourseID], U.[EmployeeID], E.[Surname], E.[Firstname], E.[dateemploy], EJ.[Hierarchy1],
		  (RT.firstname+' '+RT.Surname) ReportsTo
	FROM [User] U
	INNER JOIN [dbo].[Employee] E ON E.[empid] = U.[EmployeeID]
	LEFT OUTER JOIN [dbo].[EmployeeJobs] EJ ON EJ.[id] = (Select max(id) FROM [EmployeeJobs] WHERE [empid]=U.[EmployeeID])	
LEFT OUTER JOIN [dbo].[Employee] RT ON RT.[empid] = EJ.[ReportsTo]
	WHERE NOT EXISTS(SELECT * FROM [dbo].[UserDefined] UD
                        WHERE UD.[EmployeeID] = U.[EmployeeID] AND UD.[DiariedCourseID] = U.[DiariedCourseID])

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:07:38

How can I just take the Top returned record from this join please?

Top based on what criteria? is there id field for determining order?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources