pb9 SQLserver2k by first building the table A_Imp_Insert_Sqlif exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [A_Imp_Insert_Sql]') and OBJECTPROPERTY (id, N'IsUserTable ') = 1) drop table [dbo ]. [A_imp_insert_sql] GO
Create Table [DBO]. [A_IMP_INSERT_SQL] ([INSERTSQL] [VARCHAR] (8000) Collate Chinese_PRC_CI_AS NULL, [FLAG] [varchar] (50) collate chinese_prc_ci_as null) ON [PRIMARY] GO
Built storage procedure proc_insert
CREATE proc proc_insert (@ls_server varchar (256), @ ls_user varchar (20), @ ls_pwd varchar (20), @ tablename varchar (30), @ ls_colName varchar (20), @ ls_operator varchar (10), @ str_bookid varchar ( 100)) AS Begin Set NoCount on Declare @Sqlstr Varchar (8000) Declare @ Sqlstr1 Varchar (8000) Declare @ SQLSTR2 VARCHAR (8000)
- to delete pb Table .-- exec ( 'truncate table A_Imp_Insert_Sql') - insert into A_Imp_Insert_Sql - A_CLS_Tempif @tablename = 'A_CLS_Temp' begin-- a separate treatment, special treatment select @ sqlstr = 'select' 'INSERT chineselibraryclass' endelse begin select @ SQLSTR = 'SELECT' 'INSERT' @ TableName End
- select @ sqlstr = 'select' 'insert' @ tablename select @ sqlstr1 = '' select @ sqlstr2 = '(' select @ sqlstr1 = 'values (' ' ' select @ sqlstr1 = @ sqlstr1 col ' ' ',' ' ', @ SQLSTR2 = @ SQLSTR2 Name ',' from (select case - when a.xtype = 173 TEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' ELSE ' 'Convert (VARCHART (VARCHAR (VARCHAR (4), A.Length * 2 2) '), ' a.name ') ' ' end 'when a.xtype = 104 THEN' CASE WHEN ' a.Name ' Is Null Then '' Null '' Else ' ' Convert (varchar (1), ' A.NAME ') ' ' end 'when a.xtype = 175 TEN' CASE WHEN ' A.NAME 'Is Null Then' 'Null' 'Else' '' '' '' '' ' ' Replace (' a.name ', '' '' '' ',' '' '' '' '' '' '' '' End 'When A.XTYPE = 61 THEN' CASE WHEN ' A.NAME ' Is Null Ten '' Null ' Else ' ' '' '' '' ' ' Convert (VARCHAR (23), ' A.NAME ', 121) ' ' '' '' '' '' End '
When a.XTYPE = 106 THEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null' 'Else' 'Convert (Varchar (' Convert (varchar (4), A.XPREC 2) ') , ' a.Name ') ' ' end 'when a.xtype = 62 THEN' CASE WHEN ' A.NAME ' IS NULL THEN '' NULL 'ELSE' 'Convert (varchar (23),' a.Name ', 2)' 'end' when a.xtype = 56 THEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null' 'Else' 'Convert (Varchar (11),' a.Name ')' 'end' when a.xtype = 60 TEN 'CASE WHEN' A.NAME 'Is Null Then' 'Null' 'Else' 'Convert (VARCHAR (22),' A .name ')' 'end' when a.xtype = 239 TEN 'CASE WHEN' A.NAME 'is NULL THEN' '' '' '' ELSE ' ' '' '' '' ' ' Replace (' a.name ', '' '' '' ',' '' '' '' '') ' ' '' '' '' 'End' When A .XTYPE = 108 TEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (Varchar (' Convert (varchar (4), A.XPREC 2) '),' a.Name ')' 'end' when a.xtype = 231 TEN 'CASE WHEN'
A.NAME 'Is Null Then' 'Null' 'Else' '' '' '' '' ' ' Replace (' a.name ', '' '' '' ',' '' '' '' '' '' '' '' 'End' when a.xtype = 59 TEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' ' Else ' ' Convert (VARCHAR (23), ' A.NAME ', 2) ' ' end 'when a.xtype = 58 TEN' CASE WHEN ' A.NAME ' Is Null Ten '' Null '' Else ' ' '' '' '' ' ' Convert (VARCHAR (23), ' A.NAME ', 121) ' ' '' '' '' '' End ' When a.XTYPE = 52 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (12),' A.NAME ')' 'End' When A .XTYPE = 122 THEN 'CASE WHEN' A.NAME 'Is Null Ten' 'Null' 'Else' 'Convert (varchar (22),' a.name ')' End 'When A.XTYPE = 48 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' 'NULL' ELSE ' ' Convert (varchar (6), ' a.name ') ' ' end '- WHEN A.XTYPE = 165 TEN 'CASE WHEN' A.NAME 'Is Null Then' '
NULL '' ELSE ' ' Convert (VARCHAR (VARCHAR (4), A.LENGTH * 2 2) '),' a.name ')' 'end' when a.xtype = 167 THEN 'CASE WHEN' A.NAME 'IS NULL THEN' '' '' '' '' '' '' '' '' ' ' Replace (' a.name ', '' '' " '', '' '' '' '' ' ' '' '' '' 'End' Else '' 'Null' 'End As Col, A.COLID, A .name from syscolumns a where a.id = Object_id (@tablename) and a.XTYPE <> 189 and A.XTYPE <> 34 and A.XTYPE <> 35 and A.XTYPE <> 36) T ORDER BY Colid Declare @ SQLSTR_B VARCHAR (8000) SELECT @ SQLSTR = @ SQLSTR LEFT (@ SQLSTR2, LEN (@ Sqlstr2) -1) ')' Left (@ Sqlstr1, Len (@ SQLSTR1) -3) ')', ' '' @ tablename '' 'from' @ tablename - select @ sqlstr = @ sqlstr 'where CATLRID =' '' @ str_bookid '' '' select @ sqlstr = @ sqlstr 'where' @ ls_colName @ ls_operator '' '' @ STR_BOOKID '' '---- Print @Sqlstr Select @ SQLSTR_B =' INSERT INTO A_IMP_INSERT_SQL (InsertSql, Flag) (' @ SQLSTR ') '- Print @sqlstr_b
--exec (@sqlstr_b) - Export to a separate stored procedure - Declare @BCPSTR VARCHAR (1000) - set @BCPSTR = 'bcp bookonline_test..A_IMP_INSERT_SQL' - set @BCPSTR = @ bcpstr 'OUT' 'C: /' @ Tablename '. txt' '-c -s' @ ls_server ' -u ' @ ls_pwd ' - exec master..xp_cmdshell @BCPSTRSET NOCOUNT OFF ENDGO
Built storage procedure proc_imp_text
CREATE PROCEDURE proc_imp_text (@ls_dbname varchar (50), @ ls_server varchar (200), @ ls_user varchar (200), @ ls_pwd varchar (200), @ ls_filepathname varchar (500), @ ls_tabname varchar (50)) ASDeclare @bcpstr varchar (1500) Begin
set @bcpstr = 'bcp "select insertsql from' @ ls_dbname '.. A_Imp_Insert_Sql where flag =' '' @ ls_tabname '' '" queryout' @ ls_filepathname '-w -S' @ ls_server '-U' @ l_user '-p' @ ls_pwd ''
Exec master..xp_cmdshell @BCPSTREND
Go unknown connection: gutengcom@hotmail.com
