July 5, 2018 at 12:47 am
Hi Experts,
Need some TSQL help.
We have guids in our env and want to get rid of it down the lane. For simplicity sake, I am taking below example for 2 tables which has parent - child table relationship.
DEPT is parent table.
EMP is a child table.
Requirement :
DEPT - added a new column DEPTNO of interger data type. We need to generate a new value for the existing rows and the same has to updated in DEPTNO column of EMP table.
EMP table - added a new column EMPNO of interger data type. Need to generate new values for existing data
Once we have the data integrity setup, want to drop the guid columns. Finally we can add not null constraints on EMP(deptno),
create the primary key constraint on DEPT(deptno) and EMP(Empno);
Sample data
CREATE TABLE [dbo].[DEPT]
(
DNO UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[DEPTNO] [int] ,
[DNAME] [varchar](14) NULL,
[LOC] [varchar](13) NULL
)
CREATE TABLE [dbo].[EMP](
EmployeeNo UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[EMPNO] [int],
[ENAME] [varchar](10) NULL,
[JOB] [varchar](9) NULL,
[MGR] [int] NULL,
[HIREDATE] [datetime] NULL,
[SAL] [numeric](7, 2) NULL,
[COMM] [numeric](7, 2) NULL,
[DEPTNO] [int] NULL,
DNO UNIQUEIDENTIFIER NOT NULL
)
ALTER TABLE [dbo].[EMP] WITH CHECK ADD CONSTRAINT [FK_DEPTNO] FOREIGN KEY([DNO])
REFERENCES [dbo].[DEPT] ([DNO])
GO
GO
INSERT [dbo].[DEPT] ([DNO], [DNAME], [LOC]) VALUES ('1F86DB32-E913-4194-A2A8-6FC9153F4E5B', N'ACCOUNTING', N'NEW YORK')
GO
INSERT [dbo].[DEPT] ([DNO], [DNAME], [LOC]) VALUES ('BB4BC9BC-9707-471F-B033-939B27FAACD3', N'RESEARCH', N'DALLAS')
GO
INSERT [dbo].[DEPT] ([DNO], [DNAME], [LOC]) VALUES ('0A6B4C8D-C01E-414F-AB9C-EFDE46B79FDF', N'SALES', N'CHICAGO')
GO
INSERT [dbo].[DEPT] ([DNO], [DNAME], [LOC]) VALUES ('3C3E5524-31AF-4643-93BA-F9EB4322F497', N'OPERATIONS', N'BOSTON')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'SMITH', N'CLERK', 7902, CAST(N'1980-12-17T00:00:00.000' AS DateTime), CAST(800.00 AS Numeric(7, 2)), NULL, '3C3E5524-31AF-4643-93BA-F9EB4322F497')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'ALLEN', N'SALESMAN', 7698, CAST(N'1981-02-20T00:00:00.000' AS DateTime), CAST(1600.00 AS Numeric(7, 2)), CAST(300.00 AS Numeric(7, 2)), '3C3E5524-31AF-4643-93BA-F9EB4322F497')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'WARD', N'SALESMAN', 7698, CAST(N'1981-02-22T00:00:00.000' AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(500.00 AS Numeric(7, 2)), 'BB4BC9BC-9707-471F-B033-939B27FAACD3')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'JONES', N'MANAGER', 7839, CAST(N'1981-04-02T00:00:00.000' AS DateTime), CAST(2975.00 AS Numeric(7, 2)), NULL, 'BB4BC9BC-9707-471F-B033-939B27FAACD3')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'MARTIN', N'SALESMAN', 7698, CAST(N'1981-09-28T00:00:00.000' AS DateTime), CAST(1250.00 AS Numeric(7, 2)), CAST(1400.00 AS Numeric(7, 2)), '1F86DB32-E913-4194-A2A8-6FC9153F4E5B')
GO
INSERT [dbo].[EMP] (EmployeeNo, [ENAME], [JOB], [MGR], [HIREDATE], [SAL], [COMM], [DNO]) VALUES (NEWID(), N'BLAKE', N'MANAGER', 7839, CAST(N'1981-05-01T00:00:00.000' AS DateTime), CAST(2850.00 AS Numeric(7, 2)), NULL, 'BB4BC9BC-9707-471F-B033-939B27FAACD3')
GO
SELECT * FROM DEPT;
GO
SELECT * FROM EMP;
GO
Can one provide a proper solution of handling this scenario.?
Thanks,
Sam
July 5, 2018 at 2:13 am
Thom A - Thursday, July 5, 2018 12:52 AMWhat have you tried so far to fix the problem yourself? If you post that, we can help you with the errors you received and what steps to take next/instead.
I don't have a solution with me as such. however, thinking to use object like sequence and use an update stmt,
July 5, 2018 at 2:38 am
This should work, provided you make the new int columns not nullable. I recommend you do this at a time when there's no activity on the tables in question.
WITH NewNumbers AS (
SELECT
DNO
, DEPTNO
, ROW_NUMBER() OVER (ORDER BY DNO) AS NewNumber -- order by anything you like here
FROM DEPT
)
UPDATE NewNumbers
SET DEPTNO = NewNumber;
WITH NewNumbers AS (
SELECT
EmployeeNo
, EMPNO
, ROW_NUMBER() OVER (ORDER BY DNO) AS NewNumber -- order by anything you like here
FROM EMP
)
UPDATE NewNumbers
SET EMPNO = NewNumber;
WITH NewFKNumbers AS (
SELECT
d.DEPTNO AS Parent
, e.DEPTNO AS Child
FROM DEPT d
JOIN EMP e ON d.DNO = e.DNO
)
UPDATE NewFKNumbers
SET Child = Parent;
SELECT * FROM DEPT;
SELECT * FROM EMP;
ALTER TABLE EMP
DROP CONSTRAINT FK_DEPTNO;
ALTER TABLE DEPT
DROP CONSTRAINT PK__DEPT__C035B8C200B5014B; -- your constraint will have a different name from this
ALTER TABLE DEPT
DROP CONSTRAINT PK__EMP__7AD0F1B6CB7A1C9B; -- your constraint will have a different name from this
ALTER TABLE DEPT
ADD CONSTRAINT PK_DEPT_DEPTNO
PRIMARY KEY CLUSTERED (DEPTNO); -- clustered may or may not be the best choice here
ALTER TABLE EMP
ADD CONSTRAINT PK_EMP_EMPNO
PRIMARY KEY CLUSTERED (EMPNO); -- clustered may or may not be the best choice here
ALTER TABLE EMP WITH CHECK
ADD CONSTRAINT FK_EMP_DEPT_DEPTNO
FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO);
July 5, 2018 at 2:50 am
Thanks John it worked fine. it was throwing some constraint errors. I can fix it.
July 5, 2018 at 3:02 am
vsamantha35 - Thursday, July 5, 2018 2:13 AMThom A - Thursday, July 5, 2018 12:52 AMWhat have you tried so far to fix the problem yourself? If you post that, we can help you with the errors you received and what steps to take next/instead.I don't have a solution with me as such. however, thinking to use object like sequence and use an update stmt,
Exactly.
1. Add a column DEPTNO with IDENTITY property in DEPT table
2. Add columns EMPNO with IDENTITY property, DEPTNO in EMP table
3. Perform an update in EMP table by doing a JOIN on DNO
4. Drop the columns DNO, ENO from the tables
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply