SQL Server Performance

Export Table data into text file formate

Discussion in 'General Developer Questions' started by mpolaiah, Apr 3, 2009.

  1. mpolaiah New Member

    Hai all,
    my database all tables data is store. but i want into text formate like...
    Create table Emp(Empno int,Ename varchar(100),Job varchar(100),Mgr int,HireDate datetime,Sal Money,Comm Money,Deptno int)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1001, 'Scott', 'President', NULL, '01/01/78', 5000, NULL, 10)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1002, 'Clark', 'Manager', 1001, '01/01/78', 4000, NULL, 10)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1003, 'Smith', 'Manager', 1001, '01/01/78', 3500, 500, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1004, 'Vijay', 'Manager', 1001, '01/01/78', 4000, NULL, 30)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1005, 'Ajay', 'Salesman', 1003, '02/04/79', 3000, 300, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1006, 'Satish', 'Salesman', 1003, '02/08/78', 4000, 600, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1007, 'Venkat', 'Salesman', 1003, '04/15/78', 3300, 0, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1008, 'Vinod', 'Clerk', 1003, '01/15/78', 2400, NULL, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1009, 'Suneel', 'Clerk', 1004, '05/12/83', 2000, NULL, 30)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1010, 'Srinivas', 'Analyst', 1004, '03/01/79', 3400, NULL, 30)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1011, 'Prakash', 'Analyst', 1004, '03/01/79', 3600, NULL, 30)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1012, 'Madan', 'Analyst', 1004, '01/09/81', 3100, NULL, 30)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1013, 'Ravi', 'Clerk', 1002, '01/06/78', 1800, NULL, 10)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1014, 'Raju', 'Clerk', 1005, '06/01/78', 2300, NULL, 20)
    Insert into Emp (Empno, Ename, Job, Mgr, HireDate, Sal, Comm, Deptno) Values(1015, 'Ramesh', 'Clerk', 1011, '08/22/78', 2500, NULL, 30)
    i want all tables at time .
    regards
    pols
  2. sqlemail@yahoo.com New Member

    If the purpose is only to EXPORT & then IMPORT, why dont you try BCP OUT / BCP IN feature within SQL Server
  3. sqlemail@yahoo.com New Member

    Here is the one that I used long time back, hope it works for you, please feel free to tweak to make it work for your project :)
    ---------------------------------------------------------------------
    -- Generate Insert Statements Easily
    -- http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=36
    -- File: Generate_Insert_TableColumnValues.sql
    ---------------------------------------------------------------------set nocount on
    Drop
    PROC sp_DataAsInsCommandgoset
    quoted_identifier offgoCREATE
    PROC sp_DataAsInsCommand (@TableList varchar (8000))AS
    SET NOCOUNT ON
    DECLARE
    @position int, @exec_str varchar (2000), @TableName varchar (50)DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint
    SELECT
    @TableList = @TableList + ','SELECT
    @IsIdentity = 0SELECT
    @position = PATINDEX('%,%', @TableList)WHILE
    (@position <> 0)
    BEGIN
    SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
    SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList),'')
    SELECT @position = PATINDEX('%,%', @TableList)
    SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR ' + 'SELECT a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id = b.id and b.name = ''' + @TableName + ''''
    EXEC (@exec_str)OPEN fetch_cursor
    FETCH fetch_cursor INTO @name, @xtype, @statusIF (@status & 0x80) <> 0
    BEGINSELECT @IsIdentity = 1
    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
    SELECT 'GO'
    ENDSELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "
    Select ' -- The table name is: ' + @TableName
    --text or ntext
    IF (@xtype = 35) OR (@xtype = 99)
    SELECT @exec_str = @exec_str + '''"None yet"'''
    ELSE
    --image
    IF (@xtype = 34)
    SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
    ELSE
    --smalldatetime or datetime
    IF (@xtype = 58) OR (@xtype = 61)
    SELECT @exec_str = @exec_str + 'Coalesce(' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
    ELSE
    --varchar or char or nvarchar or nchar
    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
    SELECT @exec_str = @exec_str + 'Coalesce(' + '''"'' + ' + @name + ' + ''"''' + ',"null")'
    ELSE
    --uniqueidentifier
    IF (@xtype = 36)
    SELECT @exec_str = @exec_str + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"''' + ',"null")'
    ELSE
    --binary or varbinary
    IF (@xtype = 173) OR (@xtype = 165)
    SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
    ELSE
    SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' + @name + '), "null")'WHILE @@FETCH_STATUS <> -1
    BEGINFETCH fetch_cursor INTO @name, @xtype, @status
    IF (@@FETCH_STATUS = -1) BREAKIF (@status & 0x80) <> 0
    BEGINSELECT @IsIdentity = 1
    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
    SELECT 'GO'
    END
    --text or ntext
    IF (@xtype = 35) OR (@xtype = 99)
    SELECT @exec_str = @exec_str + ' + ","' + ' + ''"None yet"'''
    ELSE
    --image
    IF (@xtype = 34)
    SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0xFFFFFFFF' + '"'
    ELSE
    --smalldatetime or datetime
    IF (@xtype = 58) OR (@xtype = 61)
    SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
    ELSE
    --varchar or char or nvarchar or nchar
    IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
    SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + @name + ' + ''"''' + ',"null")'
    ELSE
    --uniqueidentifier
    IF (@xtype = 36)
    SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"''' + ',"null")'
    ELSE
    --binary or varbinary
    IF (@xtype = 173) OR (@xtype = 165)
    SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' + '"'
    ELSE
    SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(CONVERT(varchar,' + @name + '), "null")'
    ENDCLOSE fetch_cursor
    DEALLOCATE fetch_cursorSELECT @exec_str = @exec_str + '+ ")" FROM ' + @TableName
    EXEC(@exec_str)-- print (@exec_str)
    SELECT 'GO'IF @IsIdentity = 1
    BEGINSELECT @IsIdentity = 0
    SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
    SELECT 'GO'
    END
    ENDgodrop table SomeWorkTable
    goCreate
    table SomeWorkTable(c0 int identity (1,1)
    ,
    c1 int,c2 char(10)
    ,
    c3 varchar(10),
    c4 datetime)goinsert
    into SomeWorkTable (c1, c2, c3, c4) Select 1, 'Hello-1', 'Hey Buddy', GetDate()insert
    into SomeWorkTable (c1, c2, c3, c4) Select 2, 'Hello-2', 'Hey Buddy', GetDate()insert
    into SomeWorkTable (c1, c2, c3, c4) Select 3, 'Hello-3', 'Hey Buddy', GetDate()insert
    into SomeWorkTable (c1, c2, c3, c4) Select 4, 'Hello-4', 'Hey Buddy', GetDate()insert
    into SomeWorkTable (c1, c2, c3, c4) Select 5, 'Hello-5', 'Hey Buddy', GetDate()goExec
    sp_DataAsInsCommand @TableList = 'SomeWorkTable'godrop table SomeWorkTable
    go

Share This Page