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.
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
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'
You say you're using SQL 2005, but the VALUES (), () syntax wasn't implemented until 2008.
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
SELECT t.field1, t.field2 INTO #myTempTable
FROM myDB.myOwner.myTable t
WHERE ...
ORDER BY t.field1, t.field2;
-- use ##myTempTable as the name if you want your table to be GLOBAL.
