Help! Get version for each set of rows
-
In our concern, we have a table with lots of redundant rows, to avoid the redundant, we create some structure and denormalize the table now we need to migrate the old data into the new structure.
Here is my following table structure
DECLARE @TestTable AS TABLE(id INT, DAta1 VARCHAR(500), Data2 VARCHAR(500), Data3 VARCHAR(3))
INSERT INTO @TestTable
VALUES
(1, 'Name', 8, 1),
(1, 'possible Shifts', 30, 7),
(1, 'First shift', 22, 8),
(1, 'Second Shift', 24, 9),
(1, 'Third Shift', 22, 10),
(2, 'Name', 8, 11),
(2, 'Testing', 14, 16),
(2, 'possible Shifts', 30, 17),
(2, 'First shift', 22, 18),
(2, 'Second Shift', 24, 19),
(3, 'Name', 8, 20),
(3, 'possible Shifts', 30, 26),
(3, 'First shift', 22, 27),
(3, 'Second Shift', 24, 28),
(4, 'Name', 8, 29),
(4, 'possible Shifts', 30, 35),
(4, '1st shift', 18, 36),
(4, '2nd Shift', 18, 37),
(5, 'Name', 8, 20),
(5, 'possible Shifts', 30, 26),
(5, 'First shift', 22, 27),
(5, 'Second Shift', 24, 28)
SELECT *
FROM @TestTable
This is just a sample data we have a lot amount of data like this,
any changes in Data1 or Data2 might came under a new version, any new insert or delete in a ID column based Set might consider as a new version
so i need to migrate this into the following structure of output
DECLARE @tbl AS TABLE (ID INT, DAta1 VARCHAR(200), DAta2 VARCHAR(200), Dversion INT)
i need to import the data as set with out redundant please do not consider the column data3, and the output might be like this
SELECT * FROM
(
VALUES
(1,'Name','8',1),
(1,'possible Shifts','30',1),
(1,'First shift','22',1),
(1,'Second Shift','24',1),
(1,'Third Shift','22',1),
(1,'Name','8',2),
(1,'possible Shifts','30',2),
(1,'First shift','22',2),
(1,'Second Shift','24',2),
(1,'Name','8',3),
(1,'possible Shifts','30',3),
(1,'1st shift','18',3),
(1,'2nd Shift','18',3)
) AS vtable (id,[DAta1],[Data2],[DVersion])
This is for single time import only, please give some idea hope iam clear if not i will ready to clarify it
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
select distinct col1, col2 from your table?
-
Thanks for the reply, but that doesn't work for me any other point
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Sorry I couldn't help. It might help to see a better example of what you want as the output...I can't tell from your post. Could just be me, I guess.
-
Please give an example of what "redundant" means to you based on the data you provided. Apologies, but "doesn't work" doesn't mean very much. In what context? This is where a sample output to match against would make a big difference!
-
thava (3/20/2014)
i need to import the data as set with out redundantby import, do you mean insert?
typically i would join the new data against the destination table,and check for non matches:
INSERT INTO DestinationTable(Column1,Column2,ColumnList)
SELECT Column1,Column2,ColumnList
FROM StagingTable
LEFT OUTER JOIN DestinationTable
ON StagingTable.Column1 = DestinationTable.Column1
AND StagingTable.Column2 = DestinationTable.Column2
WHERE DestinationTable.Column1 IS NULL --No existing data matched, insert it.
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! -
i want the result as follows
SELECT * FROM
(
VALUES
(1,'Name','8',1),
(1,'possible Shifts','30',1),
(1,'First shift','22',1),
(1,'Second Shift','24',1),
(1,'Third Shift','22',1),
(1,'Name','8',2),
(1,'possible Shifts','30',2),
(1,'First shift','22',2),
(1,'Second Shift','24',2),
(1,'Name','8',3),
(1,'possible Shifts','30',3),
(1,'1st shift','18',3),
(1,'2nd Shift','18',3)
) AS vtable (id,[DAta1],[Data2],[DVersion])
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Ok, I will explain it clearly please do not consider the Data3 column
id DAta1 Data2 Data3
1 Name 8 1
1 possible Shifts 30 7
1 First shift 22 8
1 Second Shift 24 9
1 Third Shift 22 10
This is my First ID Based set, it might be the first version without the Data 3
id DAta1 Data2 Data3
2 Name 8 11
2 possible Shifts 30 17
2 First shift 22 18
2 Second Shift 24 19
From the above set the third Shift is missing in the second set so it might be considered as the second version
id DAta1 Data2 Data3
3 Name 8 20
3 possible Shifts 30 26
3 First shift 22 27
3 Second Shift 24 28
Since the second and third set is equal we just move to the next set
id DAta1 Data2 Data3
4 Name 8 29
4 possible Shifts 30 35
4 1st shift 18 36
4 2nd Shift 18 37
Since the first shift and second shift is changed from the second version this will be conidered as third version
id DAta1 Data2 Data3
5 Name 8 20
5 possible Shifts 30 26
5 First shift 22 27
5 Second Shift 24 28
Since the fifth set is matched with the second version we just leave it
Similarly, if there is any new record added it might be considered as a new version
On the whole the result might be the posted in the previous post
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
you said "Ignore the Data3 column"
if i do that, each set you posted are identical, with the exception of the id column, which seems to be incrementing?.
it seems the only common data DAta1 Data2 columns might or might not exist, right?
drilling down to specifics, in your example, if i received those rows, how do i know it's relationship between the original data? it's still not clear if you want to insert or update.
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! -
Hi lowel,
thanks for you, I added a identity column, in the results is it possible to produce that kind of result
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
!?
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply