CODEWITHSUNDEEP
sqlsql-server
Harry Torry
Harry Torry

Reputation: 373

SQL Server : combine two tables

I have 2 large tables, with around 25 columns each. They both contain the same sort of data but the columns are in different orders.

How can I combine these tables without having to re-arrange my query code? I'd rather not do that for a ~600 line script.

If needed, I can give an example of what it looks like but I don't really see the need.

I have so far tried;

SELECT * FROM [guest].table1 UNION ALL SELECT * FROM [guest].table2;
SELECT * FROM [guest].table1, [guest].table2;

I also tried inserting the data like so;

SET IDENTITY_INSERT [guest].table1 ON;
SET IDENTITY_INSERT [guest].table2 ON;

INSERT INTO [guest].table1 id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number)
select id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number
FROM [guest].table2;

But that gives me this error:

SQL Error: Cannot insert explicit value for identity column in table 'exported_cases' when IDENTITY_INSERT is set to OFF.

Any help would be greatly appreciated!

To clarify, I am exporting a database that currently exists in 2 portions, (table1 and table2). It already has the PK generated, and I am not inserting into it until it's completely exported, at which point the PK will be set to a number higher than any current PK.

(I need to keep the ID field preserved.)

Upvotes: 0

Views: 121

Answers (4)

oerkelens
oerkelens

Reputation: 5161

As everyone is focusing on the insert, let me answer the select part :)

For the UNION to work, make sure you explicitly select all the columns in the same order:

SELECT id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number
FROM [guest].table1
UNION ALL
SELECT id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number
FROM [guest].table2

This result set you can use to query, insert into a new table or whatever :)

Upvotes: 1

vhadalgi
vhadalgi

Reputation: 7189

    you should not insert identity element its generated auto:

just try inserting all except id(which is your identity)

pls try this:

    SET IDENTITY_INSERT [guest].table1 ON;
    SET IDENTITY_INSERT [guest].table2 ON;

    INSERT INTO [guest].table1 id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number)
    select id,  short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number
    FROM [guest].table2;

SET IDENTITY_INSERT [guest].table1 OFF;
SET IDENTITY_INSERT [guest].table2 OFF;

Upvotes: 0

chinna_82
chinna_82

Reputation: 6403

INSERT INTO   short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number)
select   short_name,  name,  invention_title, reference,  client_id,  client_ref,  date_case_opened,  date_case_closed,  case_type,  notes,  fee_earner,  created,  last_updated,  file_location,  foreign_attorney_id,  foreign_attorney_ref,  country_code,  application_number,  filing_date,  publication_number,  invoice_currency,  publication_date,  status,  pct_application_number,  pct_case_id,  national_phase_entry,  base_number,  base_country,  base_date,  base_case_id,  divisional_date_lodged,  illustrative_image,  parent_case_id,  parent_application_number
FROM [guest].table2;

Try this without the ID.

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14460

You have set the Identity Specification-> Is Identity to True

So you can't insert the auto generated primary key manually!!!

Upvotes: 0

Related Questions