SQL update data from one table to another

Question

Post reply

SQL update data from one table to another

Dave Schutz

SSCarpal Tunnel

Points: 4190
More actions
March 26, 2010 at 11:27 am

#386585

I have 2 tables and first table is updated daily from our accounting app. Second table uses info from first table and needs to have that info updated daily. I'm trying to create a SQL job that will take employId field from first table to empId field second table, but only for users with a specific deprtmnt field (table 1).
Here's the build for tables:
Table 1
USE [Database]
GO
/****** Object: Table [dbo].[employees] Script Date: 03/26/2010 13:20:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employees](
[employId] [char](15) NOT NULL,
[lastName] [char](21) NOT NULL,
[firstName] [char](15) NOT NULL,
[deprtmnt] [char](7) NOT NULL,
[strtDate] [datetime] NOT NULL,
[empClas] [char](15) NOT NULL,
[dual] [char](7) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Table 2
USE [Database]
GO
/****** Object: Table [dbo].[SLsubDept] Script Date: 03/26/2010 13:21:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SLsubDept](
[groupId] [int] NOT NULL,
[empId] [char](14) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Here's my thought on the sql code:
INSERT INTO SLsubDept ( empId )
SELECT employId
FROM employees
WHERE deprtmnt='740000'
But when I run this it doesn't add any new rows to table 2.
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135307 More actions · Answer 1

you declared SLsubDept with the column

[groupId] [int] NOT NULL

This means you must provide a value for this column when inserting new rows.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dave Schutz SSCarpal Tunnel Points: 4190 More actions · Answer 2

ALZDBA

I changed to null setting to allow nulls and ran the job and it makes no difference.

K Cline Ten Centuries Points: 1391 More actions · Answer 3

Does your initial query produce an error or just not pull back any results?

Couple of points.

1. The EmployId in table 1 does not match up with EmpId. They are Char(15) and Char(14) respectively. Not sure if this was a typo, but if not it could possibly lead to a truncation error later on.

2. You are using Char instead of VarChar. That being the case you need to pay extra attention to how the stored data is padded. Could be your data was saved with a leading space instead of a trailing space. ("740000 " = "740000"), but (" 740000" != "740000"). If you are certain there are records that contain "740000" in the [deprtmnt] field, then try:

Insert Into SLsubDept (empId)

Select employId

From dbo.employees

Where (LTRIM(deprtmnt) = '740000');

Best of luck.

Johan Bijnens SSC Guru Points: 135307 More actions · Answer 4

My guess is K Cline is spot on.

Run your original select query and see if it produces result rows.

If not, find out why. K Cline explained what to do.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

Dave Schutz (3/26/2010)
I have 2 tables and first table is updated daily from our accounting app. Second table uses info from first table and needs to have that info updated daily. I'm trying to create a SQL job that will take employId field from first table to empId field second table, but only for users with a specific deprtmnt field (table 1).

This is generally speaking a bad idea. It's better to store the information in one place only and retrieve it in a query when it is needed. As with many things in SQL, having the right design to start with is the most important part of the solution.

The two tables you posted don't include any keys. Always include keys when you ask a question such as this. Every table should have at least one key and if you don't include it then you may not get the right answers or the best answers to your question.

Dave Schutz SSCarpal Tunnel Points: 4190 More actions · Answer 6

Using K Cline's code works except it appends all the records (empId) from table 1 onto table 2 not just the new records, creating duplicate records. I cannot delete the info in table 2 and then run the job as users are changing the groupId field from a web app. I thinking about using distinct.