January 27, 2017 at 12:35 pm
Hi All,
First of all thanks for the time .
Issue:-
I have a table with 6 columns a,b,c,d,e,f .
Now this Table should be loaded from a CSV file which has 4 columns and its data column a,b,c,d .
Column e is a+'|'+b
Column f is a+'|'+c+'|'+d
As I am new to SQL Serverkindly help me to write a Procedure (SP) to achive the same.
SP to load table from CSV and then updation of the column e and f.
January 27, 2017 at 2:39 pm
minturimi_04 - Friday, January 27, 2017 12:35 PMHi All,
First of all thanks for the time .Issue:-
I have a table with 6 columns a,b,c,d,e,f .
Now this Table should be loaded from a CSV file which has 4 columns and its data column a,b,c,d .Column e is a+'|'+b
Column f is a+'|'+c+'|'+dAs I am new to SQL Serverkindly help me to write a Procedure (SP) to achive the same.
SP to load table from CSV and then updation of the column e and f.
Why would you need that in your table? It looks like Columns E and F are derived columns. Why not create a view and use that in place of the table when you need the calculated column values?
January 28, 2017 at 7:31 am
Thanks but I need in all these 6 columns in a table for that my reporting from MSTR is easy to build.
Second could you please let me know the process for loading a table from CSV file using SP.
January 30, 2017 at 8:31 am
If you don't want to use a view, computed columns may be an option since it looks like you just want a simple concatenation of other columns in the same row.
https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
For actually doing the import from a CSV file, the 2 main options are going to be BULK INSERT:
https://msdn.microsoft.com/en-us/library/ms188365.aspx
or OPENROWSET
https://technet.microsoft.com/en-us/library/ms190312(v=sql.110).aspx
I've often found BULK INSERT to be less finicky when reading CSV files, but you will probably need a format file to map the CSV columns to your table.
January 30, 2017 at 11:02 am
minturimi_04 - Saturday, January 28, 2017 7:31 AMThanks but I need in all these 6 columns in a table for that my reporting from MSTR is easy to build.Second could you please let me know the process for loading a table from CSV file using SP.
You should be careful sacrificing data integrity to make it easier to build a report. How do you propose keeping these columns in synch when one of the columns deriving this data gets updated?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2017 at 11:02 am
Chris Harshman - Monday, January 30, 2017 8:31 AMIf you don't want to use a view, computed columns may be an option since it looks like you just want a simple concatenation of other columns in the same row.
https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspxFor actually doing the import from a CSV file, the 2 main options are going to be BULK INSERT:
https://msdn.microsoft.com/en-us/library/ms188365.aspx
or OPENROWSET
https://technet.microsoft.com/en-us/library/ms190312(v=sql.110).aspx
I've often found BULK INSERT to be less finicky when reading CSV files, but you will probably need a format file to map the CSV columns to your table.
+10000 - computed columns are the best options for this kind of thing by far!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2017 at 3:49 am
Chris Harshman - Monday, January 30, 2017 8:31 AMIf you don't want to use a view, computed columns may be an option since it looks like you just want a simple concatenation of other columns in the same row.
https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspxFor actually doing the import from a CSV file, the 2 main options are going to be BULK INSERT:
https://msdn.microsoft.com/en-us/library/ms188365.aspx
or OPENROWSET
https://technet.microsoft.com/en-us/library/ms190312(v=sql.110).aspx
I've often found BULK INSERT to be less finicky when reading CSV files, but you will probably need a format file to map the CSV columns to your table.
I actually prefer building a statement with Dynamic SQL so that I can use BCP for the Import. This can then be easily used within a stored procedure for scheduled execution or a batch file for on-demand execution.
What isn't clear from the OP is whether the import should occur on a schedule or on-demand (and how often) and whether or not the name of the CSV will change with each created file.
What also isn't clear is why MSTR has a Problem with using views. I have often used Talend for exactly this purpose.
February 2, 2017 at 11:32 am
Thanks Chris
View is perfectly ok with MSTR , I was just trying to load and edit the derived columns by just 1 SP so that my efforts are less
But unfortunately I do not have permisssion to us BCP statement.
Currently I have a Table I used the Import wizard of SQL Server to load then SP to update the derived columns..
I wanted to save the Wizard Step and do all by just executing a single SP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply