SQL语法：SQL Server与Teradata

I've been querying against Teradata servers with SQL Assistant for years, but now have to work with a SQL Server. I've been stumbling over my code for hours, having a hard time figuring out which pieces of syntax need to be updated.

多年来我一直在使用SQL Assistant查询Teradata服务器,但现在必须使用SQL Server。几个小时以来,我一直在绊倒我的代码,很难搞清楚哪些语法需要更新。

Does anyone know of a good resource for converting logic?

有谁知道转换逻辑的好资源?

Here's an example -- I was loading .txt data into a temp table:

这是一个例子 - 我正在将.txt数据加载到临时表中:

In Teradata, the following works:

在Teradata中,以下作品:

CREATE MULTISET TABLE USER_WORK.TABLE1 (
VAR1 CHAR(3)
,VAR2 CHAR(5)
,VAR3 DECIMAL(12,2)  )
PRIMARY INDEX (VAR1, VAR2);

In SQL Server, I was able to get the following to work:

在SQL Server中,我能够使以下工作:

CREATE TABLE #TABLE1 (
VAR1 VARCHAR(20)
,VAR2 VARCHAR(20)
,VAR3 VAR(20)  );

(Main differences: No "Multiset"; all variables read in as VARCHAR & and I couldn't get any length shorter than 20 to work; I couldn't figure out how to define a functional index)

(主要区别:没有“Multiset”;所有变量都以VARCHAR形式读入&我无法获得任何长度短于20的工作;我无法弄清楚如何定义功能索引)

Mostly wondering if there is some sort of pattern behind migrating the logic - it's painful to have to look up every single piece of failed code, and to sort out of it will actually run on SQL Server.

大多数人都想知道在迁移逻辑时是否存在某种模式 - 必须查找每一段失败的代码是很痛苦的,并且要对它进行排序实际上会在SQL Server上运行。

2 个解决方案

#1

A few points...

几点......

The # prefix in your SQL Server attempt defines a local temporary table. It's visible to your session only, and it will go away when the session ends. I think it's similar to a VOLATILE table in Teradata. Is that what you wanted?

SQL Server尝试中的#前缀定义了本地临时表。它仅对您的会话可见,并且会话结束时会消失。我认为它类似于Teradata中的VOLATILE表。那是你想要的吗?
SQL Server tables are MULTISET by default so SQL has no equivalent keyword.

SQL Server表默认为MULTISET,因此SQL没有等效的关键字。
If you were having trouble with CHAR column sizes it was most likely a syntax error elsewhere. CHAR columns can be from 1 to 8,000 characters long, using a single-byte character set.

如果您遇到CHAR列大小的问题,很可能在其他地方出现语法错误。使用单字节字符集,CHAR列的长度可以是1到8,000个字符。
SQL Server doesn't have a PRIMARY INDEX. As I understand it, the equivalent in SQL Server is a CLUSTERED index.

SQL Server没有PRIMARY INDEX。据我了解,SQL Server中的等价物是一个CLUSTERED索引。

So your exact table structure in SQL Server would be like this:

因此,您在SQL Server中的确切表结构将如下所示:

CREATE TABLE USER_WORK.TABLE1 (
  VAR1 CHAR(3)
  ,VAR2 CHAR(5)
  ,VAR3 DECIMAL(12,2));

And for the index (the name can be whatever you want):

对于索引(名称可以是你想要的任何东西):

CREATE CLUSTERED INDEX TABLE1_FOO ON USER_WORK.TABLE1(VAR1, VAR2);

#2

You can create the exact same schema in sql server as well but the syntax will be a bit different.

您也可以在sql server中创建完全相同的模式,但语法会有所不同。

I would translate your teradata table as below:

我会翻译你的teradata表如下:

CREATE TABLE TABLE1 
 ( VAR1   CHAR(3)       NOT NULL
  ,VAR2   CHAR(5)       NOT NULL
  ,VAR3   DECIMAL(12,2)  
  ,PRIMARY KEY (VAR1, VAR2)
);
GO

You can still have CHAR(3) and CHAR(5) data types for VAR1 and VAR2 columns, but you have to make them non-nullable column since they are going to be Primary key columns ( requirement in sql server).

您仍然可以为VAR1和VAR2列提供CHAR(3)和CHAR(5)数据类型,但是您必须使它们成为非可空列,因为它们将成为主键列(sql server中的要求)。

Sql server also has data type decimal(12,2) you can use it for your VAR3 column. finally the composite primary key can be part of the table definition as shows above. tar

Sql server也有数据类型decimal(12,2),你可以将它用于你的VAR3列。最后,复合主键可以是表定义的一部分,如上所示。柏油

#1