Merge data from 2 tables into one

Question

I have Employees and Non Employees tables (data is being imported from views).

Now, I need to create a single table which holds both Employee and Non Employee data. I am unable to progress from here.

I am getting this error when I try it using import-export task:

Executing (Error)

Messages • Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "Unit Number" (44) to column "Unit Number" (101). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard) • Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Unit Number]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Unit Number]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (85) failed with error code 0xC0209029 while processing input "Data Conversion Input" (86). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

score 2 · Accepted Answer · edited Aug 17 '16 at 11:44

2

First Create by script a new table with the column that you want.

CREATE TABLE dbo.Employees (
     COLUMN_A   TYPE,
     COLUMN_B   VARCHAR(100)
);

After this, made a Insert with selecting the your old tables.

INSERT INTO dbo.Employees ( COLUMN_A, COLUMN_B )
   SELECT COLUMN_A, COLUMN_B FROM TABLE1 UNION ALL
   SELECT COLUMN_A, COLUMN_B FROM TABLE2;

And Create an index to improve performance at your most queried column.

edited Aug 17 '16 at 11:44

TheGameiswar

25,396
5
48
82

answered Aug 17 '16 at 11:42

Marcelo Macedo

91
5

will above query get rid of duplicates? – Aruna Raghunam Aug 17 '16 at 11:49
I ram above query and getting this Error:Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. – Aruna Raghunam Aug 17 '16 at 11:51

score 1 · Answer 2 · answered Aug 17 '16 at 11:47

1

After reviewing the details it seems "Unit Number" column has different data type. To overcome this problem either make the data type same of both the table or create a new table which has high data type then existing one (means if your table has tiny int, small int) then the common table will have data type smallint.

answered Aug 17 '16 at 11:47

Rajat Jaiswal

627
3
12

so in this case I have Unit Number as int and other tablea s varchar(1). any idea what common table data type should be? – Aruna Raghunam Aug 17 '16 at 11:55
If this is the case then the best way is to keep every column as VARCHAR and once you confirm that the particular column has specific data type then change accordingly in int, tinyint, smallint etc. – Rajat Jaiswal Aug 21 '16 at 08:59

score 1 · Answer 3 · answered Aug 17 '16 at 11:59

1

Implicit conversion issue is caused when rows in varbinary type are inserted into varchar or any other data type. Firstly identify which column is defined as varbinary and use cast as cast(column_name as varchar(100)) and the issue will be solved.

answered Aug 17 '16 at 11:59

Ranjana Ghimire

1,669
1
9
18

I tried CONVERT(varchar(MAX), PictureName, 2) but its not working :( – Aruna Raghunam Aug 17 '16 at 12:14
try cast(PictureName as varchar(max)) . It should work – Ranjana Ghimire Aug 18 '16 at 04:27

score 1 · Answer 4 · answered Aug 17 '16 at 12:22

1

Why you are using Import Export wizard?

If your tables and views are in same database, you can do following:

INSERT INTO TABLE
SELECT COL1, COL2....FROM VIEW

If you are using import export task, ensure the source and target datatype and length matches (for char/varchar/nchar/nvarchar truncation may happen if destination column length is less than the source).

answered Aug 17 '16 at 12:22

p2k

2,028
4
21
37

Yes I have 2 views in the same database. Is it possible to insert data from 2 different views to single table using above query? – Aruna Raghunam Aug 17 '16 at 12:33
Yes, develop your T-SQL first by combining your views and follow the above syntax. – p2k Aug 17 '16 at 12:40
That works! thanks..but one issue: 1st table had varbinary picturenames and after converting to varchar I see ÿØÿà.. – Aruna Raghunam Aug 17 '16 at 13:30

Merge data from 2 tables into one

4 Answers4