SQL Server，在 TSQL 中创建临时表时出现问题

发布于 2024-11-09 14:28:04 字数 295 浏览 5 评论 0原文

您好，当我执行以下 TSQL 时，我收到以下错误消息。但是SQL语法没有问题吧？

create table #tb ([t1] tinyint, [t2] varchar(50))
insert into #tb values
    (1, 'a'), 
    (2, 'b')

消息 102，级别 15，状态 1，第 3 行 “,”附近的语法不正确。

SQL 查询窗口中没有其他内容。运行 SQL Server 2005。

原文

Hi when i execute the following TSQL, i get the error message below. But there is nothing wrong with the SQL syntax is there?

create table #tb ([t1] tinyint, [t2] varchar(50))
insert into #tb values
    (1, 'a'), 
    (2, 'b')

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

There is nothing else in the SQL query window. Running SQL Server 2005.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尹雨沫 2024-11-16 14:28:04

正如 jmoreno 提到的，VALUES (), () 语法受 SQL Server 2008+ 支持，但您将其标记为 SQL Server 2005。

使用：

CREATE TABLE #tb ([t1] tinyint, [t2] varchar(50))

INSERT INTO #tb 
SELECT 1, 'a'
UNION ALL
SELECT 2, 'b'

也可以使用 < a href="http://msdn.microsoft.com/en-us/library/ms188029.aspx" rel="noreferrer">选择 ...子句，但临时表不能已经存在：

SELECT *
  INTO #tb
  FROM (SELECT CAST(1 AS TINYINT) AS t1, 
               CAST('a' AS VARCHAR(50)) AS t2
        UNION ALL
        SELECT 2, 'b') x

As jmoreno mentions, theVALUES (), () syntax is SQL Server 2008+ supported but you tagged this as SQL Server 2005.

Use:

CREATE TABLE #tb ([t1] tinyint, [t2] varchar(50))

INSERT INTO #tb 
SELECT 1, 'a'
UNION ALL
SELECT 2, 'b'

It's also possible to do this in a single query, using the SELECT ... INTO clause, but the temp table can't already exist:

SELECT *
  INTO #tb
  FROM (SELECT CAST(1 AS TINYINT) AS t1, 
               CAST('a' AS VARCHAR(50)) AS t2
        UNION ALL
        SELECT 2, 'b') x

回复收藏 0 原文

摇划花蜜的午后 2024-11-16 14:28:04

试试这个：

create table #tb ([t1] tinyint, [t2] varchar(50));
insert into #tb ([t1], [t2])
values(1, 'a'), (2, 'b')

您需要指定要插入的列。

//编辑

抱歉，SQL 2005 语法如下。它并不那么优雅。

CREATE TABLE #tb ([t1] TINYINT, [t2] VARCHAR(50));
INSERT INTO #tb
SELECT 1, 'a'
UNION ALL
SELECT 2, 'b'

Try this:

create table #tb ([t1] tinyint, [t2] varchar(50));
insert into #tb ([t1], [t2])
values(1, 'a'), (2, 'b')

You need to specify the columns that you're inserting into.

//EDIT

Sorry, SQL 2005 syntax below. It's not nearly as elegant.

CREATE TABLE #tb ([t1] TINYINT, [t2] VARCHAR(50));
INSERT INTO #tb
SELECT 1, 'a'
UNION ALL
SELECT 2, 'b'

回复收藏 0 原文

灵芸 2024-11-16 14:28:04

你说你使用的是 SQL 2005，但 VALUES (), () 语法直到 2008 年才实现。

回复收藏 0 原文

寂寞陪衬 2024-11-16 14:28:04

看起来您正在尝试插入两行，因此您需要插入第一行，然后插入第二行，而不是尝试将其全部压缩为一行：

CREATE TABLE #tb ([t1] TINYINT, [t2] VARCHAR(50));
INSERT INTO #tb([t1],[t2]) VALUES (1, 'a'); --row 1
INSERT INTO #tb([t1],[t2]) VALUES (2, 'b'); --row 2

--see if it worked
SELECT [t1], [t2] 
FROM #tb

--clean up the temp table when you're done
DROP TABLE #tb

Looks like you're trying to insert two rows, so you need to insert the first row and then the second instead of trying to squeeze it all into one:

CREATE TABLE #tb ([t1] TINYINT, [t2] VARCHAR(50));
INSERT INTO #tb([t1],[t2]) VALUES (1, 'a'); --row 1
INSERT INTO #tb([t1],[t2]) VALUES (2, 'b'); --row 2

--see if it worked
SELECT [t1], [t2] 
FROM #tb

--clean up the temp table when you're done
DROP TABLE #tb

回复收藏 0 原文