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
idDAta1Data2Data3
1Name81
1possible Shifts307
1First shift228
1Second Shift249
1Third Shift2210
This is my First ID Based set, it might be the first version without the Data 3
idDAta1Data2Data3
2Name811
2possible Shifts3017
2First shift2218
2Second Shift2419
From the above set the third Shift is missing in the second set so it might be considered as the second version
idDAta1Data2Data3
3Name820
3possible Shifts3026
3First shift2227
3Second Shift2428
Since the second and third set is equal we just move to the next set
idDAta1Data2Data3
4Name829
4possible Shifts3035
41st shift1836
42nd Shift1837
Since the first shift and second shift is changed from the second version this will be conidered as third version
idDAta1Data2Data3
5Name820
5possible Shifts3026
5First shift2227
5Second Shift2428
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