SQL Server does not have the permission to import data.

Environment

SQL Server 2012 + CentOS 6.3

Problem description

Only the production database can log on, query, and create temporary tables. If you do not have the permission to import data (such as Excel files, txt files, and SQL scripts), you must create temporary tables and insert test data.

Problem Simulation

Because the data in the production database is sensitive and the data volume is very large, it cannot be provided. Here is just a simulation of this problem. The operation method is the same for a small amount of data and a large amount of data.

Problem Solving

We can do this:

This is the source data in Excel, 1:

Figure 1 Excel source data

Step 1First, copy the source data (data in Excel), or save it as a csv file (separated by commas), and rename the suffix to txt. The file name here is set to source.txt, and the first line title is removed;

6789, Robin, Zhu 'er, Chengdu 1234, justdb, Zhang San, Luzhou 4567, HelloWorld, Li Si, Guangzhou 5678, CSDN Blog, Wang Wu, Zhongshan 1331, Wen, Deng Liu, Shenzhen 3142, wentasy, Xu Qi, Changsha 4131, Fantasy, yanba, Kunming

Step 2The source data is ready. Now we switch to the Linux environment and start to process the data. If there are four columns of data in the source data, we need to separate the data. Awk processing is used here. The Code is as follows:

#-F indicates that each column in the source data is separated by commas (,) and saved as the four new files awk-F ", "'{print $1}' source.txt> source1.txtawk-F", "'{print $2}' source.txt> source2.txtawk-F ", "'{print $3}' source.txt> source3.txtawk-F", "'{print $4}' source.txt> source4.txt

The source data is as follows:

[Root @ robin opt] # cat source.txt

1234, justdb, Zhang San, Luzhou 4567, HelloWorld, Li Si, Guangzhou 5678, CSDN Blog, Wang Wu, Zhongshan 1331, Wen, Deng 6, Shenzhen 3142, Wentasy, Xu Qi, Changsha 4131, fantasy, yanba, Kunming

Operation Result:

[Root @ robin opt] # cat source1.txt

123445675678133131424131

Effect 2:

Figure 2 Step 2

Step 3Because the data is stored based on text, you must add single quotation marks or double quotation marks at the beginning and end of the INSERT value. The Code is as follows:

# ^ Indicates the beginning of the line. This line of code indicates adding yy to the beginning of each line. Note that the content added here should not be the same as the text in the body; sed's/^/yy/g'source1.txt-I # $ indicates the end of the line. This line of code indicates that zz is added at the end of each line. Likewise, meaning that the content added here should not be the same as the text in the text sed's/$/zz/g'source1.txt-I # Replace the yy at the beginning of the line with single quotes sed "s/yy/\ '/g "source1.txt-I # Replace zz at the end of the line with single quotes sed" s/zz/\ '/g "source1.txt-I # description: you can also replace the end and the beginning of a line with the same content. Then, replace the replaced content with single quotes and you only need to execute a line of code. # Only one text is demonstrated here, and the operation methods for other texts are the same.

The operation result is as follows:

[Root @ robin opt] # cat source1.txt

yy1234zzyy4567zzyy5678zzyy1331zzyy3142zzyy4131zz

[Root @ robin opt] # cat source1.txt

'1234''4567''5678''1331''3142''4131'

Effect 3:

Figure 3 Step 3

Step 4, We get the text with single quotes for each column, but we need to put each column of the four files in a file, just like fried green peaties, put the Chopped pork shreds, condiments, and green peppers in a pot for frying. We can use the following method to merge files and use the paste command. The command is as follows:

# This command is separated by commas (,). The preceding four files are merged and saved to the result file paste-d, "source1.txt source2.txt source3.txt source4.txt> result.txt.

The operation result is as follows:

[Root @ robin opt] # cat result.txt

'123', 'justdb', 'zhang san', 'luzhou '123', 'helloworld', 'Li si', 'guangzhou '123', 'csdn blog ', 'wang 5', 'zhongshan '123', 'wen', 'deng 6', 'shenzhen '123', 'wentasy', 'xu 7 ', 'changsha '123', 'fantasy', 'swallow 8', 'kunming'

Effect 4:

Figure 4 Step 4

