将选择结果转换为插入脚本 - SQL Server

Question

提问by Captain Comic

I have SQL Server 2008, SQL Server Management Studio.

我有SQL Server 2008，SQL Server Management Studio。

I need to select data from Table1in database1. Then I have to edit some values in the results and insert values into Table1in database2.

我需要从Table1database1 中选择数据。然后我必须在结果和insert values into Table1database2 中编辑一些值。

Or let me put it other way.

或者让我换一种说法。

How can I convert the data in one table into insert script.

如何将一张表中的数据转换为insert script.

Answer 1

采纳答案by marc_s

SSMS Toolpack (which is FREEas in beer) has a variety of great features - including generating INSERT statementsfrom tables.

SSMS 工具包（在啤酒中是免费的）有很多很棒的功能——包括从表中生成 INSERT 语句。

Update:for SQL Server Management Studio 2012(and newer), SSMS Toolpack is no longer free, but requires a modest licensing fee.

更新：对于 SQL Server Management Studio 2012（及更新版本），SSMS Toolpack 不再免费，但需要适度的许可费用。

Answer 2

回答by KT.

Here is another method, which may be easier than installing plugins or external tools in some situations:

这是另一种方法，在某些情况下可能比安装插件或外部工具更容易：

Do a select [whatever you need]INTO temp.table_namefrom [... etc ...].
Right-click on the database in the Object Explorer => Tasks => Generate Scripts
Select temp.table_namein the "Choose Objects" screen, click Next.
In the "Specify how scripts should be saved" screen:
- Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties.
- Select "Save to new query window" (unless you have thousands of records).
Click Next, wait for the job to complete, observe the resulting INSERTstatements appear in a new query window.
Use Find & Replace to change all [temp.table_name]to [your_table_name].
drop table [temp.table_name].

做一个。select [whatever you need]INTO temp.table_namefrom [... etc ...]
在对象资源管理器中右键单击数据库 => 任务 => 生成脚本
选择temp.table_name在“选择对象”屏幕上，单击下一步。
在“指定应如何保存脚本”屏幕中：
- 单击高级，找到“脚本的数据类型”属性，选择“仅数据”，关闭高级属性。
- 选择“保存到新查询窗口”（除非您有数千条记录）。
单击下一步，等待作业完成，观察结果INSERT语句出现在新的查询窗口中。
使用查找和替换将所有更改[temp.table_name]为[your_table_name].
drop table [temp.table_name].

Answer 3

回答by tember

In SSMS:

在 SSMS 中：

Right click on the database > Tasks > Generate Scripts
Next
Select "Select specific database objects" and check the table you want scripted, Next
Click Advanced >in the list of options, scroll down to the bottom and look for the "Types of data to script" and change it to "Data Only" > OK
Select "Save to new query window" > Next > Next > Finish

右键单击数据库 > 任务 > 生成脚本
下一个
选择“选择特定的数据库对象”并检查要编写脚本的表，下一步
单击Advanced >选项列表，向下滚动到底部并查找“要编写脚本的数据类型”并将其更改为“仅数据”> 确定
选择“保存到新查询窗口”>下一步>下一步>完成

All 180 rows now written as 180 insert statements!

现在所有 180 行都写成 180 条插入语句！

Answer 4

回答by Andrew Orsich

Native method:

本机方法：

for example if you have table

例如，如果你有桌子

Users(Id, name)

You can do this:

你可以这样做：

select 'insert into Table values(Id=' + Id + ', name=' + name + ')' from Users

Answer 5

回答by Param Yadav

1- Explanation of Scripts

1- 脚本说明

A)Syntax for inserting data in table is as below

A)在表中插入数据的语法如下

Insert into table(col1,col2,col3,col4,col5)    

             -- To achieve this part i    
             --have used below variable
                ------@CSV_COLUMN-------

values(Col1 data in quote, Col2..quote,..Col5..quote)

-- To achieve this part 
-- i.e column data in 
--quote i have used 
--below variable
----@QUOTED_DATA---

C)To get above data from existing table we have to write the select query in such way that the output will be in form of as above scripts

C）要从现有表中获取以上数据，我们必须以上述脚本的形式编写选择查询

D)Then Finally i have Concatenated above variable to create final script that's will generate insert script on execution

D）然后最后我连接了上面的变量以创建最终脚本，该脚本将在执行时生成插入脚本

E)

@TEXT='SELECT ''INSERT INTO
 '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION

F)And Finally Executed the above query EXECUTE(TEXT)

F)并最终执行了上面的查询 EXECUTE(TEXT)

G)QUOTENAME()function is used to wrap column data inside quote

G)QUOTENAME()函数用于将列数据包裹在引号内

H)ISNULLis used because if any row has NULLdata for any column the query fails and return NULLthats why to avoid that i have used ISNULL

H)ISNULL被使用是因为如果任何行有NULL任何列的数据，查询失败并返回NULL这就是为什么要避免我使用过ISNULL

