从另一个数据库INSERT INTO数据库，在SQL Server中指定一些值

I've researched about this but haven't found a particular solution for this case. I have two tables, each one in a different databases.

我已经研究过这个但是没有找到针对这种情况的特定解决方案。我有两个表,每个表在不同的数据库中。

Let's say they are DB1.T1 and DB2.T2.

假设它们是DB1.T1和DB2.T2。

From DB1.T1 I want to select all rows from two specific columns ID and Description.

从DB1.T1我想从两个特定列ID和描述中选择所有行。

DB2.T2 has the same two columns but also three additional columns: T2.ID, Enable, Date.

DB2.T2具有相同的两列,但还有三列:T2.ID,Enable,Date。

Some considerations. DB2.T2 is a SQL Server, I've always worked with MySQL but this SQL Server had some inconsistencies from my previous experiences. The AUTOINCREMENT default condition didn't exist, and also the insert of default timestamp or date value when new record is created was not available.

一些考虑。 DB2.T2是一个SQL Server,我一直使用MySQL,但是这个SQL Server与我以前的经历有一些不一致。 AUTOINCREMENT默认条件不存在,并且创建新记录时插入的默认时间戳或日期值不可用。

From my research I've come to some kinda solution that would be

根据我的研究,我已经找到了一些解决方案

INSERT INTO DB2.T2(ID, Description, Enable, Code ,Date)
    SELECT ID, Description 
    FROM DB1.T1;

BUT how can I add the three remaining columns?

但是我怎样才能添加剩下的三列呢?

I was thinking to do some while loop and insert the increment as ID for DB2.T2 using the allowed GETDATE() function, but I'm not sure how to do it right away.

我想做一些while循环,并使用允许的GETDATE()函数将增量作为ID插入DB2.T2,但我不知道如何立即执行。

Can I use the previous syntax of select INTO and also use INSERT INTO to do something like this?

我可以使用select INTO的先前语法,还可以使用INSERT INTO做这样的事情吗?

INSERT INTO DB2.T2(ID, Description, Enable, Code, Date) 
VALUES (index,?,1,?,GETDATE())

I'm really confused right now. I hope you can get what I'm trying to explain.

我现在真的很困惑。我希望你能得到我想要解释的东西。

Beside exporting data from one table to another, I want to insert additional data to the remaining columns. For enable I want it to be always 1, and the date the current date, The ID has to be a number and must be incrementing according to the row.

除了将数据从一个表导出到另一个表之外,我还想在其余列中插入其他数据。对于启用,我希望它始终为1,并且当前日期的日期,ID必须是数字,并且必须根据行递增。

What do you recommend to do?

你建议做什么?

Meanwhile I'll keep my self trying to find similar solutions to this problem.

与此同时,我将继续努力寻找类似解决方案。

Thanks in advance. :)

提前致谢。 :)

Additional data. I'm working by VPN on a remote connection, all I've got is some sort of the cmd interface to insert and work with the DB. There are like 20000 rows, and importing exporting by CSV using bulk seems pretty strange to me.

其他数据。我正在通过远程连接上的VPN工作,我所拥有的是某种用于插入和使用数据库的cmd接口。有20000行,使用批量导入CSV导出对我来说似乎很奇怪。

1 个解决方案

#1

Unless I'm missing something, you could use:

除非我遗漏了什么,否则你可以使用:

INSERT INTO DB2.T2(ID, Description, Enable, Code ,Date) 
SELECT ID, Description, 1 ,null,getdate() FROM DB1.T1;

When you start to think about looping, stop and revisit the problem. Almost always a better way.

当您开始考虑循环时,请停止并重新访问该问题。几乎总是更好的方式。

If ID should be incrementing and not based on what is in DB1.T1, look into making that column in DB2 IDENTITY(). If you do, then you would just leave the ID off of the insert, and SQL Server will handle that itself.

如果ID应该递增而不是基于DB1.T1中的内容,请查看在DB2 IDENTITY()中创建该列。如果这样做,那么您只需将ID从插入中删除,SQL Server将自行处理。

#1