insert rows to column wise in sql server

Question

below is mine table name: sample1

ID NAME                                 ADDRESS
1  DAN  NO.10,CHANGJIANG XI STREET,JIANXI DISTRECT ,LUOYANG CITY,HENAN ,CHINA
2  SAM  BINALBAGAN NEGROS OCCIDENTAL  PHILIPPINES
3  JOSE B-36 L-40 PH-1 ST. JOSEPH VILLAGE 7, MARINIG CABUYAO LAGUNA, 4025

i need to enter the rows to column, but here the challenges is, in sample 2 i have only 4 columns source rows obtained 6 or more while using split function, how can i insert/ append balance data in ADRS4 after inserting datas in columns.

output should be as follows:

ID  NAME                  ADRS1            ADRS2            ADRS3                ADRS4
1   DAN                   NO.10       NGJIANG XI STREET   JIANXI DISTRECT  LUOYANG CITY,HENAN ,CHINA

The challenge you are facing here is because you have poor design. You have stored delimited data in a single column. This violates 1NF and is a serious pain to work with. You will have to parse this data first. http://sqlperformance.com/2012/07/t-sql-queries/split-strings Then you will have to PIVOT that data back into columns using the technique described in the question that Tab Alleman posted. — Sean Lange, Feb 22 '16 at 14:23

score 1 · Answer 1 · answered Feb 22 '16 at 14:31

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Where you want to skip a value simply input a null value. So you query should look something like this:

INSERT INTO table_name (name,adrs1,adres2,...)
VALUES (John,Null,Xi Street,...);

Your SQL design is not that great to begin with IMO but that should work with your current setup. I would create a table that has multiple columns then insert as needed, like this:

CREATE TABLE [dbo].[Table_1](
    [id] [int] NULL,
    [name] [varchar](50) NULL,
    [ADRS1] [varchar](50) NULL,
    [ADRS2] [varchar](50) NULL,
    [ADRS3] [varchar](50) NULL,
    [ADRS4] [varchar](50) NULL
) ON [PRIMARY]

insert rows to column wise in sql server

1 Answers1