Select into temp table

[dand11]

Is it possible to create a temp table on the fly as with the following SQL Server TSQL Script?

Select 'Some value' as Col1
into #temp
from myTable 
where primkey > 0

ALTER TABLE #temp ADD RowNum IDENTITY(1,1)

 

[dand11]

I have found some info and I'm trying the following but getting an error. any help is deeply appreciated:

BEGIN 
							Create temporary table tmp_Veh as 
							SELECT  'OFFENSE'  as TblSource
								, v.Primary as UNIQUEKEY
								, v.VIN as VIN
								, v.YEAR as VYEAR
								, v.MAKE as MAKE
								, v.Model as VModelText
								, v.License as TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr + ' ' + v.Bot_clr as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							
							 FROM Vehicle v inner join incident i on v.CN = i.CN
                 					 INNER JOIN charges c on v.CN = c.CN 
						 WHERE UPPER(v.Lic_Stat) = ?  
							UNION
							SELECT  'TRAFFIC'  as TblSource
								, v.Primary	as UNIQUEKEY
								, v.VIN as	VIN
								, v.YEAR as 	VYEAR
								, v.MAKE as	MAKE
								, v.Model as	VModelText
								, v.License as	TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr + ' ' + v.Bot_clr as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							 
							 FROM Vehicle v inner join incident i on v.CN = i.CN
                 					 INNER JOIN accident c on v.CN = c.ComplaintNumber 
						 WHERE LTRIM(v.Lic_Stat) = ?   
							UNION
							SELECT  'UCM'  as TblSource
								, v.Primary as UNIQUEKEY
								, v.VIN as VIN
								, v.YEAR as VYEAR
								, v.MAKE as MAKE
								, v.Model as VModelText
								, v.License as TAG
								, v.Lic_Stat as	STATE
								, v.Top_clr + ' ' + v.Bot_clr as COLORSOLID
							 	, ' ' as ORIGINATINGORG
								, 1 as TotalCount
							 
							 FROM Vehicle v inner join incident i on v.CN = i.CN
                 					 INNER JOIN ticket t on i.CN = t.CaseNumber 
						 WHERE LTRIM(v.Lic_Stat) = ?    
							ALTER TABLE tmp_Veh ADD Column RowNum AUTO_INCREMENT 
					
							Select * from tmp_Veh where RowNum > 0 and RowNum < 10
					
							COMMIT

 

[dand11]

Thats actually a MYSQL query the error I'm getting is below:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create temporary table tmp_Veh as 
							SELECT  'OFFENSE'  as TblSource
				' at line 2

 

[dand11]

The question mark is replaced with: 'FL'

 

[r937]

judging by where the error occurred, you might wish to remove the BEGIN keyword

don't forget to separate your individual SQL statements with semi-colons

r937.com | rudy.ca

 

[dand11]

Should I have the semi colons eve thought there are "UNION" keywords between them?

 

[r937]

no, the semi-colon comes at the end of the SELECTs, before the ALTER, and another after the ALTER

and that ALTER doesn't look too healthy, either

r937.com | rudy.ca