CODEWITHSUNDEEP
sql-serverdatabasedatabase-designimporttext-files
elnath78
elnath78

Reputation: 137

How to import text file into MSSQL

I need to import this file into the following table structure:

CREATE TABLE [dbo].[tab_sorts](
[row] [int] IDENTITY(1,1) NOT NULL,
[id] [int] NOT NULL,
[date_sort] [nchar](10) NOT NULL,
[date_day] [int] NOT NULL,
[date_month] [int] NOT NULL,
[date_year] [int] NOT NULL,
[town] [nchar](2) NOT NULL,
[numbers_sort] [nchar](14) NOT NULL,
[nuso_1] [int] NOT NULL,
[nuso_2] [int] NOT NULL,
[nuso_3] [int] NOT NULL,
[nuso_4] [int] NOT NULL,
[nuso_5] [int] NOT NULL,
CONSTRAINT [PK_tab_sorts] PRIMARY KEY CLUSTERED 
(
[row] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I'm wondering what is the best practice to import the data (I need to split the date into day, month, year and I need to cluster the number comma delimited to perform a LIKE search after using a single % instead of two)

Upvotes: 1

Views: 232

Answers (2)

HansLindgren
HansLindgren

Reputation: 369

Your file seems simple enough but depending on your Version and Tools, if you want a more generic solution, you can use 'Import Data' (Right click on any DB in Management Studio, Tasks->Import Data...).

Upvotes: 0

elnath78
elnath78

Reputation: 137

ok, here is how i did, super fast way for me:

  1. downloaded Notepad++ to open the TAB delimited file in a formatted way without altering the structure
  2. created a new temporary table to hold the data with essential fields
  3. selected the lines from the file i wanted to import, CTRL+C them
  4. opened the table as EDIT and selected first row then CTRL+V the rows

This solved for me and allowed me to import super fast, now I just need to import this data into my table with relative additional fields (or add the fields to this table and do the job with queries as suggested).

Upvotes: 1

Related Questions