COPY数据从table1到table2，日期插入为7天回还是14天回来？ [英] COPY data from table1 to table2 with date inserted as 7 days back or 14 days back?

问题描述

我有一个包含这些列的table1 -

  A 
 B 
 C 
 

现在我有一个table2，它有这么多列

  A 
 B 
 C 
 D 
 E 
  

在表2中，我有D作为 created_date ，E作为 updated_date 。现在我想导入table1数据到table2，我已经导出table1数据到CSV文件。

最初，我将列D和列E设置为now（），所以当我使用 COPY命令将数据从table1复制到table2 ，自动插入的日期是当前日期。





现在我正在寻找的是，每当我从table1复制任何数据table2， created_date 这是 D 和 updated_date 上面表2中的code> E 应设置为 now（） - interval'7 days'。

我已经导入table1数据到CSV文件，如上所述，现在我需要导入这个CSV文件到table2中，这样，当我插入使用COPY命令，我应该能够设置created_date和updated_date到 7天返回或 14天返回等。

基本上，我想这是可配置的，假设如果我使用COPY命令，然后根据情况，如果我需要插入数据7天回来，然后当我复制数据，我应该设置日期应该是7天前或14天前或30天后从命令行。

有什么办法吗？任何帮助将非常感谢..

我忘了要提到，这两个表都在不同的数据库中。所以我们不能在select查询上插入基础。

解决方案

  INSERT INTO [TARGET_DB ]。[dbo] .Table2 
（adID，CampaignName，EndDate）
 SELECT adID，[CampaignEndDate]，DATEADD（d，-14，[CampaignEndDate]）as EndDate 
 FROM [SOURCE_DB] 。[dbo] .Table1 
  

更换-14天后

如果数据库在同一台服务器上使用

更新您连接的其他数据库不同服务器string

使用

OPENQUERY（Transact-SQL）

http://technet.microsoft.com/en-us/library/ms188427.aspx

连接OPENDATASOURCE

http://technet.microsoft.com/ en-us / library / ms179856.aspx

I have a table1 which has these many columns -

A
B
C

Now I have a table2 which has these many columns

A
B
C
D
E

In table2 I have D as the created_date and E as the updated_date. Now I want to import table1 data into table2 for which I have already exported table1 data into CSV file.

Initially I have made column D and Column E as now() so while I was copying the data from table1 to table2 using COPY command, the date was getting inserted automatically which was the current date.

Now what I am looking for is, whenever I am copying any data from table1 to table2, created_date which is D and updated_date which is E in above table2 should be set to now()-interval '7 days'.

I have already imported table1 data into CSV file as I have mentioned above and now I need to import this CSV file into table2 such that while I am inserting using COPY command, I should be able to set the created_date and updated_date to 7 days back or 14 days back etc..

Basically, I want this to be configurable, suppose if I am using COPY command then depending on the situation if I need to insert the data for 7 days back, then while I am copying the data, I should set that date should be 7 days back or 14 days back or 30 days back from the command line.

Is there any way I can do that? Any help would be really appreciated..

UPDATE:-

I forgot to mention, both of the tables are in different database. So we cannot insert basis on select query I guess..

解决方案

INSERT INTO [TARGET_DB].[dbo].Table2
                     (adID, CampaignName, EndDate)
SELECT        adID, [CampaignEndDate],   DATEADD(d, -14, [CampaignEndDate]) as EndDate
FROM    [SOURCE_DB].[dbo].Table1

replace -14 days back

if DBs are in same server use above

to update other DB different servers you do connections string

use
OPENQUERY (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms188427.aspx

connections OPENDATASOURCE
http://technet.microsoft.com/en-us/library/ms179856.aspx

这篇关于COPY数据从table1到table2，日期插入为7天回还是14天回来？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！