October 24, 2011 at 3:27 am
I have an etl process that imports a "flat file". say, it has 2 columns and it is importing fine.
Is there a way to automatically change the import so that it will know a third column is introduced and import that new column? So, no one needs to click on the icon and add new column.
Thanks.
October 24, 2011 at 4:14 am
There is no way to do that automatically. If you'll think about it, you'll realize that it can't be done automatically. The columns have to be named, typed, and maybe also have some constraints. You cannot expect the server to know what the column’s name and type should be and what constraints to create.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 25, 2011 at 4:47 am
would it be possible to have the file to include the type, name, and constraints?
if so, how can it be done?
October 25, 2011 at 6:12 am
This will require SQL 2005
/*
Reference:
Tally OH! An Improved SQL 8K “CSV Splitter” Function
By Jeff Moden, 2011/05/02
http://www.sqlservercentral.com/articles/Tally+Table/72993/
*/
If Object_id('Tempdb..#Temp') is Null Create Table #Temp(LineText varchar(1024))
Bulk Insert #Temp from 'C:\Temp\DynamicColumns.txt'
with
(
RowTerminator=''
)
Select
Col1=[1],
Col2=[2],
Col3=[3],
col4=[4],
col5=[5],
col6=[6],
col7=[7]
From #Temp a
Cross Apply(Select * from dbo.DelimitedSplit8K(LineText,','))b
Pivot(max(Item) for ItemNumber in([1],[2],[3],[4],[5],[6],[7]))pvt
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply