Copy existing row data into new table rows

Question

Post reply

Copy existing row data into new table rows

barbersl

Grasshopper

Points: 18
More actions
August 28, 2015 at 7:21 am

#300146

Let me rephrase this problem. I have an existing table (Documents), that stores workflow stuff, like several people's user IDs, and approval dates. I'm trying to copy all the user IDs and approval dates into a new table, with each person getting their own row in the table, by DocumentID. I was able to come up with some script that shows the data in the Documents table (which is a good representation of how our data looks) and what I am trying to accomplish in the new Workflow table. The NULL values are OK. I'm using table variables here to test, but at work the tables will be real tables. I'm not sure if this is a good approach or not; will it work?
-- Sample existing table
declare @documents table (
DocumentID int,
WorkflowStatus nvarchar(50),
Status nvarchar(50),
ManagerID int NULL,
ManagerApprovedDate datetime NULL,
ClerkID int NULL,
ClerkApprovedDate datetime NULL,
FlunkyID int,
FlunkyApprovedDate datetime
)
insert into @documents values (1, 'Approved', 'Approved', 300, '7/3/2015', 400, '7/2/2015', 500, '7/1/2015')
insert into @documents values (2, 'Submitted', 'Submitted', NULL, NULL, NULL, NULL, 500, '6/30/2015')
insert into @documents values (3, 'ClerkAccepted', 'Pending', NULL, NULL, 400, '7/10/2015', 500, '7/9/2015')
select * from @documents
-- New table
DECLARE @Workflow TABLE(
DocumentID int,
StepName nvarchar(50),
UserID int NULL,
ApprovedDate datetime NULL,
Status nvarchar(50))
DECLARE @DocumentID int
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR
SELECT DocumentID FROM @documents
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @DocumentID WHILE @@FETCH_STATUS = 0
BEGIN
insert into @Workflow values(
(SELECT DocumentID from @documents WHERE DocumentID = @DocumentID),
'Submitted',
(SELECT FlunkyID from @documents WHERE DocumentID = @DocumentID),
(SELECT FlunkyApprovedDate from @documents WHERE DocumentID = @DocumentID),
'Submitted')
insert into @Workflow values(
(SELECT DocumentID from @documents WHERE DocumentID = @DocumentID),
'ClerkApproved',
(SELECT ClerkID from @documents WHERE DocumentID = @DocumentID),
(SELECT ClerkApprovedDate from @documents WHERE DocumentID = @DocumentID),
'SemiApproved')
insert into @Workflow values(
(SELECT DocumentID from @documents WHERE DocumentID = @DocumentID),
'ManagerApproved',
(SELECT ManagerID from @documents WHERE DocumentID = @DocumentID),
(SELECT ManagerApprovedDate from @documents WHERE DocumentID = @DocumentID),
'Approved')
FETCH NEXT FROM @MyCursor INTO @DocumentID
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
select DocumentID, StepName, UserID, ApprovedDate, Status from @Workflow
Original post:
--------------------------------------------------------------------------------------------
Hello, I'm an asp.net application developer trying to accomplish some database rework in our SQL Server 2008R2 database. I apologize upfront for not be able to present this better. I don't even know which technology to pursue; i.e., cursor? unpivot?
I'm trying to copy some column data from one existing table, into another new table as rows, by RecordID.
For example, let's say my existing table has the following columns, which would all be in one row in the table by RecordID (such as RecordID 1, RecordID2, etc.):
RecordID (int PK)
ManagerID (int)
ManagerApprovedDate (datetime)
ClerkID (int)
ClerkApprovedDate (datetime)
FlunkyID (int)
FlunkyApprovedDate (datetime)
I want to create a new table that will contain the user IDs and approved dates as separate rows, by RecordID, where there would be a row each for the manager, clerk, and flunky. For example, data would look like this:
RecordID UserID ApprovedDate StepName
1 ----------300 ----7/15/2015 --ManagerApproved
1 ----------301 ----7/16/2015 --ClerkApproved
1 ----------302 ----7/17/2015 --FlunkyApproved
I'm not sure what the best approach would be, given my inexperience with SQL. Thank you for any suggestions.
Sherry

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think it's just a select of three queries, which inserts into the new table.

INSERT INTO NEWTABLE(ColumnList)

SELECT RecordID,ManagerID,ManagerApprovedDate,'ManagerApproved'

From OriginalTable WHERE ManagerID IS NOT NULL UNION ALL

SELECT RecordID,ClerkID ,ClerkApprovedDate,'ClerkApproved'

From OriginalTable WHERE ClerkID IS NOT NULL UNION ALL

SELECT RecordID,FlunkyID,FlunkyApprovedDate,'FlunkyApproved'

From OriginalTable WHERE FlunkyID IS NOT NULL UNION ALL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

barbersl Grasshopper Points: 18 More actions · Answer 2

Hi Lowell,

Thank you for attempting to solve my gibberish. I worked on the UNION ALL solution you presented, but I just couldn't get it to do what I wanted; probably because I didn't do a very good job initially explaining what I need. I will edit my original post with some better data for you.

Sherry