asked on

Need to join two tables, in sql server

Hi all,
I need to join two tables, table1 has 1000 rows which has the identity number set to ID column, similarly I have another table with 1000 rows and it also has a ID column with identity number set to it, so now when I join this two tables after 1000 rows of 1st table the number should start from 1001 and so on from table 2. How can I do tat, can you please help me out in this.

Thanks,
Aparanjit

Nem Schlecht

I've got 2 solutions for you.

#1) Use a variable to store the max value of the ID column from the 1st table, then use id+max as the id for the second table (using UNION ALL):

DECLARE @data1_max_id INT;
SET @data1_max_id = (SELECT MAX(id) FROM table_one);

select id, f1, f2, f3 from table_one
UNION ALL
select @data1_max_id+id, f1, f2, f3 from table_two
;

Open in new window

If there are any gaps in the IDs, there will still be gaps in the results.

The second one here is less efficient and re-numbers all the IDs so you won't have any gaps:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS new_ID, f1, f2, f3
FROM (
      SELECT f1, f2, f3
      FROM table_one
      UNION ALL
      SELECT f1, f2, f3
      from table_two
      ) AS tbl_union
;

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aparanjith

ASKER

Hi,
I have id,name and salary columns in table1
and id,name,age columns in table2, how can we work then with the union clause. Or do we have any other way, say we have only the id column same in both the tables and, all other columns are different, then how can we join or union.

PortletPaul

always useful knowing the full extent of the need in the question.

Like any union, you must align all parts of that with the same number of columns, and the data types must also be correct for each column. I'm assuming age in an int field here change the cast() if needed:

SELECT
       ROW_NUMBER() OVER(ORDER BY Source, ID) AS new_ID
     , ID as orig_id
     , name
     , salary
     , age
FROM (
      SELECT 1 as Source, id, name, salary, cast(null as int) as age
      FROM table_one
      UNION ALL
      SELECT 2 as Source, id, name, NULL, age
      from table_two
      ) AS tbl_union
;

Open in new window

Just as an observation, is "id" actually a foreign_key to some other table?
(e.g. a "person id")
why not put this information into a single row per person?