August 4, 2015 at 9:44 am
Hello,
I need some help with this. I have a table where I would like to update the document number row for 3k rows. The problem I have is that the documents come in sets of two (version 1 and 2) but both have different numbers. Picture it like this below:
DOCNUM: 4445787 Version 1
DOCNUM: 4445790 Version 2
It should be the same docnum (ie 4445787 Version 1, 4445787 Version 2).
The challenge is how can we assign the new docnum for version 1 to be also for version 2 as well. Basically in SQL we need a way to
1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.
2. Update them so that the docnums match.
What can I extract from the attached results to help me with this and how should I approach it.
August 4, 2015 at 9:52 am
Hi welcome to the forums.
What you explain might be possible, but a simple pdf with truncated rows won't help. We need DDL, sample data as insert statements and the expected results for that sample data. We also need the rules to get the expected results.
naydenov.stoqn (8/4/2015)
1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.
We can help you to distinguish this using T-SQL, but you need to define what makes them the same. The name? Something else?
Read this article[/url] or the one posted in my signature to know what we need to help.
August 4, 2015 at 10:03 am
Hi Luis,
Thanks for the tips on posting, here is the converted csv file into an INSERT statement
DROP TABLE mytable;
CREATE TABLE mytable(
FIELD1 VARCHAR(840) NOT NULL PRIMARY KEY
);
INSERT INTO mytable(FIELD1) VALUES ('ID,PID,SrcDB,TreeId,TargetDB,PrjId,PrjPId,DOCNAME,DOCNUM,DOCSIZE,EDITWHEN,DOCINUSE,ENTRYWHEN,EDITPROFILEWHEN,INDEXED,VERSION,DOCLOC,AUTHOR,OPERATOR,ALIAS,CHECKEDOUT,COMMENTS,COMINDEX,ARCHIVE_REQ,T_ALIAS,C_ALIAS,SUBCLASS_ALIAS,ARCHIVED,INUSEBY,INDEXABLE,ISRELATED,C1ALIAS,C2ALIAS,C3ALIAS,C4ALIAS,C5ALIAS,C6ALIAS,C7ALIAS,C8ALIAS,C9ALIAS,C10ALIAS,C11ALIAS,C12ALIAS,C13ALIAS,C14ALIAS,C15ALIAS,C16ALIAS,C29ALIAS,C30ALIAS,C31ALIAS,CDBL1,CDBL2,CDBL3,CDBL4,CBOOL1,CBOOL2,CBOOL3,CBOOL4,CDATE1,CDATE2,CDATE3,CDATE4,DECLAREWHEN,PENDING_CLOSE,PENDING_IRM,IRM,ARCHV_ID,LASTUSER,RETAIN,LOGNODEADDR,DEFAULT_SECURITY,IS_SECURED,TYPE,MSG_ID,IS_EXTERNAL,HAS_ATTACHMENT,EXTRNL_AS_NRML,FILEENTRYWHEN,FILEEDITWHEN,Status,Deleted,Msg,Error,TargetDocNum,TargetVersion,TargetPrjPId,TargetPrjId,Ctr,LastEditDate,InsertedBy,InsertedDate,OldServer,NewServer,IsMoveJob');
INSERT INTO mytable(FIELD1) VALUES ('152,85,Active,0,Archive,NULL,NULL,Zone for G+T script,33238,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33238.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33238,1,NULL,12,NULL,NULL,NULL,2015-07-30 16:58:00,MILANDEMO,MILANDEMO,1');
INSERT INTO mytable(FIELD1) VALUES ('153,86,Archive,0,Active,NULL,NULL,Zone for G+T script,33238,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\ARCHIVE\MILANADM\11\1\33238.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33240,1,NULL,9646,NULL,NULL,NULL,2015-07-30 17:00:00,MILANDEMO,MILANDEMO,1');
INSERT INTO mytable(FIELD1) VALUES ('154,87,Active,0,Archive,NULL,NULL,Zone for G+T script,33240,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33240.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33239,1,NULL,12,NULL,NULL,NULL,2015-07-30 17:03:00,MILANDEMO,MILANDEMO,1');
INSERT INTO mytable(FIELD1) VALUES ('155,87,Active,0,Archive,NULL,NULL,Zone for G+T script,33240,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,2,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\2\33240.2,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33239,2,NULL,12,NULL,NULL,NULL,2015-07-30 17:03:00,MILANDEMO,MILANDEMO,1');
I am not sure about the rules so don't know what to post in regards to that. What makes them really the same (and you can't see it in the sample of data) but there is also a COMMENTS column which contains data like this ""oldnumber:545454", "new number:548777""
Let me know if there is anything else needed, thanks again.
August 4, 2015 at 12:22 pm
You almost got it, but we need the real structure instead of rows of delimited strings. I guess you forgot to click on the "Convert CSV to SQL Insert" button.
It should be something like this:
CREATE TABLE mytable(
ID INT PRIMARY KEY
,PID INT
,SrcDB VARCHAR(7)
,TreeId BIT
,TargetDB VARCHAR(7)
,PrjId VARCHAR(4)
,PrjPId VARCHAR(4)
,DOCNAME VARCHAR(19)
,DOCNUM INT
,DOCSIZE INT
,EDITWHEN VARCHAR(23)
,DOCINUSE VARCHAR(4)
,ENTRYWHEN VARCHAR(23)
,EDITPROFILEWHEN VARCHAR(23)
,INDEXED VARCHAR(4)
,VERSION INT
,DOCLOC VARCHAR(49)
,AUTHOR VARCHAR(10)
,OPERATOR VARCHAR(10)
,ALIAS VARCHAR(4)
,CHECKEDOUT VARCHAR(4)
,COMMENTS VARCHAR(4)
,COMINDEX VARCHAR(4)
,ARCHIVE_REQ VARCHAR(4)
,T_ALIAS VARCHAR(5)
,C_ALIAS VARCHAR(3)
,SUBCLASS_ALIAS VARCHAR(4)
,ARCHIVED VARCHAR(4)
,INUSEBY VARCHAR(4)
,INDEXABLE VARCHAR(4)
,ISRELATED VARCHAR(4)
,C1ALIAS VARCHAR(4)
,C2ALIAS VARCHAR(4)
,C3ALIAS VARCHAR(4)
,C4ALIAS VARCHAR(4)
,C5ALIAS VARCHAR(4)
,C6ALIAS VARCHAR(4)
,C7ALIAS VARCHAR(4)
,C8ALIAS VARCHAR(4)
,C9ALIAS VARCHAR(4)
,C10ALIAS VARCHAR(4)
,C11ALIAS VARCHAR(4)
,C12ALIAS VARCHAR(4)
,C13ALIAS VARCHAR(4)
,C14ALIAS VARCHAR(4)
,C15ALIAS VARCHAR(4)
,C16ALIAS VARCHAR(4)
,C29ALIAS VARCHAR(4)
,C30ALIAS VARCHAR(4)
,C31ALIAS VARCHAR(4)
,CDBL1 VARCHAR(4)
,CDBL2 VARCHAR(4)
,CDBL3 VARCHAR(4)
,CDBL4 VARCHAR(4)
,CBOOL1 VARCHAR(4)
,CBOOL2 VARCHAR(4)
,CBOOL3 VARCHAR(4)
,CBOOL4 VARCHAR(4)
,CDATE1 VARCHAR(4)
,CDATE2 VARCHAR(4)
,CDATE3 VARCHAR(4)
,CDATE4 VARCHAR(4)
,DECLAREWHEN VARCHAR(4)
,PENDING_CLOSE VARCHAR(4)
,PENDING_IRM VARCHAR(4)
,IRM VARCHAR(4)
,ARCHV_ID VARCHAR(4)
,LASTUSER VARCHAR(4)
,RETAIN VARCHAR(4)
,LOGNODEADDR VARCHAR(4)
,DEFAULT_SECURITY VARCHAR(1)
,IS_SECURED VARCHAR(4)
,TYPE VARCHAR(4)
,MSG_ID VARCHAR(4)
,IS_EXTERNAL VARCHAR(4)
,HAS_ATTACHMENT VARCHAR(4)
,EXTRNL_AS_NRML VARCHAR(4)
,FILEENTRYWHEN VARCHAR(4)
,FILEEDITWHEN VARCHAR(4)
,Status INT
,Deleted VARCHAR(4)
,Msg VARCHAR(37)
,Error VARCHAR(4)
,TargetDocNum INT
,TargetVersion INT
,TargetPrjPId VARCHAR(4)
,TargetPrjId INT
,Ctr VARCHAR(4)
,LastEditDate VARCHAR(4)
,InsertedBy VARCHAR(4)
,InsertedDate VARCHAR(19)
,OldServer VARCHAR(9)
,NewServer VARCHAR(9)
,IsMoveJob BIT
);
INSERT INTO mytable(ID,PID,SrcDB,TreeId,TargetDB,PrjId,PrjPId,DOCNAME,DOCNUM,DOCSIZE,EDITWHEN,DOCINUSE,ENTRYWHEN,EDITPROFILEWHEN,INDEXED,VERSION,DOCLOC,AUTHOR,OPERATOR,ALIAS,CHECKEDOUT,COMMENTS,COMINDEX,ARCHIVE_REQ,T_ALIAS,C_ALIAS,SUBCLASS_ALIAS,ARCHIVED,INUSEBY,INDEXABLE,ISRELATED,C1ALIAS,C2ALIAS,C3ALIAS,C4ALIAS,C5ALIAS,C6ALIAS,C7ALIAS,C8ALIAS,C9ALIAS,C10ALIAS,C11ALIAS,C12ALIAS,C13ALIAS,C14ALIAS,C15ALIAS,C16ALIAS,C29ALIAS,C30ALIAS,C31ALIAS,CDBL1,CDBL2,CDBL3,CDBL4,CBOOL1,CBOOL2,CBOOL3,CBOOL4,CDATE1,CDATE2,CDATE3,CDATE4,DECLAREWHEN,PENDING_CLOSE,PENDING_IRM,IRM,ARCHV_ID,LASTUSER,RETAIN,LOGNODEADDR,DEFAULT_SECURITY,IS_SECURED,TYPE,MSG_ID,IS_EXTERNAL,HAS_ATTACHMENT,EXTRNL_AS_NRML,FILEENTRYWHEN,FILEEDITWHEN,Status,Deleted,Msg,Error,TargetDocNum,TargetVersion,TargetPrjPId,TargetPrjId,Ctr,LastEditDate,InsertedBy,InsertedDate,OldServer,NewServer,IsMoveJob)
VALUES (152,85,'Active',0,'Archive',NULL,NULL,'Zone for G+T script',33238,15721,'2015-04-06 00:00:00.000',NULL,'2015-07-23 16:55:33.000','2015-04-06 00:00:00.000',NULL,1,'DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33238.1','MILANADMIN','MILANADMIN',NULL,NULL,NULL,NULL,NULL,'WORDX','DOC',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'X',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,'Document "Zone for G+T script" moved.',NULL,33238,1,NULL,12,NULL,NULL,NULL,'2015-07-30 16:58:00','MILANDEMO','MILANDEMO',1)
,(153,86,'Archive',0,'Active',NULL,NULL,'Zone for G+T script',33238,15721,'2015-04-06 00:00:00.000',NULL,'2015-07-23 16:55:33.000','2015-04-06 00:00:00.000',NULL,1,'DEFSERVER:\DATA\ARCHIVE\MILANADM\11\1\33238.1','MILANADMIN','MILANADMIN',NULL,NULL,NULL,NULL,NULL,'WORDX','DOC',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'X',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,'Document "Zone for G+T script" moved.',NULL,33240,1,NULL,9646,NULL,NULL,NULL,'2015-07-30 17:00:00','MILANDEMO','MILANDEMO',1)
,(154,87,'Active',0,'Archive',NULL,NULL,'Zone for G+T script',33240,15721,'2015-04-06 00:00:00.000',NULL,'2015-07-23 16:55:33.000','2015-04-06 00:00:00.000',NULL,1,'DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33240.1','MILANADMIN','MILANADMIN',NULL,NULL,NULL,NULL,NULL,'WORDX','DOC',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'X',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,'Document "Zone for G+T script" moved.',NULL,33239,1,NULL,12,NULL,NULL,NULL,'2015-07-30 17:03:00','MILANDEMO','MILANDEMO',1)
,(155,87,'Active',0,'Archive',NULL,NULL,'Zone for G+T script',33240,15721,'2015-04-06 00:00:00.000',NULL,'2015-07-23 16:55:33.000','2015-04-06 00:00:00.000',NULL,2,'DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\2\33240.2','MILANADMIN','MILANADMIN',NULL,NULL,NULL,NULL,NULL,'WORDX','DOC',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'X',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,'Document "Zone for G+T script" moved.',NULL,33239,2,NULL,12,NULL,NULL,NULL,'2015-07-30 17:03:00','MILANDEMO','MILANDEMO',1);
SELECT * FROM mytable;
GO
DROP TABLE mytable;
Now we have a table with 94 columns and 4 rows but we still don't know what to do with this data.
EDIT: Most of these columns are NULLs, please consider posting relevant columns only.
August 4, 2015 at 12:44 pm
Luis Cazares (8/4/2015)
Hi welcome to the forums.What you explain might be possible, but a simple pdf with truncated rows won't help. We need DDL, sample data as insert statements and the expected results for that sample data. We also need the rules to get the expected results.
naydenov.stoqn (8/4/2015)
1. Find a way to distinguish the pair of documents in the target db that are the same even though they have different docnums.We can help you to distinguish this using T-SQL, but you need to define what makes them the same. The name? Something else?
Read this article[/url] or the one posted in my signature to know what we need to help.
That's a challenging set of sample data, because the individual fields haven't been separated or typed. I can get this easily transformed to an EAV structure, and someone else can take it the rest of the way.
As to dealing with the uniqueness in the comments field, we'll have no way to help you there without the actual COMMENTS field containing that data as it originally exists in the CSV. There may be any number of complications that are due to extraneous data that accompanies the old and new values.
DECLARE @mytable AS TABLE (
FIELD1 VARCHAR(840) NOT NULL PRIMARY KEY
);
INSERT INTO @mytable(FIELD1)
VALUES ('ID,PID,SrcDB,TreeId,TargetDB,PrjId,PrjPId,DOCNAME,DOCNUM,DOCSIZE,EDITWHEN,DOCINUSE,ENTRYWHEN,EDITPROFILEWHEN,INDEXED,VERSION,DOCLOC,AUTHOR,OPERATOR,ALIAS,CHECKEDOUT,COMMENTS,COMINDEX,ARCHIVE_REQ,T_ALIAS,C_ALIAS,SUBCLASS_ALIAS,ARCHIVED,INUSEBY,INDEXABLE,ISRELATED,C1ALIAS,C2ALIAS,C3ALIAS,C4ALIAS,C5ALIAS,C6ALIAS,C7ALIAS,C8ALIAS,C9ALIAS,C10ALIAS,C11ALIAS,C12ALIAS,C13ALIAS,C14ALIAS,C15ALIAS,C16ALIAS,C29ALIAS,C30ALIAS,C31ALIAS,CDBL1,CDBL2,CDBL3,CDBL4,CBOOL1,CBOOL2,CBOOL3,CBOOL4,CDATE1,CDATE2,CDATE3,CDATE4,DECLAREWHEN,PENDING_CLOSE,PENDING_IRM,IRM,ARCHV_ID,LASTUSER,RETAIN,LOGNODEADDR,DEFAULT_SECURITY,IS_SECURED,TYPE,MSG_ID,IS_EXTERNAL,HAS_ATTACHMENT,EXTRNL_AS_NRML,FILEENTRYWHEN,FILEEDITWHEN,Status,Deleted,Msg,Error,TargetDocNum,TargetVersion,TargetPrjPId,TargetPrjId,Ctr,LastEditDate,InsertedBy,InsertedDate,OldServer,NewServer,IsMoveJob'),
('152,85,Active,0,Archive,NULL,NULL,Zone for G+T script,33238,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33238.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33238,1,NULL,12,NULL,NULL,NULL,2015-07-30 16:58:00,MILANDEMO,MILANDEMO,1'),
('153,86,Archive,0,Active,NULL,NULL,Zone for G+T script,33238,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\ARCHIVE\MILANADM\11\1\33238.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33240,1,NULL,9646,NULL,NULL,NULL,2015-07-30 17:00:00,MILANDEMO,MILANDEMO,1'),
('154,87,Active,0,Archive,NULL,NULL,Zone for G+T script,33240,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,1,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\1\33240.1,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33239,1,NULL,12,NULL,NULL,NULL,2015-07-30 17:03:00,MILANDEMO,MILANDEMO,1'),
('155,87,Active,0,Archive,NULL,NULL,Zone for G+T script,33240,15721,2015-04-06 00:00:00.000,NULL,2015-07-23 16:55:33.000,2015-04-06 00:00:00.000,NULL,2,DEFSERVER:\DATA\DOCS\ACTIVE\MILANADM\11\2\33240.2,MILANADMIN,MILANADMIN,NULL,NULL,NULL,NULL,NULL,WORDX,DOC,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,X,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,256,NULL,Document "Zone for G+T script" moved.,NULL,33239,2,NULL,12,NULL,NULL,NULL,2015-07-30 17:03:00,MILANDEMO,MILANDEMO,1');
DECLARE @SQL AS nvarchar(max) = 'DECLARE @';
WITH COLUMN_LIST AS (
SELECT S.*
FROM @mytable AS T
CROSS APPLY dbo.fnDelimitedSplit8K(T.FIELD1, ',') AS S
WHERE T.FIELD1 LIKE 'ID%'
)
SELECT DENSE_RANK() OVER(ORDER BY M.FIELD1) AS RN, CL.Item AS FIELD_NAME, S.Item AS VALUE
FROM @mytable AS M
CROSS APPLY dbo.fnDelimitedSplit8K(M.FIELD1, ',') AS S
INNER JOIN COLUMN_LIST AS CL
ON S.ItemNumber = CL.ItemNumber
WHERE M.FIELD1 NOT LIKE 'ID%';
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 4, 2015 at 1:29 pm
Steve,
That was bad generated sample data, not the real structure of the data.
August 4, 2015 at 2:02 pm
The attached script should help you generate usable test data... Simply plug in the table name (line 12) and the number of rows you want (line 18)
If you need to filter the results to get the specific rows you want, set the @Debug parameter to 1 (line 19) and add a where clause to the generated code.
You shouldn't need to mess with any of the other parameters. They simply allow you to set obfuscation rules if you need to obfuscate sensitive data. For your current purposes, just ignore them.
To get the table definition, Go to the Object Explorer in SSMS and find your table in the list... Form there choose Script Table as > CREATE To > New Query Editor window.
HTH,
Jason
August 4, 2015 at 3:53 pm
Luis Cazares (8/4/2015)
Steve,That was bad generated sample data, not the real structure of the data.
I know, but at the time I started that post, no one else had responded yet. By the time my post was completed (which included me getting interrupted several times), several responses had already been made. No big deal. I ended up just giving a query that despite the crap structure, could go at least half way towards providing structure by at least grabbing the data into an EAV format (entity, attribute, value).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply