sql 导入文件

zai  SQLQuery4.sql 文件中

--BULK INSERT Table_1 from 'D:\aaaa#azzz.txt' with(fieldterminator=',',rowterminator='\n') //以逗号分开

BULK INSERT Table_1 from 'D:\aaaa#azzz.txt' with(fieldterminator='',rowterminator='\n') --不以逗号隔开

use test
select *  from Table_1  where id in (1,2,3,4,5)
--delete Table_1
select *  from Table_1  where id not in (1,2,3,4,5)
select id from Table_2 where id not in (select id from Table_1)


use test
DECLARE @i INT
set @i=0;
DECLARE @id INT
SET @id=0
WHILE @i<10000      --100w为你要执行插入的次数
BEGIN
INSERT INTO Table_1(A)  --xx为表名
VALUES  (@i+1) 
SET @i=@i+1
END


declare @i int   
set @i=0
while @i<5
begin
    update Student set demo = @i+5 where Uid=@i
    set @i=@i +1 
end


USE [www_4x_com] insert into [dbo].[w_4x]([w_4xAbc]) select  'aaaa' where not exists(SELECT * FROM [dbo].[w_4x] where [w_4xAbc] ='aaaa')


--insert into Table_2(id) values(2)
--insert into Table_2(id) values(3)
--insert into Table_2(id) values(4)
--insert into Table_2(id) values(5)

USE [www_4x_com]
GO
SELECT count([w_4xId])FROM [dbo].[w_4x] --141971---147772 = 5801行


select [w_4xAbc] from [dbo].[w_4x] group by [w_4xAbc] having count(*)>1
--select * from w_4x
GO

select * from student
order by sno  
offset 10 rows
fetch next 10 rows only ;

select  top 1  * from  [dbo].[w_4x] where [w_4xAbc] between 'aaaa' and 'bzzz' order by [w_4xAbc] desc --查询最后一个
select * from  [dbo].[w_4x] where [w_4xAbc] between 'aaaa' and 'bzzz' order by [w_4xAbc] desc

select COUNT(*) from  [dbo].[w_4x] where [w_4xAbc] between 'aaaa' and 'bzzz' and w_4xResult like '%数量限制%'

select * from  [dbo].[w_4x] where [w_4xAbc] between 'aaaa' and 'bzzz' and w_4xResult like '%数量限制%' order by [w_4xAbc]   
offset (0*10) rows
fetch next 10 rows only ;  --offset A rows ,将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。

--插入数据
use test
insert into Table_1(A,B) 
select  'B','B'
where not exists(select * from Table_1 where A='B')  --先查询在插入 --返回受影响的行

use www_4x_com
insert into [dbo].[w_4x]([w_4xAbc],[w_4xResult]) select  'ppva','.com' where not exists(SELECT COUNT([w_4xId]) FROM [dbo].[w_4x] where [w_4xAbc] ='ppva' and [w_4xType]='.com')  --先查询在插入 --返回受影响的行

USE [www_4x_com] 
insert into [dbo].[w_4x]([w_4xAbc],[w_4xResult],[w_4xType])
 select  'vmas','200 ok,a:;b:vmas.com;c:;d:','.com' where not exists(SELECT * FROM [dbo].[w_4x] where [w_4xAbc] ='vmas' and [w_4xType]='.com')

 USE [www_4x_com] 
 insert into [dbo].[w_4x]([w_4xAbc],[w_4xResult],[w_4xType])
  select  'dgkn','200 ok,a:;b:dgkn.com;c:;d:','.com' where not exists(SELECT COUNT([w_4xId]) FROM [dbo].[w_4x] where [w_4xAbc] ='dgkn' and [w_4xType]='.com')

--UPDATE [dbo].[w_4x] SET [w_4xType] = '.com',[w_4xAbc] = 'dgkn' WHERE  w_4xId=114805



select * from Table_1

--查询数据

select * from  [dbo].[w_4x] where [w_4xAbc] ='aaaa'
select [w_4xAbc] from [dbo].[w_4x] group by [w_4xAbc] having count(*)>1

select *  from [dbo].[w_4x] Order BY [w_4xAbc]

USE [www_4x_com] SELECT COUNT([w_4xId]) FROM [dbo].[w_4x] where [w_4xAbc] ='ppva' and [w_4xType]='.com'



if not exists(select * from  [dbo].[w_4x] where [w_4xAbc] ='aaab') 
begin
select * from  [dbo].[w_4x] where [w_4xAbc] between 'aaaa' and 'bbbb' order by [w_4xAbc] desc --查询最后一个
end
go



Create PROC [dbo].[proc_XXXXX]   
@xxx varchar(50)
AS
BEGIN
BEGIN TRAN
BEGIN TRY 
    .....................插入
    COMMIT TRAN 
END TRY

BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK TRAN
END CATCH
END
GO