CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
kafe
kafe

Reputation: 157

DB table extract and linking extracted data

let's assume we've got following tables in DB:

CREATE TABLE #nodes(reporter varchar(10), groupname varchar(10), node varchar(20))
CREATE TABLE #nodes_extract(id int IDENTITY, min_node varchar(20), count_nodes int, descr varchar(10) );
CREATE TABLE #nodes_histo(reporter varchar(10), groupname varchar(10), node varchar(20), nodes_extract_id int)

Example of data:

INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep1','group1','node1')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep1','group1','node2')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep1','group2','node3')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep2','group1','node1')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep2','group1','node4')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep2','group2','node5')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep3','group1','node5')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep3','group1','node6')
INSERT INTO #nodes (reporter, groupname, node) VALUES ('rep3','group2','node7')

Let's extract some data into #nodes_extract

INSERT INTO #nodes_extract
SELECT min(node), count(node), 'blabla'
FROM #nodes
GROUP BY reporter, groupname

Now I would like insert all information from #nodes to #nodes_histo and add nodes_extract_id Expected result in #nodes_histo:

    reporter    groupname   node    nodes_extract_id
    rep1        group1      node1   1
    rep1        group1      node2   1
    rep1        group2      node3   4
    rep2        group1      node1   2
    rep2        group1      node4   2
    rep2        group2      node5   5
    rep3        group1      node5   3
    rep3        group1      node6   3
    rep3        group2      node7   6

How would you achieve the goal? Is it possible without changing tables structure?

thanks a lot! kafe

Upvotes: 1

Views: 74

Answers (2)

kafe
kafe

Reputation: 157

Well I think I found a way..

First of all DISTINCT is required in the insert command:

INSERT INTO #nodes_extract
SELECT DISTINCT min(node), count(node), 'blabla'
FROM #nodes
GROUP BY reporter, groupname

And with following select I can extract information I'm looking for:

INSERT INTO #nodes_histo
SELECT a.reporter, a.groupname,a.node, e.id 
FROM 
    (SELECT  reporter
            , groupname
            , node
            , min(node) OVER (PARTITION BY REPORTER, groupname) m
            , count(node) OVER (PARTITION BY REPORTER, groupname) c
    FROM #nodes) a
LEFT JOIN 
    #nodes_extract e
ON a.m = e.min_node
AND a.c = e.count_nodes

Upvotes: 1

GilM
GilM

Reputation: 3771

No, you would have to include a way to join the tables, probably by including reporter and groupname in #nodes_extract. Even if you were to try to regenerate #nodes_extract with the same query (and the additional columns), there's no guarantee that the order would be the same.

Upvotes: 0

Related Questions