My data in below format. I have to insert into sql server table.
agency NO
booknbr 06000330
bookdttm 2006-07-19 10:56:00
arrdttm 2006-05-07 05:42:00
aj_id A000009454
casenbr 06006640
off1 619
off2 634
arrplace DENNYS RESTAURANT
howarr O
juvstat
ko
remarks
armed
federal N
agency NO
booknbr 06000331
bookdttm 2006-07-24 12:11:00
arrdttm 2006-07-16 16:11:00
aj_id A000003215
casenbr 06010336
off1 641
off2
arrplace 219 W GLENCOVE AV
howarr V
juvstat
ko
remarks
armed
federal N
agency NO
booknbr 06000332
bookdttm 2006-07-25 15:11:00
arrdttm 2006-06-13 22:47:00
aj_id A000009455
casenbr 06008615
off1 624
off2
arrplace 113 JULIE DR
howarr V
juvstat
ko
remarks
armed
federal N
Best Answer
You can use BULK INSERT and provide the FIELDTERMINATOR and ROWTERMINATOR values like below which will insert the data into the correct columns. The only downside is the data also includes the column name. A simple cursor to clean this up and the data looks fine.
You would want to insert into a staging table as all data types are VARCHAR on insert, then you could insert into a final data table that has correct data types after cleaning.
Create Table to Store Data:
BULK INSERT the data:
Clean the data (this cursor loops through each column and removes the column name from the inserted data):
Query the data:
Results: