1.重建数据库日志
backup log jbERP with no_log
dbcc shrinkdatabase('jbERP',0,notruncate)
2.查找重复记录
select bm from sell group by bm having count(*)>1
3.数据库备份到远程电脑,通过sql代理--作业
declare @filename nvarchar(100)
set @filename='//192.168.0.2/YiFeiBACKUP/data'+convert(char(10),getdate(),112)
print @filename
backup database jxERP to disk=@filename with noinit,nounload,
name=N'备份',noskip,stats=10,noformat
4.表中一字段ID,数值长度大于等于10,,寻找前八个数值相同的记录。。。。。
SELECT *
FROM INVMB AS t
WHERE EXISTS(SELECT *
FROM INVMB
WHERE LEFT(MB001,8)=LEFT(t.MB001,8)
AND MB001!=t.MB001) order by MB001 desc
5.字符串拆分
declare @s varchar(1000)
set @s='1,23,a'
declare @sql varchar(8000)
set @sql='select com='''+replace(@s,',',''' union all select ''')
+''''
exec(@sql)
6.更新a表的MB006等于b表的nam字段
update a
set MB006=b.NAM
from
INVMB a
join
ALEX_INV b on a.MB001=b.NUM
backup log jbERP with no_log
dbcc shrinkdatabase('jbERP',0,notruncate)
2.查找重复记录
select bm from sell group by bm having count(*)>1
3.数据库备份到远程电脑,通过sql代理--作业
declare @filename nvarchar(100)
set @filename='//192.168.0.2/YiFeiBACKUP/data'+convert(char(10),getdate(),112)
print @filename
backup database jxERP to disk=@filename with noinit,nounload,
name=N'备份',noskip,stats=10,noformat
4.表中一字段ID,数值长度大于等于10,,寻找前八个数值相同的记录。。。。。
SELECT *
FROM INVMB AS t
WHERE EXISTS(SELECT *
FROM INVMB
WHERE LEFT(MB001,8)=LEFT(t.MB001,8)
AND MB001!=t.MB001) order by MB001 desc
5.字符串拆分
declare @s varchar(1000)
set @s='1,23,a'
declare @sql varchar(8000)
set @sql='select com='''+replace(@s,',',''' union all select ''')
+''''
exec(@sql)
6.更新a表的MB006等于b表的nam字段
update a
set MB006=b.NAM
from
INVMB a
join
ALEX_INV b on a.MB001=b.NUM