Inserts based on conditions - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Inserts based on conditions

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 06:17:47

Hi,

Just cant figure out how to do. I hope someone will help me out

I have 4 tables :

Temp
empid
empname varchar(100)
sex char(8)
st_cd
city_cd

Employees
empid
empname varchar(40)
sex char(1)
category

EmpStateCity
escid
emp_id
st_cd
city_cd

StateCity
scid
st_cd
city_cd

I have to transfer data from Temp to [Employees and EmpStateCity]. I have to take care of the following :

1. In the Temp table, sex can be in the form of 'Male', 'Female', 'M' or 'F'. I have to convert any of these to just M Or F in the Employee Table as sex is of char(1).
2. category in Employees will be 1. However it cannot be used as default. So it has to explicitly specified.
3. One employee can be an employee registered in multiple cities, but same state. So let us say employee Jill has already been added in the Employee table and has empid as 202, then the same empid needs to be used to store other cities to which Jill belongs in the EmpStateCity table. I cannot use empid of temp as empid of temp and empid of Employees is not same. The Employees already has 200 records.
4. If the st_cd and city_cd in the temp table does not match the st_cd and city_cd in StateCity, then those records must be stored in a temp table on the fly.
5. All state and city listed in StateCity table must have employees. So in case there is any employee not assigned to a particular state city, those state and city need to be stored in another temp table while transferring.

SAMPLE SCRIPTS

CREATE TABLE #Temp(
[empid] [int] NULL,
[empname] [varchar](100) NULL,
[sex] [char](8) NULL,
[st_cd] [int] NULL,
[city_cd] [int] NULL
)

CREATE TABLE #Employees(
[empid] [int] NULL,
[empname] [varchar](40) NULL,
[sex] [char](1) NULL,
[category] [int] NULL
)

CREATE TABLE #EmpStateCity(
[escid] [int] NULL,
[emp_id] [int] NULL,
[st_cd] [int] NULL,
[city_cd] [int] NULL
)

CREATE TABLE #StateCity(
[scid] [int] NOT NULL,
[st_cd] [int] NULL,
[city_cd] [int] NULL
)

INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (1, 10, 1)
INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (2, 10, 2)
INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (3, 10, 3)
INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (4, 10, 4)
INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (5, 10, 5)
INSERT #StateCity ([scid], [st_cd], [city_cd]) VALUES (6, 10, 6)

INSERT #Temp ([empid], [empname], [sex], [st_cd], [city_cd]) VALUES (1, N'John', N'Male ', 10, 1)
INSERT #Temp ([empid], [empname], [sex], [st_cd], [city_cd]) VALUES (2, N'Jill', N'Female ', 10, 2)
INSERT #Temp ([empid], [empname], [sex], [st_cd], [city_cd]) VALUES (3, N'Jill', N'Female ', 10, 3)
INSERT #Temp ([empid], [empname], [sex], [st_cd], [city_cd]) VALUES (4, N'Bill', N'Male ', 10, 5)
INSERT #Temp ([empid], [empname], [sex], [st_cd], [city_cd]) VALUES (5, N'Jess', N'F ', 10, 4)

EXPECTED OUTPUT :

Employees
201	John	M	1
202	Jill	F	1
203	Bill	M	1
204	Jess	F	1

EmpStateCity
1	201	80	1
2	202	80	2
3	202	80	3
4	203	80	5
5	204	80	4


temp tables :
(State and City that did not match)
None

(State and City with no employees)
6    10     6

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:24:56

Where are the insert statements for the two other tables?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 06:32:07

Hi peso,

The data has to be transferred from Temp to Employees and EmpStateCity. Hence they will be rather the Expected outputs. That is why there are no insert statements for these 2 tables. Since StateCity is a Master table, hence the data is there.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:37:07

And where do the 200+ values come from?
What are the expected column names?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 06:37:29

Anyways, here is the data for the other two tables as well

INSERT #Employees ([empid], [empname], [sex], [category]) VALUES (201, N'John', N'M', 1)
INSERT #Employees ([empid], [empname], [sex], [category]) VALUES (202, N'Jill', N'F', 1)
INSERT #Employees ([empid], [empname], [sex], [category]) VALUES (203, N'Bill', N'M', 1)
INSERT #Employees ([empid], [empname], [sex], [category]) VALUES (204, N'Jess', N'F', 1)

INSERT #EmpStateCity ([escid], [emp_id], [st_cd], [city_cd]) VALUES (1, 201, 80, 1)
INSERT #EmpStateCity ([escid], [emp_id], [st_cd], [city_cd]) VALUES (2, 202, 80, 2)
INSERT #EmpStateCity ([escid], [emp_id], [st_cd], [city_cd]) VALUES (3, 202, 80, 3)
INSERT #EmpStateCity ([escid], [emp_id], [st_cd], [city_cd]) VALUES (4, 203, 80, 5)
INSERT #EmpStateCity ([escid], [emp_id], [st_cd], [city_cd]) VALUES (5, 204, 80, 4)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 06:40:26

