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