How Do I Split And Insert A Comma Delimited String To A Table?
I am passing in a string of dates, delimited by a comma.
So
01/01/04, 02/01/04, 03/01/04
etc
I would like to enter each of these values into a table via an INSERT stored procedure.
Does anyone have any code for this?
View Complete Forum Thread with Replies
Related Forum Messages:
Conditional Where Clause With Comma Delimited String And Link Table
I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere if @sports not null user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!
View Replies !
Split Up Comma-delimited Field
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas. Any ideas how I could do this in T-SQL?
View Replies !
Quotes Around The Comma Delimited String
I am trying to build a dynamic where statement for my sql stored prcoedure if len(@Office) > 0 select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice + ',' + ''',''' + '))' + ' and ' + @strWhereClause In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',') My csfParseDeLimitedText function looks like this Create FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text insert @resultsselect *from @tempResultsreturnEND My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up. Any help will be greatky appreciated. Thanks
View Replies !
Splitting A Comma Delimited String
I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be 3:4:5:4 2:4:1 4:1:2:5:2 I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows: Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255) Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2' While @loc != 0 begin set @prevloc=(case when @loc = 1 then 0 else @loc end) +1 set @loc = charindex(',',@person,@loc+1) Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end)) print 'String = ' + @Str set @cnt=@cnt+1 end RESULT ------ String = 3:4:5:4 String = 2:4:1 String = 4:1:2:5:2 Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.
View Replies !
SELECT WHERE IN Comma Delimited String
Hello I have a table with column Options where each field contains a comma delimited list of ID numbers. I want to select any records where a certain ID number appears in that list. So something like: SELECT * FROM Table t1 WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID) But that gives me the error: Syntax error converting the varchar value '39,20' to a column of data type int. I feel I'm close though! Could anyone point me in the right direction? Many thanks Square
View Replies !
Multiple Rows Into A Comma Delimited String
I have the following table:id name1 yes2 no3 what4 is5 this6 niceThe amount of rows can vary from 1 to 50. I only need the name column.What SQL statement do I have to execute to get the following:yes,no,what,is,this,nice, (trailing , is acceptable)Thanks!
View Replies !
How To Parse A String Column With Comma Delimited
Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156
View Replies !
Passing A Comma Delimited String Of Parameters To A Stored Proc
Hello, I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets. I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc. But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections? Thanks.
View Replies !
Error Using Bulk Insert On A Comma Delimited File
I am having a problem using the Bulk Insert task. I am getting the msg: SSIS package "Package.dtsx" starting. Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "You do not have permission to use the bulk load statement.". Task failed: Bulk Insert Task SSIS package "Package.dtsx" finished: Success. I have been granted ownership of the database. I also tried in one of my old databases that I just finished developing and I got the same msg. The file I am importing is comma delimited. I am importing it into a table that has 50 bytes allocation for each field (the max input field size is 40 bytes). The connection is solid; Format = “Specify� RowDelimiter = {CR}{LF} columnDelimiter = Comma {,} No other options are set. The data looks like: "tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000", Any ideas why I am getting this message?
View Replies !
Debug Stored Procedure That Uses Comma Delimited List To Insert Multiple Records
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc: exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8' Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs) CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150) ASBEGIN DECLARE @FileID INT SET NOCOUNT ON /*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate) Select @FileID=@@Identity /*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000) SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
View Replies !
Please - Split Select From Table To Comma 1,2,3,...
help please i have this table number_row fld_index vtext ---------------------------------------- 1 101 a 2 101 b 3 101 c 4 102 d 5 102 e 6 102 f 7 103 g 8 103 h 9 103 i ...... ...... .... now i need to do this SELECT fld_index FROM table_index GROUP BY fld_index ---------------------------- and i get 101 102 103 104 how split this select and do this in('101' ,'102','103','104') ------------------------------ Code Snippet DECLARE @aaa set @aaa =(SELECT fld_index FROM table_index GROUP BY fld_index) set fld1 = CASE WHEN fld1 in(@aaa ) then '*' else fld1 end ---- need to split the @aaa ,'101' ,'102' , '103','104' , ---------------------------------------------------------------------------------------------------------------------------- instead of this update update [dbo].[tb_pivot_big] set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end , fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end , fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end , fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end , fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end from [dbo].[tb_pivot_big]
View Replies !
Comma Delimited File With Comma Inside A Column
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN, it was never a problem in DTS 2000. any work around. Thanks,
View Replies !
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
We have the following two tables : Link ( GroupID int , MemberID int ) Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) ) The Link table contains the records showing which Member is in which Group. One particular Member can be in multiple Groups and also a particular Group may have multiple Members. The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated Groups ID, showing in which Groups the particular Member is in). We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to fill the GroupID field of the Member table, from the Link Table. For instance, Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID, then update the GroupID field of the corresponding Member in the Member table. Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View Replies !
SELECT WHERE (any Value In Comma Delimited List) IN (comma Delimited List)
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist. Here is an approach I am trying: TABLE Events(EventID int, Categories varchar(200)) EventID Catetories -------------------------- 1 ‘6,8,9’ 2 ‘2,3’ PROCEDURE ListFilteredEvents @FilterList varchar(200) -- contains ‘3,5’ AS SELECT EventID FROM Events WHERE (any value in Categories) IN @FilterList Result: EventID ---------- 2 How can I select all records where any value in the Categories column matches a value in @FilterList. In this example, record 2 would be selected since it belongs to category 3, which is also in @FilterList. I’ve looked at the table of numbers approach, which works when selecting records where a column value is in the parameter list, but I can’t see how to make this work when the column itself also contains a comma delimited list. Can someone suggest an approach? Any examples would be greatly appreciated! Gary
View Replies !
Comma Delimited Row
Hi, Good evening! I have a problem. I have a namelist coming from a distribution list of an active directory. When I converted it to csv file, the members reside in just one column and separated by a comma. I want the names to be separated in a row one by one. I tried it on excel and I used the transpose column but to no avail. My last resort is to import it on sql but the names on the column was cut and not complete. Do you have any idea how to do this. Your help is highly appreciated. this is the sample file.. names kelly.yap, lizzy.fox, yahoo, finance.dep, hope.miller, porly.john the maximum names in a row is 566. thanks in advance. myBU
View Replies !
Comma Delimited Results Help Please
Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated. tblservers servid servername 1 server1 2 server2 3 server3 tblapplications appid appname 1 app1 2 app2 3 app3 tblapplink id appid servid 1 1 1 2 1 2 3 1 3 4 2 1 5 2 3 6 3 1 we want to display this information as below: appname serverlist app1 server1, server2, server3 app2 server1, server3 app3 server1 Thank you very much, mrtwo
View Replies !
Comma Delimited Errors
I need to import comma delimited text files into sql tables. in one of the column, there is a comma in the string itself. e.g. Cust_ID Name Phone Address1 Address2 Date that I have: 001,juia, anderson,4694568855,,Cedar Spring The data does not have double quote as text qualifiers. but as you see, on the Name column, there is a comma, which is not a delimiter. can anybody give any suggestions on how i can deal with that? i would appreciate it so much. thanks
View Replies !
Comma Delimited Processing
Suppose I have a table named Test (referred in the query below) Category Indicators ctgy1Y,,,, ctgy2Y,Y,Y,N, ctgy3,Y,,Y, and If I would like to transform this table to Category Indicators ctgy1Y ctgy2Y ctgy2Y ctgy2Y ctgy3Y ctgy3Y I am able to do it using the logic below CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED) WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5 BEGIN INSERT dbo.Numbers DEFAULT VALUES END SELECT category, SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program FROM Test inner JOIN Numbers ON SUBSTRING( ',' + Value, Number, 1 ) = ',' and CHARINDEX( ',', Value+',', Number ) - Number <> 0 where Number <= Len(Value) + 1 But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on Category Indicators ctgy1Q1 ctgy2Q1 ctgy2Q2 ctgy2Q3 ctgy3Q2 ctgy3Q4 What is the best and efficient way to obtain this? Any help will be greatly helpful. Thanks Ram
View Replies !
Comma Delimited Name Column
I'm not sure it this is the correct forum, but here goes. I am not a DBA, but rather a .NET developer that has been thrust into working on an SQL database created using documentation and modeling from another database. The data is provided by the state of NC in a fixed length format. Getting the data into the database is no problem. The problem is that one of the searchable columns, the name column, is populated with the full name delimited by commas. My first thoughts are to create columns for the different name parts. The problem with that is the names sometimes do not follow the "last, first, middle, suffix" pattern. Some names in the column are of other nationalities that may consist of about five or six name parts. On top of that there are instances where there may be two or three commas before, after or in the middle of the name data. Searching the data as it is was simplified by creating a full-text index and searching the data with the containstable and near predicates and functions. The issue comes in when I the searcher needs to search for different spellings, either by the end user or the person that entered the data. Example: "Keith or Keeth". The FORMSOF function doesn't seem to do the trick when searching the name column. I have experimented with the Soundex function provided in SQL but that really doesn't seem to work on the comma delimited column data either. I get way too much useless results to deal with considering there are over 30 million rows of data to search. Does anyone have any suggestions on the best approach for this problem?
View Replies !
Comma Delimited Text File
Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.
View Replies !
Creating A Comma Delimited List
Hi, I have a complex query where each row in the final dataset is a product. However each product has a number of authors associated with it. What I would like to do is have a query/subroutine join the authors to the product, as a string: ProductID Title Authors 1 The Sacred and the Profane John Rieggle, George Alexi 2 Dancing in the Dark Dan Brown, Peter Kay, Paul Dwebinski Products Table ============== ProductID Title Authors Table ============= AuthorID Name Product Authors Table ===================== AuthorID ProductID Is this at all possible? Thanks jr.
View Replies !
Passing Comma Delimited Parameter To SP
Is this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause. Ex: Where x In(@parametername) the x column is an integer. How can one work around this??? Thanks!
View Replies !
Help - How To Select From A Comma Delimited Field ?
Dear SQL, Lets say I have a field: User_Names and it can have more than one name example: "Yovav,John,Shon" How can I select all the records that has "Yovav" in them ? I try some like this: SELECT * FROM User_Table WHERE User_Names IN ('Yovav') but it only works if the field User_Names only has "Yovav" in it with no extras... is there some SQL function to tell if string is found in a field ? Any hope 4 me ?
View Replies !
COMMA Delimited TEXT FILE
Hi,On SQLServer 2000, I have a table with a following structure:MYTABLEcol1 char,col2 date,col3 numberMy Objective:------------Externally (from a command line), to select all columns and write theoutput into a file delimited by a comma.My method:---------1. Probably will use OSQL or BCP to do this.2. Use the following syntax:select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)from MYTABLE;My 3 Problems:-------------1) If there is a NULL column, the result of concatenating any value withNULL, is NULL. How can I work around this? I still want to record thiscolumn as null. Something like say from the example above, if col2 isnull, would result to: APPLE,,52) The time format when querying the database is: 2003-06-24 15:10:20.However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can Ipreserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost theSS.3) Which utility is better? BCP or OSQL?For OSQL, it has a "-s" flag which gives me the option of putting acolumn separator. But the result is:"APPLE ,14 JUN 2003 , 5"I don't need the extra space.While for BCP, there is no column separator flag.You will notice from my inquiry above that my background in SQLServer isnot very good.Thanks in Advance!!RegardsRicky*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
Help Moving Comma Delimited Data
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place. I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated. Thanks
View Replies !
Inner Join On A Comma Delimited List?
Hi, I have the following query: SELECT * FROM News INNER JOIN Newsletters ON News.ID = Newsletters.fkNewsID My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?
View Replies !
Importing A Comma Delimited File
Hello... I have a problem... When I insert data from a comma delimited file using this mehod a flat file connection sorting, merge join and inserting into the database I get "" around all the data!! The quotes end up around the column names and everything! I had to go in and manually remove the quotes in the text file to get some of my data conversions to work. I know there is a better way. How do I get SSIS to load the data without the quotes? This is an example of the data in the file: "1007","1","A","","Congratulations - No Health Violations Found","11/02/2005","1007" When I remove the quotes I do not have any problems. How do I do this without modifying the underlying data? Any ideas would be greatly appreciated!! Thank you for your help! SD
View Replies !
How To Handle Comma Delimited Columns
Hello I have this newbie question regarding SSIS: I have a table named People(#Code, Name, eMailList), where the column eMailList has a list of email addresses separated by commas, like "someone@hotmail.com,someone@gmail.com,someone@yahoo.com". I would like that for each Person, extract the email address info in such a way that I can insert into the following table: EMail(#Code, #ID, Address), where Address has a single email address. How can I accomplish that? Thanks a lot.
View Replies !
Splitting A Comma Delimited Column
Hi I wondered if anyone could help me. I have a table that holds an ID in the first column then a list of values split by commas in the 2nd column i.e.: IDColumn: 1 2ndCoumn: 1stvalue, 2ndvalue, 3rdvalue, 4thvalue I am trying to return as a dataset of this that shows the ID as column 1 then each value in the 2nd column as individual columns if I use SELECT LEFT('2ndColumn,PATINDEX(',',2ndColum)-1) I can return the first value as a column but then can't return any further values individually after the first column, I am just learning the new functionality in SSIS so not sure whether this would be my answer as apposed to T-SQL, if anyone has any advice on this it would be greatly appreciated? Thanks in advance Caralyn
View Replies !
Help With Subquery: Comma-delimited Id List To Value
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery within a query to keep myself from having to loop over the original query on display and then run additional queries to get the further info. here's the setup. i have two tables: persons table column: name (varchar) column: vehicleids (varchar) vehicles table column: id (int pk) column: vehiclename (varchar) - The persons table is a list of peoples' names and what kind of vehicle/s they own. - The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field. - The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field. The result i want returned by the query is: NAME - VEHICLES Joe Somebody - car,bicycle Sheila Johnson - van,pogostick,motorcycle John Nobody - skateboard,car The query i'm trying to run to get this result is: Code Snippet SELECT pe.name, ( SELECT ve.vehiclename FROM vehicles ve WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids) ) AS vehicles FROM persons pe ORDER BY pe.name It returns the persons names in the first column, but only returns vehicle names in the second column when there's a single id in the persons.vehicleids field. if there's more than one integer in the field, it returns an empty string. Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if its necessary, but I'm not looking for a stored procedure solution or a temp table solution. Any takers? I would be in the kharmic debt of anyone providing a workable avenue. Thank you, Tyler
View Replies !
Creating Columns From A Comma Delimited Row
Hi All This is my first official post...very exciting:) OK.... I need to split a string, based on comma delimetrs, into columns of a table. I am using SQL Server 2005. The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table. A typical record will look like this: AP05-07,ACTUAL,ZA01,...... I have looked at some of the past solutions to this type of post, but am battling to understand.... So if its possible, is there a simple way to create a stored procedure that will do this? Many thanks
View Replies !
Bcp Comma Delimited File Format
i'd like my output text file fromthe following to be in this format "field 1","field2","field3","field4" but instead it is showing this way field 1","field2","field3","field4 it is missing the opening and closing " here is the stored procedure i am running declare @today datetime Declare @table varchar(100) declare @FileName varchar(100) set @today = getDate() set @table = 'tblOptOut' set @FileName = 'C:Uploadupload_ccsi.txt' If exists(Select * from information_Schema.tables where table_name=@table) Begin Declare @str varchar(1000) set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'_'+convert(char(10),@today,120)+'" -t """,""""'+'" -c"''' Exec(@str) end else Select 'The table '+@table+' does not exist in the database' GO what am i doing wrong?
View Replies !
Export To Comma Delimited File
I'm trying to upload a small Web application with a one table database. The hosting company, GoDaddy requires that I upload the database as a comma delimited file. I created the database in Visual Web Developer Express but also have Visual Studio and SQL Server Management Studio Express. I can't figure out how to export the database into a comma delimited file using any of these tools. This should be simple like it is in Access but that doesn't seem to be the case. This is holding up deploying my Web Application. Can anyone help me? Thanks
View Replies !
Export SQL Server To A Comma Delimited File
Hi, I'm trying to deploy my Web site to GoDaddy. They told me I have to export the SQL Server Express database to a comma delimited file and then upload that file. The export procedure is simple in Access but I don't see any way to do it in SQL Server or from Visual Web Developer or Visual Studio. Also, I can ask them, but I assume I have to export each table separately and also export the ASPNETDB as well. Thanks for the help
View Replies !
Design Question Regarding Comma Delimited Data
Hi, I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below: movie_title | movie_rating | category_name--------------------------------------------------------------------------------------------------Pirates of the Carribean | PG-13 | Action,Adventure,FantasyEvil Dead | Unrated | Horrorand so on ...I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift? It all seems counter-intuitive. Thoughts? Thanks :o)
View Replies !
Comma Delimited List Of IDs To A Recordset From 2 Tables...
Have 2 tables in SQL Server 05 DB: First one is MyList user_id -> unique value list -> comma-delimited list of user_ids notes -> random varchar data Second one is MyProfile user_id -> unique value name address email phone I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @user_id for MyList then return all this data. The format of the comma-delimited data is as such (all values are 10-digit alphanumerics): d25ef46bp3,s46ji25tn9,p53fy76nc9 The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned). Thank you!
View Replies !
Processing Comma-delimited Strings (redux)
Hello, I have no trouble converting a comma-delimited string of values into multiple records, bur have recently encountered a problem that's giving me a headache - hopefully someone on the forum already has some experience doing this task: create table tester (col1 int, col2 varchar(1000), col2 varchar(1000)) insert into tester values(1, '1,3,5,7', 'a,c,e,g,') insert into tester values(2, '11,13,15,17', 'aa,ac,ae,ag,') There is no correlation between rows, but between the 2 varchar columns is a positional relationship - in the first record, the '1' in col2 relates to the 'a' in col 3, same for the '3' and 'c', on and on. The values within each of the comma-delimited strings of the 2 columns are positionally related. Say they could be time and temperature values, with a string of time values in col1 and a string of related temps in col2. This is data from an external system that I have no control over, but must load the data into my system I need to write a select statement that will return the contents like so: 1, 1, a 1, 3, c 1, 5, e 1, 7, g 2, 11, aa 2, 13, ac 2, 15, ae 2, 17, ag Has anyone encountered such as this? Any clues or code snippets? Thanks for any ideas, sa
View Replies !
Loops And Building Comma Delimited Strings
The problem: I have 2 tables, with a one to many relationship - lets say customers, and order items. Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this: 1, 2, 3 And if the next order item for that customer has a quantity of 4, the reference number value is 4, 5, 6, 7 And the final item with quantity of 2: 8, 9 Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric. In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers. If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it: 4, 5, 6 (2nd) 7,8 (3rd with same quantity of 2). I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running. This is what I have so far. The print lines and hard coded values are for debugging purposes only. DECLARE @NumberingType varchar(10) DECLARE @TotalSum int DECLARE @DoorLineItemID int DECLARE @Quantity int DECLARE @SeedInt int SET @SeedInt = 1 SELECT @TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345 DECLARE UpdateRefCursor CURSOR FOR SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1 OPEN UpdateRefCursor FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity DECLARE @RefNumberLine varchar(1024) SET @RefNumberLine = '' WHILE @@FETCH_STATUS = 0 BEGIN WHILE @SeedInt <= @Quantity BEGIN SET @RefNumberLine = @RefNumberLine + CONVERT(varchar, @SeedInt, 101) + ', ' SET @SeedInt = @SeedInt + 1 END PRINT @RefNumberLine SET @SeedInt = @Quantity + @SeedInt PRINT 'new seed: ' + CONVERT(varchar, @SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @Quantity + @SeedInt, 101) FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity END CLOSE UpdateRefCursor DEALLOCATE UpdateRefCursor This returns the same delimited string for X number of items. So I'm getting this: 1,2,3 1,2,3 1,2,3 When I really want the results described above. What am I doing wrong? Thanks!
View Replies !
Export SQL Data To Comma Delimited Csv File
Hi, I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file. So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited. I tried finding examples but couldn't see what I should do to have a comma delimited file. (I'm getting a bit tired now, so I might be missing something!) I have created a bat file containing the following code: bcp "TestDB..GA_FSM_DCSF_Extract" out "C:GA_FSM_DCSF_Extract.csv" -fexport.fmt -e "C:error.log" -c -T -S srckvzg2j -r Any help / pointers would be much appreciated. Thanks, Henrik
View Replies !
Comma Delimited List Update Stored Procedure
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list. Example: name1 = value1, value2, value3, etc. name2 = value1, value2, value3, etc. name3 = value1, value2, value3, etc. Here is how I wrote my stored procedure: CREATE PROCEDURE dbo.Sp_Update_ABR_Record ( @abrID int, @ddo varchar(50), @ay varchar(50), @strategy varchar(10), @budgacct varchar(10), @budgobj varchar(10), @origamt varchar(50), @incrdecr varchar(50), @review char(10), @abrdetlsID varchar(50) ) AS UPDATE DIM_ABR_REQ_HDR SET ABR_review = @review WHERE ABR_ID = @abrID UPDATE DIM_ABR_REQ_DETLS SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay, ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct, ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr) WHERE ABR_DETLS_ID = @abrdetlsID GO The second update is where the comma delimited list needs to be handled. The first update is only updating one field once. Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this? Thanks for any help. -D-
View Replies !
SSIS Issue: How To Handle Comma Delimited Columns
Hello I have this newbie question regarding SSIS: I have a table named People(#Code, Name, eMailList), where the column eMailList has a list of email addresses separated by commas, like "someone@hotmail.com,someone@gmail.com,someone@yaho o.com". I would like that for each Person, extract the email address info in such a way that I can insert into the following table: EMail(#Code, #ID, Address), where Address has a single email address. How can I accomplish that? Thanks a lot.
View Replies !
Import Into Comma Delimited Text File (very Urgent)
I want to join differnet tables and import the data into comma delimited text file. There will be lot of checks like if then else to manipulate data. I want to use stored procedure but don't know how to output to text file. Is there any utility which can be used in stored procedure. In future this will be run as an automated job. Thanks in advance.
View Replies !
|
TRACKER
