Append records with different number of Columns

Question

Append records with different number of Columns

OscarBoots

Ten Centuries

Points: 1258
More actions
February 16, 2015 at 1:24 pm

#297838

Hi,
I have a table of Customers & their data in about 20 Columns.
I have another table that has potential Customers with 3 Columns.
I want to append the records from Table 2 onto Table 1 to the Columns with the same names.
I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.
There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.
Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?
Thanks for any suggests.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

pbo71465 (2/16/2015)
Hi,
I have a table of Customers & their data in about 20 Columns.
I have another table that has potential Customers with 3 Columns.
I want to append the records from Table 2 onto Table 1 to the Columns with the same names.
I've thought of using UNION ALL or Select Insert but I'm mainly stuck on the most efficient way to do this.
There is also no related field that can be used to join the data as these Customers in table 2 have no Customer ID yet as they're only potential Customers.
Can I just append the 3 columns from Table 2 to the same 3 columns in table 1?
Thanks for any suggests.

The answer is going to depend on whether the target table accepts NULLs in columns you don't have data for, and describe how the customer IDs should be generated.

Post DDL to create the customers table, add a few customers and then show us the data you have for your prospects. Finally, show us how you want the results to look.

Note that it is possible I may have misinterpreted, because I'm not 100% sure you really want to insert prospects into a customers table if you don't have a Customer ID for them. Having the information I asked for would help to clarify this point.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

OscarBoots Ten Centuries Points: 1258 More actions · Answer 2

Thanks for your reply,

The key word here is append.

I have the 2 tables 3 Fields with the same name in each but the main table has other columns.

I simply want to append the 3 columns form the prospects table under the same columns in the main table.

I think I'll just add the extra columns to the prospects & Union All the tables.

Thanks