SQL database table , Data separated by ","
-
I thought it will work for a series of data in one column but it did not. Actually, if i have 1,2,3 in one column and would like to put 1 in column A, 2 in column B and 3 in coulumn C of another table. I had something like: insert test (a) select substring(aa,1,patindex('%,%',aa)-1)as a from table1 insert test (b) select substring(aa,3,patindex('%,%',aa)-1)as b from table1 insert test (c) select substring(aa,5,patindex('%,%',aa)-1)as c from table1 but what happens is that it does not put data in 1 row. instead it gives me 3 rows like this: 1 NULL NULL null 2 NULL NULL NULL 3 How do I put this in one row phokojoe
-
I thought it will work for a series of data in one column but it did not. Actually, if i have 1,2,3 in one column and would like to put 1 in column A, 2 in column B and 3 in coulumn C of another table. I had something like: insert test (a) select substring(aa,1,patindex('%,%',aa)-1)as a from table1 insert test (b) select substring(aa,3,patindex('%,%',aa)-1)as b from table1 insert test (c) select substring(aa,5,patindex('%,%',aa)-1)as c from table1 but what happens is that it does not put data in 1 row. instead it gives me 3 rows like this: 1 NULL NULL null 2 NULL NULL NULL 3 How do I put this in one row phokojoe
Each INSERT statement creates the number of rows selected - 1 in the case of an INSERT...VALUES statement, the number of rows returned by the SELECT in the case of INSERT...SELECT. To put all the data in a single row, you simply need to select all the data you need for that row:
INSERT test (a, b, c)
SELECT
SUBSTRING(aa, 1, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 3, PATINDEX('%,%',aa)-1 ),
SUBSTRING(aa, 5, PATINDEX('%,%',aa)-1 )
FROM table1Stability. What an interesting concept. -- Chris Maunder