Thank you. Now it makes some sense.
Now please explain the columns in the expected output. For example, what is the rightmost column?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 06:41:07

Hi,
There are already 200 records in the Employee table. However you can ignore that. I just mentioned that as then people would advise me to use the empid in temp table to be stored in EmpStateCity.

Moreover the expected columns are the column names of the tables Employees and EmpStateCity respectively. The column names for point 4 and 5 can be anything. I just want the condition to be taken into consideration

thanks for responding

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 06:45:08

For clarity sake, i will still mention


Employees
empid   empname sex     category
201	John	M	1
202	Jill	F	1
203	Bill	M	1
204	Jess	F	1

EmpStateCity
escid   emp_id  st_cd   city_cd
1	201	80	1
2	202	80	2
3	202	80	3
4	203	80	5
5	204	80	4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 07:06:58

How do yo know which cities an employee already is working on?
There is no relation between #Employees and #StateCity tables.

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 07:21:08

Two ways :

We can compare st_cd and city_cd of Temp and StateCity tables
or after the EmpStateCity table is populated, compare the st_cd and city_cd of EmpStateCity with StateCity

Whichever works best and is easy to query

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 07:32:54

[code]-- Prepare sample data
DECLARE @StateCity TABLE (scid int, st_cd int, city_cd int)
DECLARE @Temp TABLE (empid int, empname varchar(100), sex char(8), st_cd int, city_cd int)
DECLARE @Employees TABLE (empid int, empname varchar(40), sex char(1), category int)
DECLARE @EmpStateCity TABLE (escid int, empid int, st_cd int, city_cd int)

INSERT @StateCity
SELECT 1, 10, 1 UNION ALL
SELECT 2, 10, 2 UNION ALL
SELECT 3, 10, 3 UNION ALL
SELECT 4, 10, 4 UNION ALL
SELECT 5, 10, 5 UNION ALL
SELECT 6, 10, 6

INSERT @Temp
SELECT 1, 'John', 'Male ', 10, 1 UNION ALL
SELECT 2, 'Jill', 'Female ', 10, 2 UNION ALL
SELECT 3, 'Jill', 'Female ', 10, 3 UNION ALL
SELECT 4, 'Bill', 'Male ', 10, 5 UNION ALL
SELECT 5, 'Jess', 'F ', 10, 4

INSERT @Employees
SELECT 200, 'Last', 'M', 1

/*
INSERT @Employees
SELECT 201, 'John', 'M', 1 UNION ALL
SELECT 202, 'Jill', 'F', 1 UNION ALL
SELECT 203, 'Bill', 'M', 1 UNION ALL
SELECT 204, 'Jess', 'F', 1

INSERT @EmpStateCity
SELECT 1, 201, 80, 1 UNION ALL
SELECT 2, 202, 80, 2 UNION ALL
SELECT 3, 202, 80, 3 UNION ALL
SELECT 4, 203, 80, 5 UNION ALL
SELECT 5, 204, 80, 4
*/

-- Prepare INSERTs
DECLARE @EmployeeID INT,
@StateCityID INT

SELECT @EmployeeID = MAX(empid)
FROM @Employees

SET @EmployeeID = COALESCE(@EmployeeID, 0)

SELECT @StateCityID = MAX(escid)
FROM @EmpStateCity

SET @StateCityID = COALESCE(@StateCityID, 0)

-- INSERT into Employees table
INSERT @Employees
(
empid,
empname,
sex,
category
)
SELECT @EmployeeID + ROW_NUMBER() OVER (ORDER BY t.empid) AS empid,
t.empname,
LEFT(t.sex, 1) AS sex,
1 as category
FROM (
SELECT empid,
empname,
sex,
row_number() over (partition by empname order by empid) AS recid
FROM @Temp
) AS t
where t.recid = 1

-- INSERT into EmpCityStates table
INSERT @EmpStateCity
(
escid,
empid,
st_cd,
city_cd
)
SELECT @StateCityID + ROW_NUMBER () OVER (ORDER BY t.empid) AS escid,
e.empid,
80 as st_cd,
t.city_cd
FROM @Temp AS t
INNER JOIN @Employees AS e ON e.empname = t.empname
WHERE e.empid > @EmployeeID

-- Show the inserted records
SELECT *
FROM @Employees
WHERE empid > @employeeid

SELECT *
FROM @EmpStateCity
WHERE escid > @StateCityID[/code]

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 07:38:20

thank you..i will try it out..

lols
Posting Yak Master

174 Posts

Posted - 2007-12-12 : 07:45:15

Any idea of how to execute these ?
4. If the st_cd and city_cd in the temp table does not match the st_cd and city_cd in StateCity, then those records must be stored in a temp table on the fly.
5. All state and city listed in StateCity table must have employees. So in case there is any employee not assigned to a particular state city, those state and city need to be stored in another temp table while transferring.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-12 : 07:51:51

Using same technique as decribed for @Employees table.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources