What are the steps and methods for inserting data into SQL SERVER database?

How to insert data into SQL SERVER database

1. Open sql2008 and log in using windows identity

2. After logging in, right-click and select "Properties". Select "Security" on the left and check "SQL Server and Windows Authentication Mode" on the right to enable mixed login mode

3. Select "Connect", check "Allow remote connections to this server", and then click "OK"

4. Expand "Security", "Login Name"; "sa", right-click and select "Properties"

5. Select "General" on the left, select "SQL Server Authentication" on the right, and set the password

6. Right-click the database and select "Aspect"

7. Select "Server Configuration" in the drop-down box on the right; set the "RemoteAccessEnabled" property to "True" and click "OK"

8. SSMS has been set up now. Log out first, and then log in with sa. Success means that the sa account has been enabled

9. Open the sql server configuration manager

10. Next, start configuring SSCM, select "SQL Server Service" on the left, and ensure that "SQL Server" and "SQL Server Browser" on the right are running

How to import data from Excel table into SQLSERVER database

In the query analyzer, select Lee to operate the database object and write the SQL statement directly:

If you are importing data into an existing table, use

INSERT INTO table SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

form

If you are importing data and adding a new table, use

SELECT * INTO table FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

form.

The above statement is to read in all the columns in the SHEET1 worksheet in the EXCEL file. If you only want to import some columns, you can

INSERT INTO table (a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

In fact, you can use OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$) as a table, for example, I wrote such a sentence:

INSERT INTO eval_channel_employee(channel,employee_id)

SELECT CASE a.Channel WHEN 'DIY' THEN 1 WHEN 'RDC' THEN 0 WHEN 'KCM' THEN 2 ELSE 3 END

,b.id FROM

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:tempname.xls',sheet1$) AS a,pers_employee b

WHERE a.Employee code =b.code

simple way:

The import function can be directly implemented in SQL2005. I don’t know if it is possible in SQL2008.

The operation process is as follows:

Step one: Log in to SQL Server Management Studio,

Step 2: Right-click "Manage" in "Object Explorer" and click "Import Data" in the pop-up list

Step 3: Click "Next" in the "Import Wizard" dialog box, enter the "Select Data Source" dialog box, select "Microsoft Excel" in the "Data Source" list, and select the corresponding Excel. document, click "Next" when completed (be sure to check "First row contains column names" in this dialog box, so it is to change the column titles in the Excel document to the column item titles in the database table)

Step 4: Specify the target database service, click "Next"... until "Complete"

Step 5: Re-enter SQL Server Management Studio, enter the imported database table, and you can find the imported Excel document data.

You can try the following:

Open SQL Server Configuration Manager and enable SQL Server Agent (instance name). Change startup mode to "Auto"

How to efficiently batch import data into SqlServer

2.SqlBulkCopy is a good choice, it can be imported directly into the database from DataTable

, but please note that (1) the column name is consistent with the target table (2) the data type is consistent (3) null value fault tolerance processing, reference code: ///

To batch insert

///

The amount of data written in each batch.

(DataTable dataTable,(DataTable dataTable,

stringtableName,

intbatchSize =10000){using(SqlConnection connection =newSqlConnection(myConnectionString)){try{connection.Open(); //Add leading and leading characters to the table name using(varbulk =newSqlBulkCopy(connection, SqlBulkCopyOptions.KeepIdentity,

null){DestinationTableName=tableName,

BatchSize=batchSize}) {//Loop through all columns and add mapping to bulk//dataTable.EachColumn(c = bulk.ColumnMappings.Add(c.ColumnName, c.ColumnName), c = !c.AutoIncrement); foreach(DataColumn dcindataTable.Columns){bulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);}bulk.WriteToServer(dataTable);

bulk.Close();}returntrue;}catch(Exception exp){returnfalse;}finally{connection.Close();}}}

3. If the amount of data is very large, exceeding 10W, you can consider generating a .dtsx file and then calling it directly from the C# code. The efficiency is still good.

The above is the detailed content of What are the steps and methods for inserting data into SQL SERVER database?. For more information, please follow other related articles on the PHP Chinese website!