I)And created the sp sp_generate_insertscriptsfor same

I）并sp_generate_insertscripts为其创建了 sp

1- Just put the table name for which you want insert script

1-只需输入您想要的表名 insert script

2- Filter condition if you want specific results

2- 如果您想要特定结果，则过滤条件

----------Final Procedure To generate Script------

CREATE PROCEDURE sp_generate_insertscripts
(
    @TABLE_NAME VARCHAR(MAX),
    @FILTER_CONDITION VARCHAR(MAX)=''
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @CSV_COLUMN VARCHAR(MAX),
        @QUOTED_DATA VARCHAR(MAX),
        @TEXT VARCHAR(MAX)

SELECT @CSV_COLUMN=STUFF
(
    (
     SELECT ',['+ NAME +']' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION

--SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT

EXECUTE (@TEXT)

SET NOCOUNT OFF

END

Answer 6

回答by BotanMan

It's possible to do via Visual StudioSQL Server Object Explorer.

可以通过Visual StudioSQL Server 对象资源管理器来完成。

You can click "View Data" from context menu for necessary table, filter results and save result as script.

您可以从上下文菜单中单击“查看数据”以获取必要的表格、过滤结果并将结果保存为脚本。

Answer 7

回答by greg

Using visual studio, do the following

使用visual studio，执行以下操作

Create a project of type SQL Server-->SQL Server Database Project

创建一个类型为 SQL Server 的项目-->SQL Server 数据库项目

open the sql server explorer CTL-\ , CTL-S

打开 sql server explorer CTL-\ , CTL-S

add a SQL Server by right clicking on the SQL SERVER icon. Selcet ADD NEW SERVER

通过右键单击 SQL SERVER 图标添加 SQL Server。Selcet 添加新服务器

navigate down to the table you are interested in

向下导航到您感兴趣的表格

right click--> VIEW DATA

右键单击--> 查看数据

Click the top left cell to highlight everything (ctl-A doesnt seem to work)

单击左上角的单元格以突出显示所有内容（ctl-A 似乎不起作用）

Right Click -->SCript

右键单击-->脚本

This is fabulous. I have tried everything listed above over the years. I know there is a tool out there that will do this and much more, cant think of the name of it. But it is very expensive.

这太棒了。多年来，我已经尝试了上面列出的所有内容。我知道有一个工具可以做到这一点，还有更多，想不出它的名字。但它非常昂贵。

Good luck. I just figured this out. Have not tested it extensively w/ text fields etc, but it looks like it gets you a long ways down the road.

祝你好运。我只是想通了这一点。尚未使用文本字段等对它进行广泛的测试，但看起来它会让您走得很远。

Greg

格雷格

Answer 8

回答by KMR

Create a separate table using into statement For example

使用 into 语句创建一个单独的表例如

Select * into Test_123 from [dbo].[Employee] where Name like '%Test%'

Go to the Database Right Click the Database Click on Generate Script Select your table Select advanace option and select the Attribute "Data Only" Select the file "open in new query"

转到数据库右键单击数据库单击生成脚本选择您的表选择高级选项并选择属性“仅数据”选择文件“在新查询中打开”

Sql will generate script for you

Sql 会为你生成脚本

Answer 9

回答by Pankaj Agarwal

You can Choose 'Result to File' option in SSMS and export your select result to file and make your changes in result file and finally using BCP - Bulk copy you can insert in table 1 in database 2.

您可以在 SSMS 中选择“结果到文件”选项并将您选择的结果导出到文件并在结果文件中进行更改，最后使用 BCP - 批量复制您可以插入到数据库 2 的表 1 中。

I think for bulk insert you have to convert .rpt file to .csv file

我认为对于批量插入，您必须将 .rpt 文件转换为 .csv 文件

Hope it will help.

希望它会有所帮助。

Answer 10

回答by Neo

This is a more versatile solution (that can do a little more than the question asks), and can be used in a query window without having to create a new stored proc - useful in production databases for instance where you don't have write access.

这是一个更通用的解决方案（可以做的比问题多一点），并且可以在查询窗口中使用而无需创建新的存储过程 - 例如在您没有写访问权限的生产数据库中很有用.

To use the code, please modify according to the in line comments which explain its usage. You can then just run this query in a query window and it will print the INSERT statements you require.

要使用代码，请根据解释其用法的行内注释进行修改。然后您可以在查询窗口中运行此查询，它将打印您需要的 INSERT 语句。

SET NOCOUNT ON

-- Set the ID you wish to filter on here
DECLARE @id AS INT = 123

DECLARE @tables TABLE (Name NVARCHAR(128), IdField NVARCHAR(128), IdInsert BIT, Excluded NVARCHAR(128))

-- Add any tables you wish to generate INSERT statements for here. The fields are as thus:
 -- Name: Your table name
 -- IdField: The field on which to filter the dataset
 -- IdInsert: If the primary key field is to be included in the INSERT statement
 -- Excluded: Any fields you do not wish to include in the INSERT statement
INSERT INTO @tables (Name, IdField, IdInsert, Excluded) VALUES ('MyTable1', 'Id', 0, 'Created,Modified')
INSERT INTO @tables (Name, IdField, IdInsert, Excluded) VALUES ('MyTable2', 'Id', 1, 'Created,Modified')

DECLARE @numberTypes TABLE (sysId TINYINT)

-- This will ensure INT and BIT types are not surrounded with quotes in the
-- resultant INSERT statement, but you may need to add more (from sys.types)
INSERT @numberTypes(SysId) VALUES(56),(104)

DECLARE @rows INT = (SELECT COUNT(*) FROM @tables)
DECLARE @cnt INT = 1

DECLARE @results TABLE (Sql NVARCHAR(4000))

WHILE @cnt <= @rows
BEGIN
    DECLARE @tablename AS NVARCHAR(128)
    DECLARE @idField AS NVARCHAR(128)
    DECLARE @idInsert AS BIT
    DECLARE @excluded AS NVARCHAR(128)
    SELECT
        @tablename = Name,
        @idField = IdField,
        @idInsert = IdInsert,
        @excluded = Excluded
    FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowId FROM @tables) t WHERE t.RowId = @cnt

    DECLARE @excludedFields TABLE (FieldName NVARCHAR(128))
    DECLARE @xml AS XML = CAST(('<X>' + REPLACE(@excluded, ',', '</X><X>') + '</X>') AS XML)
    INSERT INTO @excludedFields SELECT N.value('.', 'NVARCHAR(128)') FROM @xml.nodes('X') AS T(N)

    DECLARE @setIdentity NVARCHAR(128) = 'SET IDENTITY_INSERT ' + @tablename

    DECLARE @execsql AS NVARCHAR(4000) = 'SELECT ''' + CASE WHEN @idInsert = 1 THEN @setIdentity + ' ON' + CHAR(13) ELSE '' END + 'INSERT INTO ' + @tablename + ' ('
    SELECT @execsql = @execsql +
    STUFF
    (
        (
            SELECT CASE WHEN NOT EXISTS(SELECT * FROM @excludedFields WHERE FieldName = name) THEN ', ' + name ELSE '' END
            FROM sys.columns
            WHERE object_id = OBJECT_ID('dbo.' + @tablename)
            FOR XML PATH('')
        ), 1, 2, ''
    ) +
    ')' + CHAR(13) + 'VALUES (' +
    STUFF
    (
        (
            SELECT
                CASE WHEN NOT EXISTS(SELECT * FROM @excludedFields WHERE FieldName = name) THEN
                    ''', '' + ISNULL(' +
                    CASE WHEN EXISTS(SELECT * FROM @numberTypes WHERE SysId = system_type_id) THEN '' ELSE ''''''''' + ' END +
                    'CAST(' + name + ' AS VARCHAR)' +
                    CASE WHEN EXISTS(SELECT * FROM @numberTypes WHERE SysId = system_type_id) THEN '' ELSE ' + ''''''''' END +
                    ', ''NULL'') + '
                ELSE ''
                END
            FROM sys.columns
            WHERE object_id = OBJECT_ID('dbo.' + @tablename)
            FOR XML PATH('')
        ), 1, 3, ''
    ) +
    ''')' + CASE WHEN @idInsert = 1 THEN CHAR(13) + @setIdentity + ' OFF' ELSE '' END +
    ''' FROM ' + @tablename + ' WHERE ' + @idField + ' = ' + CAST(@id AS VARCHAR)

    INSERT @results EXEC (@execsql)
    DELETE @excludedFields
    SET @cnt = @cnt + 1
END

DECLARE cur CURSOR FOR SELECT Sql FROM @results
OPEN cur

DECLARE @sql NVARCHAR(4000)
FETCH NEXT FROM cur INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql
    FETCH NEXT FROM cur INTO @sql
END

CLOSE cur
DEALLOCATE cur

将选择结果转换为插入脚本 - SQL Server

提问by Captain Comic

采纳答案by marc_s

回答by KT.

回答by tember

回答by Andrew Orsich

回答by Param Yadav

回答by BotanMan

回答by greg

回答by KMR

回答by Pankaj Agarwal

回答by Neo

相关推荐

最近更新

标签

将选择结果转换为插入脚本 - SQL Server

提问by Captain Comic

采纳答案by marc_s

回答by KT.

回答by tember

回答by Andrew Orsich

回答by Param Yadav

回答by BotanMan

回答by greg

回答by KMR

回答by Pankaj Agarwal

回答by Neo

相关推荐

有没有办法在 SQL Server 2000 数据库上列出打开的事务？

为什么 SQL Server 不支持无符号数据类型？

SQL 在两个数据库之间添加外键关系

SQL 如何编辑表格以启用 CASCADE DELETE？

相关推荐

最近更新

标签