Step 5. We add an INSERT statement at the end of the row. Assume that the temporary table name created after the row is # temp, and add parentheses and semicolons at the end of the row. The statement is as follows:

sed 's/^/INSERT INTO ##tempVALUES(/g' result.txt -ised 's/$/);/g'result.txt -i

The operation result is as follows:

[Root @ robin opt] # cat result.txt

Insert into # temp VALUES ('123', 'justdb', 'zhang san', 'luzhou '); insert into # temp VALUES ('123', 'helloworld ', 'Li si', 'guangzhou '); insert into # temp VALUES ('2013', 'csdn Blog', 'wang 5', 'zhongshan '); insert into # temp VALUES ('123456', 'wen', 'deng 6', 'shenzhen'); insert into # temp VALUES ('123456', 'wentasy ', 'xu 7', 'changsha '); insert into # temp VALUES ('201312', 'fantasy', 'swallow 8', 'kunming ');

Effect 5:

Figure 5 Step 5

Step 6To create a temporary table. The statement is as follows:

CREATE TABLE ##temp(       ID CHAR(16) NOT NULL,       EName VARCHAR(20),       CName VARCHAR(40),       City VARCHAR(20));

Step 7: Open the SQL Server Query analyzer and execute the statements for creating a temporary table and inserting data.

Result 6:

Figure 6 data insertion Effect

Other Instructions

1. If there is a space at the end of each line in the file, we can use this command to remove the space:

Sed's/\ + $/'source1.txt-I

2. If ^ M appears in the file, we can use this command to remove ^ M:

Sed's/^ M // g'source_4.txt-I

3This article is just a simple simulation. A small amount of data does not reflect the superiority of this method. If there is a large amount of data, it will bring you a qualitative leap;

4. In this article, step 3 can be simplified by adding INSERT and parentheses directly at the beginning and end of each row in each column, but this is only the first and last OK rows, and each string is not enclosed in single quotes, you can use each row as a unit and then add single quotes instead of separating each column in this article;

5This article also tells readers how to use Linux more and learn a scripting language, which will make your work more efficient;

6This article is based on the situation where you do not have the permission to import data. If you have the permission, it is naturally very simple. If not, this article is of great reference value. In fact, this article provides a way of thinking, how to split the problem and how to skillfully splice the text. The core technology used is the Linux Shell, such as the usage of awk and sed.

The final one-click script

#! /Bin/bash # FileName: auto_import_data.sh # Desc: Auto Import DataTo ms SQL # Date: 17:53:12 # Author: Robin #1. extract data awk-F "," '{print $1}' source.txt> source1.txtawk-F "," '{print $2}' source.txt> source2.txtawk-F ", "'{print $3}' source.txt> source3.txtawk-F", "'{print $4}' source.txt> source4.txt #2. add single quotes sed's/\ + $/'source1.txt-ised's/^/yy/g'source1.txt-ised's/$/zz/g'source1.txt-ised's/$/zz/g'source1.txt-ised "s/yy/\ '/g" source1.txt-ised "s/zz/\'/g" source1.txt-I sed's/^/yy/g'source2.txt-ised's/ $/zz/g'source2.txt-ised "s/yy/\ '/g" source2.txt-ised "s/zz/\'/g" source2.txt-I sed's/^/yy /g'source3.txt-ised's/$/zz/g'source3.txt-ised "s/yy/\ '/g" source3.txt-ised "s/zz/\'/g" source3.txt-I sed's/^/yy/g'source4.txt-ised's/$/zz/g'source4.txt-ised "s/yy/\ '/g" source4.txt-ised "s/zz /\ '/g "source4.txt-I #3. merge files paste-d "," source1.txt source2.txt source3.txt source4.txt> result.txt #4. concatenate the final INSERT statement sed's/^/insert into # tempVALUES (/G' result.txt-ised's/$/);/g'result.txt-I

My mailbox: wgbno27@gmail.com Sina Weibo: @ jutdb public platform: JustOracle (No.: justoracle) database technology exchange group: 336882565 (when adding group verification From csdn xxx)All is well  March 15, 2014  By Robin Wen

	@ Wentasy blog is for your reference only. Welcome to visit. I hope to criticize and correct any mistakes. If you need to repost the original blog, please indicate the source. Thank you :) [CSDN blog]