July 31, 2017 at 7:31 am
I need to create a table from 2 different tables but I am running into issues where 1 table has more columns than the other table. Is it even possible?select * into ContactAddressTest
from (SELECT [Company Title]
,[Company Id]
,[Company PMG Pin]
,[Company Rbit Id]
,[Company Net Sales]
,[Company Headquarters]
,[Company Truck Lots]
,[Company related Business Tags]
,[Company Email]
,[Company Website]
,[Company Legacy Operation]
,[Company Commodity]
,[Company About Us]
,[Company Phone and Fax]
,[Company Address Line 1]
,[Company Address Line 2]
,[Company State]
,[Company Country]
,[Company City]
,[Company Postal Code]
,[Contact Id]
FROM [Company]
UNION ALL
SELECT [Contact Name]
,[Contact Id]
,[Contact Email]
,[Contact Fax Number]
,[Contact Fax Extension]
,[Contact First Name]
,[Title]
,[Contact Job Title]
,[Contact Last Name]
,[Contact Middle Name]
,[Contact Mobile Number]
,[Contact Phone Number]
,[Contact Phone Number Extension]
,[Contact Rbit Id]
,[Company Id]
,[Column 15]
FROM [Contact]
) n
July 31, 2017 at 7:39 am
Yes, but you'll need to supply NULL values for the columns that don't exist. So, in simple terms:WITH AllData AS (
SELECT Title, Firstname, Surname,
CAST(NULL AS date) as DateOfBirth, --DateOfBirth does not exist in Customer Table. CAST so that SQL knows the data type
AddressLine1, AddressLine2, AddressLine3, CAST(NULL AS varchar(50)) AS AddressLine4, --AddressLine4 does not exist in Customer Table. CAST so that SQL knows the data type
PostCode
FROM Customer
UNION ALL
SELECT NULL AS Title, FirstName, Surname, --Title does not exist in Client Table. No need to CAST, as data type derived from above SELECT statement.
DateOfBirth,
AddressLine1, AddressLine2, AddressLine3, AddressLine4,
PostCode
FROM Client)
SELECT *
INTO YourNewTable
FROM AllData;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply