Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
learning_grsql
Posting Yak Master
230 Posts |
 Posted - 2012-07-29 : 09:25:10
|
| Hi,I'm new to sql. I have imported a table from csv file into my sql server management studio.The imported table contains a column with values like 'and 1234 lant''dow 6789 abti'In this column, I want to display only the numbers i.e. 1234, 6789. I don't want any letters preceding or following the numbers.I can do this in query with charindex and substring. But I'm wondering is it possible that every time I import a data into the database, the column automatically displays only numbers.After reading various sources, it seems it has to do with something called table trigger. But I'm not sure how to start with. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-07-29 : 10:23:13
|
If you are using SSIS (or Import/Export Wizard), you can easily filter the column in the package by using a function.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-29 : 15:06:54
|
quote:
Originally posted by learning_grsql
Hi,I'm new to sql. I have imported a table from csv file into my sql server management studio.The imported table contains a column with values like 'and 1234 lant''dow 6789 abti'In this column, I want to display only the numbers i.e. 1234, 6789. I don't want any letters preceding or following the numbers.I can do this in query with charindex and substring. But I'm wondering is it possible that every time I import a data into the database, the column automatically displays only numbers.After reading various sources, it seems it has to do with something called table trigger. But I'm not sure how to start with.
its not a good idea to use trigger for this. You should be doing this transform while you do data import itself either using sql functions like CHARINDEX etc if import is via sql stored proc or via SSIS derived column task if import is why ssis export import task.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-07-29 : 15:11:21
|
| Thanks for reply. I export via SQL Server Import and Export Wizard. Do you mean I shall write a query in the window that I get if I select "Write a Query to specify the data to transfer"? |
|
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2012-07-29 : 15:42:53
|
| I'm trying to import excel file using the wizard.Can I write a query for a excel file? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-29 : 16:15:05
|
quote:
Originally posted by learning_grsql
I'm trying to import excel file using the wizard.Can I write a query for a excel file?
yep you can instead of using export import wizard launch Business intelligence development studio and start a new integration serviced project. then you'll be able to add derived task in between excel source and oedb destination to do your transformation logic------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
automatically update column while importing