| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2002-05-20 : 12:25:34
|
| So I want to take the old data and import it into a much better schema. In this post I will display the create tables and some sample data inserts. I will post a reply to this with an explanation since I hope this post is not beyond 4000 characters. CREATE TABLE tworkflow ( ckworkflow int IDENTITY (1,1) NOT NULL PRIMARY KEY, ckproject int NULL, caction varchar(256) NULL, ckstate int NULL, cdtreceived datetime NULL, ccomments varchar(8000) NULL, cdtprocessed datetime NULL, cdtupdate datetime NULL DEFAULT (getdate()), clstatus int NULL, cfirststate bit NOT NULL DEFAULT (0), ccurrentstate bit NOT NULL DEFAULT (0), cdefault bit NOT NULL DEFAULT (0), cupdateby varchar (100) NULL DEFAULT (user_name()), cdtnotified datetime NULL DEFAULT (getdate()), cactor varchar (256) NULL DEFAULT (user_name()), cdtcreate datetime NULL DEFAULT (getdate()), ccreateby varchar (100) NULL DEFAULT (user_name()), cprocessed bit NOT NULL DEFAULT (0), corder int NULL DEFAULT (0), clevel int NULL DEFAULT (0), citem int NULL DEFAULT (0), cknextstate int NULL, ckprevstate int NULL, claststate bit NOT NULL DEFAULT (0),)GO SET NOCOUNT ONGO INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 1, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 1, 0, 1, 'dbo', NULL, 'SAPEZZULO', 'Nov 19 2001 3:21PM', 'dbo', 0, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 2, NULL, NULL, NULL, 'Nov 19 2001 3:36PM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'RGVANDERHORST', 'Nov 19 2001 3:36PM', 'AXDIAZ', 0, 2, 1, 1, 3, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 3, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 0, 0, 1, 'dbo', NULL, 'AXDIAZ', 'Nov 19 2001 3:21PM', 'dbo', 0, 3, 2, 1, 4, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 4, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 0, 0, 1, 'dbo', NULL, 'MJGARDNER', 'Nov 19 2001 3:21PM', 'dbo', 0, 4, 3, 1, 5, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 5, NULL, NULL, NULL, 'May 17 2002 10:32AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'DRPETERS', 'May 17 2002 10:32AM', 'AXDIAZ', 0, 5, 4, 1, 6, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 6, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 0, 0, 1, 'dbo', NULL, 'DWREEVE', 'Nov 19 2001 3:21PM', 'dbo', 0, 6, 5, 1, 9, 5, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 6, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 0, 0, 1, 'dbo', NULL, 'WHDONNER', 'Nov 19 2001 3:21PM', 'dbo', 0, 7, 5, 2, 9, 5, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 9, NULL, NULL, NULL, 'Nov 19 2001 3:21PM', NULL, 0, 0, 1, 'dbo', NULL, 'AXDIAZ', 'Nov 19 2001 3:21PM', 'dbo', 0, 8, 6, 1, NULL, 6, 1)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, NULL, 5, 'May 17 2002 10:32AM', NULL, NULL, 'May 17 2002 10:32AM', 57, 0, 1, 0, 'AXDIAZ', 'May 17 2002 10:32AM', 'DRPETERS', 'May 17 2002 10:32AM', 'AXDIAZ', 0, 5, 4, 1, 6, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, 'Approve', 1, 'Nov 19 2001 3:21PM', 'These would be comments', 'Nov 19 2001 3:24PM', 'Nov 19 2001 3:24PM', 58, 0, 0, 0, 'SAPEZZULO', 'Nov 19 2001 3:24PM', 'SAPEZZULO', 'Nov 19 2001 3:21PM', 'dbo', 1, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, 'Approve', 2, 'Nov 19 2001 3:36PM', 'These would be comments', 'Nov 20 2001 8:27AM', 'Nov 20 2001 8:27AM', 58, 0, 0, 0, 'RGVANDERHORST', 'Nov 20 2001 8:27AM', 'RGVANDERHORST', 'Nov 19 2001 3:36PM', 'AXDIAZ', 1, 2, 1, 1, 3, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, 'Request More Info', 3, 'Nov 20 2001 8:27AM', 'These would be comments', 'Nov 23 2001 11:22AM', 'Nov 23 2001 11:22AM', 58, 0, 0, 0, 'AXDIAZ', 'Nov 23 2001 11:22AM', 'AXDIAZ', 'Nov 19 2001 3:21PM', 'dbo', 1, 3, 2, 1, 4, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37000, 'Approve', 4, 'May 16 2002 3:40PM', 'These would be comments', 'May 16 2002 3:43PM', 'May 16 2002 3:43PM', 58, 0, 0, 0, 'MJGARDNER', 'May 16 2002 3:43PM', 'MJGARDNER', 'Nov 19 2001 3:21PM', 'dbo', 1, 4, 3, 1, 5, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 1, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 1, 0, 1, 'dbo', NULL, 'SAPEZZULO', 'Nov 19 2001 4:29PM', 'dbo', 0, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 2, NULL, NULL, NULL, 'Nov 19 2001 4:34PM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'RGVANDERHORST', 'Nov 19 2001 4:34PM', 'AXDIAZ', 0, 2, 1, 1, 3, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 3, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 0, 0, 1, 'dbo', NULL, 'AXDIAZ', 'Nov 19 2001 4:29PM', 'dbo', 0, 3, 2, 1, 4, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 4, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 0, 0, 1, 'dbo', NULL, 'MJGARDNER', 'Nov 19 2001 4:29PM', 'dbo', 0, 4, 3, 1, 5, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 5, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 0, 0, 1, 'dbo', NULL, 'DWREEVE', 'Nov 19 2001 4:29PM', 'dbo', 0, 5, 4, 1, 8, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 5, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 0, 0, 1, 'dbo', NULL, 'WHDONNER', 'Nov 19 2001 4:29PM', 'dbo', 0, 6, 4, 2, 8, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 8, NULL, NULL, NULL, 'Nov 19 2001 4:29PM', NULL, 0, 0, 1, 'dbo', NULL, 'AXDIAZ', 'Nov 19 2001 4:29PM', 'dbo', 0, 7, 5, 1, 9, 5, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 9, NULL, NULL, NULL, 'May 20 2002 10:34AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'RGVANDERHORST', 'May 20 2002 10:34AM', 'AXDIAZ', 0, 8, 6, 1, 10, 8, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 10, NULL, NULL, NULL, 'May 20 2002 10:33AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'DRPETERS', 'May 20 2002 10:33AM', 'AXDIAZ', 0, 9, 7, 1, 11, 9, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 11, NULL, NULL, NULL, 'May 20 2002 10:35AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'MSBARGMANN', 'May 20 2002 10:35AM', 'AXDIAZ', 0, 10, 8, 1, 12, 10, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 12, NULL, NULL, NULL, 'May 20 2002 10:35AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'WHDONNER', 'May 20 2002 10:35AM', 'AXDIAZ', 0, 11, 9, 1, 13, 11, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 13, NULL, NULL, NULL, 'May 20 2002 10:43AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'DWREEVE', 'May 20 2002 10:43AM', 'AXDIAZ', 0, 12, 10, 1, 14, 12, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 14, NULL, NULL, NULL, 'May 20 2002 10:44AM', 57, 0, 0, 1, 'AXDIAZ', NULL, 'DRPETERS', 'May 20 2002 10:44AM', 'AXDIAZ', 0, 13, 11, 1, NULL, 13, 1)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, NULL, 9, 'May 20 2002 10:58AM', NULL, NULL, 'May 20 2002 10:58AM', 57, 0, 1, 0, 'AXDIAZ', 'May 20 2002 10:58AM', 'RGVANDERHORST', 'May 20 2002 10:34AM', 'AXDIAZ', 0, 8, 6, 1, 10, 8, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 1, 'Nov 19 2001 4:29PM', 'These would be comments', 'Nov 19 2001 4:33PM', 'Nov 19 2001 4:33PM', 58, 0, 0, 0, 'SAPEZZULO', 'Nov 19 2001 4:33PM', 'SAPEZZULO', 'Nov 19 2001 4:29PM', 'dbo', 1, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 2, 'Nov 19 2001 4:34PM', 'These would be comments', 'Nov 20 2001 8:28AM', 'Nov 20 2001 8:28AM', 58, 0, 0, 0, 'RGVANDERHORST', 'Nov 20 2001 8:28AM', 'RGVANDERHORST', 'Nov 19 2001 4:34PM', 'AXDIAZ', 1, 2, 1, 1, 3, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Request More Info', 3, 'Nov 20 2001 8:28AM', 'These would be comments', 'Nov 23 2001 11:26AM', 'Nov 23 2001 11:26AM', 58, 0, 0, 0, 'AXDIAZ', 'Nov 23 2001 11:26AM', 'AXDIAZ', 'Nov 19 2001 4:29PM', 'dbo', 1, 3, 2, 1, 4, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 4, 'May 16 2002 3:40PM', 'These would be comments', 'May 16 2002 3:44PM', 'May 16 2002 3:44PM', 58, 0, 0, 0, 'MJGARDNER', 'May 16 2002 3:44PM', 'MJGARDNER', 'Nov 19 2001 4:29PM', 'dbo', 1, 4, 3, 1, 5, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 5, 'May 17 2002 9:57AM', 'These would be comments', 'May 17 2002 4:40PM', 'May 17 2002 4:40PM', 58, 0, 0, 0, 'DWREEVE', 'May 17 2002 4:40PM', 'DWREEVE', 'Nov 19 2001 4:29PM', 'dbo', 1, 5, 4, 1, 8, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 5, 'May 17 2002 9:57AM', 'These would be comments', 'May 19 2002 4:40PM', 'May 19 2002 4:40PM', 58, 0, 0, 0, 'WHDONNER', 'May 19 2002 4:40PM', 'WHDONNER', 'Nov 19 2001 4:29PM', 'dbo', 1, 6, 4, 2, 8, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (37003, 'Approve', 8, 'May 19 2002 4:40PM', 'These would be comments', 'May 20 2002 10:58AM', 'May 20 2002 10:58AM', 58, 0, 0, 0, 'AXDIAZ', 'May 20 2002 10:58AM', 'AXDIAZ', 'Nov 19 2001 4:29PM', 'dbo', 1, 7, 5, 1, 9, 5, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, NULL, 1, NULL, NULL, NULL, 'Apr 9 2002 11:02AM', NULL, 1, 0, 1, 'dbo', NULL, 'TMMITTLER', 'Apr 9 2002 11:02AM', 'dbo', 0, 1, 0, 1, 3, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, NULL, 3, NULL, NULL, NULL, 'Apr 9 2002 11:04AM', 57, 0, 0, 1, 'TMMITTLER', NULL, 'CNREDDER', 'Apr 9 2002 11:04AM', 'TMMITTLER', 0, 2, 1, 1, 4, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, NULL, 4, NULL, NULL, NULL, 'Apr 9 2002 11:04AM', 57, 0, 0, 1, 'TMMITTLER', NULL, 'TROSINSKI', 'Apr 9 2002 11:04AM', 'TMMITTLER', 0, 3, 2, 1, 5, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, NULL, 5, NULL, NULL, NULL, 'Apr 9 2002 11:02AM', NULL, 0, 0, 1, 'dbo', NULL, 'LJDAMORE', 'Apr 9 2002 11:02AM', 'dbo', 0, 4, 3, 1, NULL, 4, 1)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, NULL, 1, 'May 17 2002 9:57AM', NULL, NULL, 'Apr 9 2002 11:02AM', 57, 1, 1, 0, 'dbo', 'May 17 2002 9:57AM', 'TMMITTLER', 'Apr 9 2002 11:02AM', 'dbo', 0, 1, 0, 1, 4, 4, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, 'Approve', 1, 'Apr 9 2002 11:02AM', 'These would be comments', 'Apr 9 2002 11:44AM', 'Apr 9 2002 11:44AM', 58, 0, 0, 0, 'TMMITTLER', 'Apr 9 2002 11:44AM', 'TMMITTLER', 'Apr 9 2002 11:02AM', 'dbo', 1, 1, 0, 1, 3, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, 'Approve', 3, 'Apr 9 2002 11:44AM', 'These would be comments', 'Apr 9 2002 12:11PM', 'Apr 9 2002 12:11PM', 58, 0, 0, 0, 'CNREDDER', 'Apr 9 2002 12:11PM', 'CNREDDER', 'Apr 9 2002 11:04AM', 'TMMITTLER', 1, 2, 1, 1, 4, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39436, 'Request More Info', 4, 'Apr 9 2002 12:11PM', 'These would be comments', 'Apr 9 2002 4:14PM', 'Apr 9 2002 4:14PM', 58, 0, 0, 0, 'TROSINSKI', 'Apr 9 2002 4:14PM', 'TROSINSKI', 'Apr 9 2002 11:04AM', 'TMMITTLER', 1, 3, 2, 1, 1, 3, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, NULL, 1, NULL, NULL, NULL, 'Apr 12 2002 7:56AM', NULL, 1, 0, 1, 'dbo', NULL, 'AJPISA', 'Apr 12 2002 7:56AM', 'dbo', 0, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, NULL, 2, NULL, NULL, NULL, 'Apr 12 2002 7:56AM', NULL, 0, 0, 1, 'dbo', NULL, 'WXGUDSELAK', 'Apr 12 2002 7:56AM', 'dbo', 0, 2, 1, 1, 4, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, NULL, 4, NULL, NULL, NULL, 'Apr 12 2002 7:58AM', 57, 0, 0, 1, 'AJPISA', NULL, 'MEKITTRELL', 'Apr 12 2002 7:58AM', 'AJPISA', 0, 3, 2, 1, 5, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, NULL, 5, NULL, NULL, NULL, 'Apr 16 2002 12:10PM', 57, 0, 0, 1, 'MEKITTRELL', NULL, 'JALINGL', 'Apr 16 2002 12:10PM', 'MEKITTRELL', 0, 4, 3, 1, NULL, 4, 1)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, NULL, 4, 'May 17 2002 10:43AM', NULL, NULL, 'May 17 2002 10:43AM', 57, 0, 1, 0, 'WXGUDSELAK', 'May 17 2002 10:43AM', 'MEKITTRELL', 'Apr 12 2002 7:58AM', 'AJPISA', 0, 3, 2, 1, 5, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, 'Approve', 1, 'Apr 12 2002 7:56AM', 'These would be comments', 'Apr 12 2002 7:58AM', 'Apr 12 2002 7:58AM', 58, 0, 0, 0, 'AJPISA', 'Apr 12 2002 7:58AM', 'AJPISA', 'Apr 12 2002 7:56AM', 'dbo', 1, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, 'Approve', 2, 'Apr 12 2002 7:59AM', 'These would be comments', 'Apr 12 2002 1:58PM', 'Apr 12 2002 1:58PM', 58, 0, 0, 0, 'WXGUDSELAK', 'Apr 12 2002 1:58PM', 'WXGUDSELAK', 'Apr 12 2002 7:56AM', 'dbo', 1, 2, 1, 1, 4, 1, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, 'Request More Info', 4, 'Apr 12 2002 1:58PM', 'These would be comments', 'Apr 16 2002 12:10PM', 'Apr 16 2002 12:10PM', 58, 0, 0, 0, 'MEKITTRELL', 'Apr 16 2002 12:10PM', 'MEKITTRELL', 'Apr 12 2002 7:58AM', 'AJPISA', 1, 3, 2, 1, 1, 2, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, 'Approve', 1, 'May 17 2002 9:57AM', 'These would be comments', 'May 17 2002 10:39AM', 'May 17 2002 10:39AM', 58, 0, 0, 0, 'AJPISA', 'May 17 2002 10:39AM', 'AJPISA', 'Apr 12 2002 7:56AM', 'dbo', 1, 1, 0, 1, 2, NULL, 0)INSERT INTO tworkflow (ckproject, ckaction, ckstate, cdtreceived, ccomments, cdtprocessed, cdtupdate, clstatus, cfirststate, ccurrentstate, cdefault, cupdateby, cdtnotified, cactor, cdtcreate, ccreateby, cprocessed, corder, clevel, citem, cknextstate, ckprevstate, claststate) VALUES (39520, 'Approve', 2, 'May 17 2002 10:39AM', 'These would be comments', 'May 17 2002 10:43AM', 'May 17 2002 10:43AM', 58, 0, 0, 0, 'WXGUDSELAK', 'May 17 2002 10:43AM', 'WXGUDSELAK', 'Apr 12 2002 7:56AM', 'dbo', 1, 2, 1, 1, 4, 1, 0)GO CREATE TABLE ProjectApprovals ( ProjectID INT NOT NULL, UserID INT NOT NULL, SequenceID INT NOT NULL, ActionID INT NULL, Comment VARCHAR(4096) NULL, DateCreated DATETIME NOT NULL, DateNotified DATETIME NULL, DateProcessed DATETIME NULL, PRIMARY KEY CLUSTERED (ProjectID, UserID, SequenceID))GOEdited by - onamuji on 05/20/2002 14:43:02 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-05-20 : 12:37:27
|
| The first set is the old table with a few sample projects ... if you look at the table at the bottom... that's what i want to get it into. ok for an explanation... The first table used to combine what we call default and actual workflow. The default being what the user wants to happen and the actual being what does happen. There are three possible actions a user can take on an actual workflow (APPROVE, REJECT, REQUEST MORE INFO [RQM]) when approved it moves to the next state, when rejected it stops, when RQM is selected it goes back to the first person in the default workflow ... that user can then REJECT OR APPROVE it .. if it's approved it goes back to the user who RQM'd. This makes working with the workflow very complicated and it $@*% up all the time (bad code and again it's hard to work with) The new schema I have designed combined both default and actual workflow. Management is ok with this concept (they will do anything to make it work better). The default workflow in my new schema is where the action has not been taken yet. Once an action is taking it moves to the next person and inserts or removes as needed. How I was planning on getting this data into the new schema was simple, i was hoping there was a unique constraint like my natural primary key, which is conformed to how business logic is. There can only be one user per project and step. So that a user cannot appear in the same step twice. Unfortunately the old schema does not do this. It uses the cknextstate to move through the workflow. When RQM is selected the state becomes 1 again. thus there are two entries for step 1. I tried using a ranking-type query with no luck since i need to import in 3 specific chunks. Importing completed workflow. This is easy the project is appaorved and i can use the ranking solution to pull unique sequence id's from the table. Importing for rejected workflow. I need to import all actual workflow up to the rejected point, then find out what the default workflow looks like for the next step (after the rejected step, this is for a new featuere of unrejecting a project and continuing it through the workflow) (this requires 2 steps) Importing the proposed workflow, this contains actual workflow up to the last step plus any remaining default workflow. The latter two are the most trouble some. Any help you guys can give me would be great. Also you have to account for sometimes the cknextstate value is null but shouldn't be... this application is seriously $%#) up.... i didn't do it ... ha ha... Oh i tried using a looped structure to handle all the catches and such but it took nearly 10+ seconds per project to import the workflow. Didn't use cursors either ... it just took forever ... and there are nearly 6000 projects i neeed to import... So I hope this is enough info and data for you guys to play with. I know you guys rock and will be able to help me through this ... thanks! |
 |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-05-21 : 08:38:40
|
Here's a view that may help see what I'm trying to do... This would work except now for the RQM and workflows that are not complete I need to also UNION this to the remainder of the workflow... NextStep is the key for each project!-- For actual workflowSELECT wf.ckproject AS ProjectID, wf.cactor AS UserName, (SELECT COUNT(i.ckstate) + 1 FROM tworkflow AS i WHERE i.ckproject = wf.ckproject AND i.cdefault = wf.cdefault AND i.cdtnotified < wf.cdtnotified) AS SequenceID, wf.caction AS Action, wf.ccomments AS Comments, wf.cdtcreate AS DateCreated, wf.cdtnotified AS DateNotified, wf.cdtprocessed AS DateProcessed, wf.cknextstate AS NextStep FROM tworkflow AS wf WHERE wf.ckproject IN (37000, 37003, 39436, 39520) AND wf.cdefault = 0 ORDER BY wf.ckproject, wf.cdtnotified, wf.ckstate -- For default workflowSELECT wf.ckproject AS ProjectID, wf.cactor AS UserName, (SELECT COUNT(i.ckstate) + 1 FROM tworkflow AS i WHERE i.ckproject = wf.ckproject AND i.cdefault = wf.cdefault AND i.ckstate < wf.ckstate) AS SequenceID, wf.caction AS Action, wf.ccomments AS Comments, wf.cdtcreate AS DateCreated, wf.cdtnotified AS DateNotified, wf.cdtprocessed AS DateProcessed, wf.cknextstate AS NextStep FROM tworkflow AS wf WHERE wf.ckproject IN (37000, 37003, 39436, 39520) AND wf.cdefault = 1 ORDER BY wf.ckproject, wf.cdtnotified, wf.ckstate Edited by - onamuji on 05/21/2002 08:44:27 |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-05-21 : 08:57:02
|
| Here's another view I worked up to find the default workflow for each project ... it appear right but wow can it look anymore complicated? Please help me simplify if possible :-) SELECT wf.ckproject AS ProjectID, wf.cactor AS UserName, wf.ckstate - i.NextStep + i.SequenceID + 1 AS SequenceID, NULL AS Action, NULL AS Comments, wf.cdtcreate AS DateCreated, NULL AS DateNotified, NULL AS DateProcessed FROM (SELECT i.ProjectID, i.NextStep, i.SequenceID FROM (SELECT wf.ckproject AS ProjectID, (SELECT COUNT(i.ckstate) + 1 FROM tworkflow AS i WHERE i.ckproject = wf.ckproject AND i.cdefault = wf.cdefault AND i.cdtnotified < wf.cdtnotified) AS SequenceID, wf.cknextstate AS NextStep FROM tworkflow AS wf WHERE wf.ckproject IN (37000, 37003, 39436, 39520) AND wf.cdefault = 0) AS i INNER JOIN (SELECT i.ProjectID, MAX(i.SequenceID) AS SequenceID FROM (SELECT wf.ckproject AS ProjectID, (SELECT COUNT(i.ckstate) + 1 FROM tworkflow AS i WHERE i.ckproject = wf.ckproject AND i.cdefault = wf.cdefault AND i.cdtnotified < wf.cdtnotified) AS SequenceID, wf.cknextstate AS NextStep FROM tworkflow AS wf WHERE wf.ckproject IN (37000, 37003, 39436, 39520) AND wf.cdefault = 0) AS i GROUP BY i.ProjectID) AS d ON i.ProjectID = d.ProjectID AND i.SequenceID = d.SequenceID) AS i INNER JOIN tworkflow AS wf ON i.ProjectID = wf.ckproject WHERE wf.cdefault = 1 AND wf.ckstate >= i.NextStep |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-05-21 : 11:32:01
|
| So I guess that last query was the key ... I got it to import ... thought it was going to take forever when it was running for 10+ minutes on the server just to import it into a table on the same database... I then went to my other machine did an OPENROWSET and imported the ENTIRE tworkflow table into it. Then the two queries inserted into that server in less than 30 seconds ... the only difference is the original server is SQL7 and the one that took less than 30 seconds is SQL2k ... however the SQL7 is a quad xeon 450 w/512MB and the SQL2k is a dual 200mhz pentium pro w/162MB ... any idea why the huge difference is it because there were no index on the tworkflow table in the SQL2k envrionment since I did a SELECT * INTO ?? |
|
|
|
|
|
|
Complicated Data Transformation