Import columns from one table into another table with dissimilar field layout
Import columns from one table into another table with dissimilar field layout
(OP)
I have a permanent template with zero records. Every time I get a new file from my client I push the csv file to sql and my end game is to import that file into my permanent template then export back to csv format in the same field layout all the time.
In Foxpro I just type 'Append From <table> and it finds all the matching field names automatically and populates the table. Can that be done in SQL? I can't find anything but I'm sure I should be able to accomplish it. I don't want to input 68 columns using an insert command. My client files change field layout all the time too so it's important to get the right template before I start the data work so my data is always in a uniform format.
Any help would be so much appreciated. Sorry to ask but after a full day and a half trying different things I'm ready to throw in the towel here!
In Foxpro I just type 'Append From <table> and it finds all the matching field names automatically and populates the table. Can that be done in SQL? I can't find anything but I'm sure I should be able to accomplish it. I don't want to input 68 columns using an insert command. My client files change field layout all the time too so it's important to get the right template before I start the data work so my data is always in a uniform format.
Any help would be so much appreciated. Sorry to ask but after a full day and a half trying different things I'm ready to throw in the towel here!
RE: Import columns from one table into another table with dissimilar field layout
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Import columns from one table into another table with dissimilar field layout
The field (column) names in both tables are the same but my permanent template has less fields/columns (72 vs 189).
The csv does have header rows. I'm used to working in foxpro so I don't have any issue importing records from one dbf to another one as long as the field names are the same in both dbf files.
RE: Import columns from one table into another table with dissimilar field layout
BCP and format files should lead to a solution: https://learn.microsoft.com/en-us/sql/relational-d...
First create a format file for your permanent import table, then create one for the current CSV format and map the fields. See the next steps section about mapping fields
https://learn.microsoft.com/en-us/sql/relational-d...
Instead of manually rearranging the field rows or XML portions you could write a generator.
Chriss
RE: Import columns from one table into another table with dissimilar field layout
It looks to me you have 2 tables: TableA and TableB
"my permanent template has less fields/columns"
So, you have another table: "permanent template" table? (TableC...?)
And you also have a CSV file(s) from your clients as well, right?
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Import columns from one table into another table with dissimilar field layout
This only works from DBF to DBF, not appending a csv text file into a DBF.
So actually in VFP you also would need a mapping of field names, you can take the first line of the CSV file as a template for names, taking the field types from your import staging table, but you either need a table for the CSV import as a DBF in the order of the fields of the CSV captions or you - and that's only working with BCP in the SQL Server context - you need a format file that can map the fields of the CSV to the SQL Server table in a single step.
Chriss
RE: Import columns from one table into another table with dissimilar field layout
Well, and if all else fails you could redo the import with VFP and then use SSIS with DBFs as a data source, use a linked server using a Foxpro DBC or a directory of DBFs via VFP ODBC driver.
Then there are newer things like Azure Data Factory, if ou use SQL Server in the context of Azure in the cloud instead of on-premise database.
Chriss
RE: Import columns from one table into another table with dissimilar field layout
CODE
Your permanent table would be the template of the column data types for the dbo.Newstagingtable, which then is designed with all the column names in the order of the CSV file and could take in the CSV in a BULK COPY step. But there's still one thing that makes this complicated, too: As you say the CSV has much more fields than you actually have in your import table (dbo.permanenttable), such a SELECT column-list would cause errors about non-existing columns. You can of course remove them, but it will be a tedious process to go through 189-72=103 error reports of missing column names to arrive at the list of fields your import table can actually provide the column definitions of. All unknown columns would need to be defined as varchar columns, for example, if you still import the CSV without a format file by simply importing all columns.
It's not the first thought and likely not the best anyway, so ou still need to do some work per different CSV format you need to deal with.
Chriss