none
我想实现这个功能,应该怎样写这个sql。 RRS feed

  • 问题

  • 如果发现在表中包含identity栏位,那么就将identity_insert设置为on。

    CREATE PROCEDURE usp_identityinsert
    @dbname CHAR(100),
    @tablename CHAR(100)
    IF EXISTS (SELECT 1 FROM dbo.syscolumns WHERE ID=OBJECT_ID(@dbname.dbo.@tablename) AND status = 0x80)
    BEGIN
    SET IDENTITY_INSERT @dbname.dbo.@tablename on 
    END

    我这样写根本就不能work,

    因为以前没做过开发,所以也不知道到底应该怎样来写这个东西,只是知道思路。但是就是写不出来。

    可以帮下忙吗?

    多谢


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年7月15日 7:18

答案

  • USE [MetroProduct]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_identityinsert]    Script Date: 07/18/2011 14:19:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[sp_identityinsert](@tablename varchar(30),@switch varchar(5))
    AS
    DECLARE @_sqls VARCHAR (1000)
    SELECT @_sqls=N'
    IF EXISTS (SELECT 1 FROM dbo.syscolumns WHERE ID=OBJECT_ID(''dbo.@tablename@'') AND status = 0x80)
    BEGIN
    SET IDENTITY_INSERT dbo.@tablename@ @switch@
    END
    '
    SET @_sqls = REPLACE(@_sqls, '@tablename@', @tablename)
    SET @_sqls = REPLACE(@_sqls, '@switch@', @switch)

    --PRINT @_sqls
    EXEC (@_sqls)

     

    这是我写的存储过程.

    环境是sqlserver 2000


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.


    大哥您没注意到我贴的连结吗Orz

    http://support.microsoft.com/kb/281200/zh-cn

     您在存储过程只写Set Identity_Insert On,在呼叫时,Identity_Insert会失效

    依您的需求来看,我想只好多写几份存储过程

    Set Identity_Insert ON
    
    Insert into A
    Select * from B
    Set Identity_Insert OFF
    
    如此处理并排程它
    Shadowと愉快なコード達
    Please correct me if my concept is wrong
    2011年7月19日 13:53

全部回复

  • Hi:

    试试看这样

    CREATE PROCEDURE usp_identityinsert
    (
    @dbname VARCHAR(100),
    @tablename VARCHAR(100)
    )
    AS
    BEGIN
    
     Declare @s varchar(MAX)
     Set @s = 'IF EXISTS (SELECT 1 FROM dbo.syscolumns WHERE ID=OBJECT_ID('+@dbname+'.dbo.'+@tablename+') AND status = 0x80)
      Begin
      SET IDENTITY_INSERT '+@dbname+'.dbo.'+@tablename+' on 
      End';
     Execute(@s)  
    
    END
    

    参考: http://msdn.microsoft.com/en-us/library/ms187926.aspx


    Shadowと愉快なコード達
    Please correct me if my concept is wrong

    2011年7月15日 7:49
  • Create Procedure usp_identityinsert
    (
    @dbName VARCHAR(100),
    @schemaName varchar(100),
    @tableName VARCHAR(100),
    @insertSql Varchar(MAX)
    )
    AS
    BEGIN
    
     Declare @s varchar(MAX)
     if(OBJECTPROPERTY(OBJECT_ID(@dbName +'.'+@schemaName +'.'+@tableName ,'U'),'TableHasIdentity') > 0)
     Begin
      Set @s = 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' ON;'
       Set @s = @s + @insertSql 
       Set @s = @s + 'SET IDENTITY_INSERT ' + @dbName+'.'+@schemaName+'.'+@tableName+ ' OFF;'
       Execute(@s) 
    
     End
    
    
    END
    
    
    执行示例:Exec usp_identityinsert 'MyDB','dbo','tb_test','Insert into tb_test (uid,title) values (1234,''Shadow'')'
    参考:

    http://msdn.microsoft.com/zh-tw/library/ms176105(v=sql.90).aspx


    Shadowと愉快なコード達
    Please correct me if my concept is wrong

    2011年7月15日 10:28
  • 我现在的需求是这样,由于需要定期移动数据,但是有些表中存在identity栏位。所以在insert数据的时候需要set identity_insert on。

    因为是循环的插入数据,所以不能像LS那样直接写一个'Insert into tb_test (uid,title) values (1234,''Shadow'')'这种。

    于是就写了一个存储过程。

    但是执行的时候发现一个问题。

    sp_identityinsert @tablename='test',@switch='on'
    INSERT INTO test(id,name) VALUES(12,'Wison')

    这样根本就不行。

    但是就算我直接写的话,又不能set identity_insert [DBLink].DBname.dbo.tablename on。

    那我到底要怎么才能当遇到有identity栏位的时候 就打开 identity_insert呢?

    除非在数据的目的端执行这个存储过程,但是抓取数据的时候,是通过DBLink抓取远程的数据,那么会比较慢。所以希望能在数据源端实现这个功能。


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年7月18日 6:00
  • USE [MetroProduct]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_identityinsert]    Script Date: 07/18/2011 14:19:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[sp_identityinsert](@tablename varchar(30),@switch varchar(5))
    AS
    DECLARE @_sqls VARCHAR (1000)
    SELECT @_sqls=N'
    IF EXISTS (SELECT 1 FROM dbo.syscolumns WHERE ID=OBJECT_ID(''dbo.@tablename@'') AND status = 0x80)
    BEGIN
    SET IDENTITY_INSERT dbo.@tablename@ @switch@
    END
    '
    SET @_sqls = REPLACE(@_sqls, '@tablename@', @tablename)
    SET @_sqls = REPLACE(@_sqls, '@switch@', @switch)

    --PRINT @_sqls
    EXEC (@_sqls)

     

    这是我写的存储过程.

    环境是sqlserver 2000


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年7月18日 6:15
  • USE [MetroProduct]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_identityinsert]    Script Date: 07/18/2011 14:19:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    ALTER PROCEDURE [dbo].[sp_identityinsert](@tablename varchar(30),@switch varchar(5))
    AS
    DECLARE @_sqls VARCHAR (1000)
    SELECT @_sqls=N'
    IF EXISTS (SELECT 1 FROM dbo.syscolumns WHERE ID=OBJECT_ID(''dbo.@tablename@'') AND status = 0x80)
    BEGIN
    SET IDENTITY_INSERT dbo.@tablename@ @switch@
    END
    '
    SET @_sqls = REPLACE(@_sqls, '@tablename@', @tablename)
    SET @_sqls = REPLACE(@_sqls, '@switch@', @switch)

    --PRINT @_sqls
    EXEC (@_sqls)

     

    这是我写的存储过程.

    环境是sqlserver 2000


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.


    大哥您没注意到我贴的连结吗Orz

    http://support.microsoft.com/kb/281200/zh-cn

     您在存储过程只写Set Identity_Insert On,在呼叫时,Identity_Insert会失效

    依您的需求来看,我想只好多写几份存储过程

    Set Identity_Insert ON
    
    Insert into A
    Select * from B
    Set Identity_Insert OFF
    
    如此处理并排程它
    Shadowと愉快なコード達
    Please correct me if my concept is wrong
    2011年7月19日 13:53