Use navicat to export the data table structure and insert statement, navicatinsert
After using navicat for a while, it was found that navicat is indeed very convenient and supports multiple databases. I used to think that his SQL files are troublesome everywhere. Every table generates an SQL file, which is inconvenient to import. Today, I suddenly found that it also has the ability to batch generate an SQL file.
Procedure
Menu: Data Transmission
Select the target, set the target, and click Start.
How to Use navicat to export data, SQL statements, and table Structures
Open navicat, right-click the database you want to export, and select dump SQL file! You can also open the database and have an export wizard at the top to select the exported items!
The database exports data from the table in the form of an insert statement.
Since someone proposed to use a statement to implement it, we should use a statement. I made her into a stored procedure and passed the test in the Environment sql2005. One note is that the field value in the table cannot be blank, otherwise, a null value is returned:
Step 1: -- create a result table to save the data to be output.
Create table out_data
(Flag int, -- identifier
Tb_name varchar (1000), -- table name
Insert_info varchar (8000) -- insert statement
Step 2: Create a stored procedure:
Create proc [dbo]. [out_table_data]
@ Tb_name varchar (1000) -- table name
As
Declare @ str varchar (8000), -- store the field name
@ Insert_str varchar (8000), -- stores the processing result string
@ Bl varchar (8000): stores variables and their data types.
@ Bl2 varchar (8000), -- store the variable name
@ Bl3 varchar (8000), -- Data Type
@ Bd varchar (1000), -- start with quotation marks
@ Bd2 varchar (1000), -- end with quotation marks
@ Sum_str varchar (8000), -- stores value assignment variables and fields
@ Sum_str_column varchar (8000), -- store the variable fields to be extracted
@ J varchar (3000), -- number of fields
@ I varchar (3000) -- Starting number of cycles
Set @ str =''
Set @ bl = 'desc' -- declare a variable (retain spaces in the middle)
Set @ bl2 =''
Set @ bl3 =''
Set @ I = 1
Set @ sum_str =''
Set @ sum_str_column =''
Set @ bd = ''' +'
Set @ bd2 = '+ '''''''
-- Number of fields
Select @ j = count (a. [name]) from syscolumns A inner join sysobjects B
On A. id = B. id and B. name = @ tb_name
-- Automatically defines variables into the string @ bl (case when c. [name] in ('Char ', 'nchar', 'varchar ', 'nvarchar') then' ('+ convert (varchar (500),. length) + ') 'else' 'end)
Select @ bl = @ bl + '@' +. [name] + ''+ 'varchar (8000) '+', ', @ bl3 = @ bl3 + c. [name] + ',' from syscolumns A inner join sysobjects B on. id = B. id
Inner join policypes c
On A. xtype = C. xusertype
And B. name = @ tb_name
Set @ bl = left (@ bl, len (@ bl)-1)
-- The table field to be queried... the remaining full text>