sql的部分总结积累

--创建表
CREATE TABLE geotable
(
id int,
shape geometry
)

--判断某表是否已经存在，若存在，则删除已有数据表
IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL
DROP TABLE dbo.SpatialTable;

--复制表结构，创建新表
--select * into junctionunion from FLOOR2_NET_JUNCTIONS where 1=2

--数据类型转换
declare @i int
set @i=11
select CAST(@i as varchar)

 

--测试replace函数使用
declare @a varchar(1000),@b varchar(1000)
set @a='FLOOR2_NET_JUNCTIONS'
--set @b='_NET_JUNCTIONS'
set @b=replace( @a,'_NET_JUNCTIONS','')
select @b

 

----获取exec得到的返回值
declare @num1 int, @num2 int, @sqls nvarchar(4000)
set @sqls='select @a=count(*),@b=count(*) from FLOOR3_NET_JUNCTIONS '
exec sp_executesql @sqls,N'@a int output,@b int output',@num1 output, @num2 output
select @num1, @num2

 

 

--

-- DECLARE @cdline geometry;

--SET @cdline = geometry::STGeomFromText('LINESTRING(30.6666 104.06202, 29.50763 106.48938)', 4326);
--select @cdline.STAsText() as 重庆_成都
--SELECT @cdline

 

--游标循环数据库所有表，并将所有的表合并为一个表。
use cctv
go
declare @name varchar(1000),@tempname varchar(1000)
declare mycursor cursor for
select name from sysobjects where type='u' and name like '%_NET_JUNCTIONS' order by name asc
open mycursor
fetch next from mycursor into @name
while @@fetch_status=0
begin

--添加各种所需要执行的语句
--

fetch next from mycursor into @name
end
close mycursor
deallocate mycursor
go