SQL - 在同一个表中复制数据 [英] SQL - Copy Data Within Same Table

问题描述

我不太擅长 SQL Server，但我正在尝试做一些幕后工作，以创建我们的 EMR 系统缺乏的一些功能 - 在患者之间复制表格(及其所有数据).

I'm not that great with SQL Server, but I'm trying to do some behind the scenes work to create some functionality that our EMR system lacks - copying forms (and all their data) between patients.

在 SQL Server 2008 R2 中，我有三个表来处理这些已填写的表单:

In SQL Server 2008 R2 I have three tables that deal with these forms that have been filled out:

**Table 1**
encounter_id  patient_id  date        time          etc  etc  etc  etc
1234          112233      2014-01-02  14:25:01:00   a    b    c    d

**Table 2**
encounter_id  page  recorded_on  recorded_by  etc   etc
1234          1     2014-01-02   134          asdf  asdf
1234          2     2014-01-02   134          jkl;  jkl;

**Table 3**
encounter_id  page  keyname  keyvalue
1234          1     key1     aaa
1234          1     key2     bbb
1234          1     key3     ccc
1234          1     key4     ddd
1234          2     key5     eee
1234          2     key6     fff
1234          2     key7     ggg

如您所见，它们都与遇到_id 匹配，后者与患者_id 相关联(在第一个表中).

As you can see, they all match together with the encounter_id, which is linked to the patient_id (In the first table).

我想要做的是将这三个表中特定遇到_id 的所有行复制回它们来自的同一个表中，但使用不同的(系统生成的)遇到_id 用于患者 ID，我会指定.实质上，将表格从一名患者复制到另一名患者.

What I'm trying to be able to do is copy all the rows in these three tables for a particular encounter_id back into the same table they come from, but with a different (system generated) encounter_id for a patient_id that I would specify. In essence, copying the form from one patient to another.

非常感谢您对此的任何帮助.

Any help on this is greatly appreciated.

推荐答案

做了个小例子，这里(链接)

该解决方案可能不必要地复杂，但它也提供了很多其他有用的东西，我只是想测试如何动态构建它.该脚本确实会打印出命令，从而可以相对容易地删除 TSQL 并按照您的意愿生成纯 SQL.

The solution is perhaps needlessly complex but it offers a good variety of other useful stuff as well, I just wanted to test how to build that dynamically. The script does print out the commands, making it relatively easy to remove the TSQL and just produce the plain-SQL to do as you wish.

它的作用是，它需要一个遇到_id，然后它将用于动态获取列(假设遇到_id 是 TABLE_1 的 PK)以在 TABLE_1 中插入新记录，存储 insert.encounter_id 值，并使用该值从 TABLE_2 和 TABLE_3 中获取和复制匹配的行.

What it does, is that it requires an encounter_id, which it will then use to dynamically fetch the columns (with the assumption that encounter_id is the PK for TABLE_1) to insert a new record in TABLE_1, store the inserted.encounter_id value, and use that value to fetch and copy the matching rows from TABLE_2 and TABLE_3.

基本上，只要结构是正确的(TABLE_1 PK 是遇到_id，这是一种身份类型)，您应该能够只更改脚本中引用的表名，并且无论哪种类型的列它都应该直接工作(以及其中有多少)您的特定表有.

Basically, as long as the structure is correct (TABLE_1 PK is encounter_id which is an identity type), you should be able to just change the table names referenced in the script and it should work directly regardless of which types of columns (and how many of them) your particular tables have.

脚本的要点是:

/* Script begins here */
DECLARE @ENCOUNTER_ID INT, @NEWID INT, @SQL VARCHAR(MAX), @COLUMNS VARCHAR(MAX)
IF OBJECT_ID('tempdb..##NEW_ID') IS NOT NULL
    DROP TABLE ##NEW_ID
CREATE TABLE ##NEW_ID (ID INT)

/* !!! SET YOUR DESIRED encounter_id RECORDS TO BE COPIED, HERE !!! */
  SET @ENCOUNTER_ID = 1234

IF EXISTS (SELECT TOP 1 1 FROM TABLE_1 WHERE encounter_id = @ENCOUNTER_ID)
BEGIN
    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', 'SELECT ')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_1' AND name <> 'encounter_id'

    SET @COLUMNS = 'INSERT INTO TABLE_1 OUTPUT inserted.encounter_id INTO ##NEW_ID '+@COLUMNS+' FROM TABLE_1 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    SELECT TOP 1 @NEWID = ID, @COLUMNS = NULL FROM ##NEW_ID

    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', '')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_2'
    SET @COLUMNS = 'INSERT INTO TABLE_2 ('+@COLUMNS+') SELECT '+REPLACE(@COLUMNS,'encounter_id',''+CAST(@NEWID AS VARCHAR(25))+'')
        +' FROM TABLE_2 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    SET @COLUMNS = NULL

    SELECT @COLUMNS = COALESCE(@COLUMNS+', ', '')+name
    FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TABLE_3'
    SET @COLUMNS = 'INSERT INTO TABLE_3 ('+@COLUMNS+') SELECT '+REPLACE(@COLUMNS,'encounter_id',''+CAST(@NEWID AS VARCHAR(25))+'')
        +' FROM TABLE_3 WHERE encounter_id = '+CAST(@ENCOUNTER_ID AS VARCHAR(25))

    EXEC(@COLUMNS)
    PRINT(@COLUMNS)

    IF OBJECT_ID('tempdb..##NEW_ID') IS NOT NULL
        DROP TABLE ##NEW_ID
END

这篇关于SQL - 在同一个表中复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！