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
AparanjithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
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

0
PortletPaulfreelancerCommented:
just a tiny change to the second option above, if you wish to ensure the result is ordered by ID:
SELECT ROW_NUMBER() OVER(ORDER BY Source, ID) AS new_ID, ID as orig_id, f1, f2, f3
FROM (
      SELECT 1 as Source, ID, f1, f2, f3
      FROM table_one
      UNION ALL
      SELECT 2 as Source, ID, f1, f2, f3
      from table_two
      ) AS tbl_union
;

Open in new window

Regarding efficiency, at 1000 rows per table I doubt you would even notice a difference. Functionally you have more control using row_number().
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AparanjithAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.