SQL Server Performance

Programmatically import table data T-SQL

Discussion in 'General Developer Questions' started by animalcub, Aug 13, 2007.

  1. animalcub New Member

    Hi,
    I intend to create a simple stored procedure that imports data from a excisting table (Server1.dbo.DB1.Table1) to a another table in another database (Server2.dbo.DB2.Table2).
    At the end of each day I'll run a stored procedure to import all the new rows in (Server1.dbo.DB1.Table1) to (Server2.dbo.DB2.Table2).
    Both database have an identically structure (table names, row etc)
    I need a few pointer (keywords) I can search for to help me solve this problem as I can only find code telling me to use the SELECT INTO statements which doesn't work for my problem.
    Can someone please point me in the right direction.
    Thank You,
    ac
  2. satya Moderator

    You can take help of WORKTABLEs and use bulk load features in SQL Server such as using DTS, if you have control over the process that creates the text files, it might be better to create two files—one with inserts and another with updates only. This way you can bulk insert the new rows using bulk copy and use a worktable approach for updates.
  3. animalcub New Member

    Hi satya,
    I am going to investigate the topics you suggested (i.e. WORKTABLES, bulk load).
    Just to clarify, can all the processes above be achieved using stored procedures?
    If you have any quick code snippets I'd be extremely grateful.
    Cheers
  4. satya Moderator

    Yes they can be, but to suggest to better one if you can explain what kind of processes are handling will be useful.
  5. animalcub New Member

    OK...
    I have an application that will be running on a central database server that will be updated throughout the day by many users. They will be entering data via my compan's application.
    Our clients want to be able to use our system out of hours so i have decided to install our app and a copy of SQL Server on a laptop.
    My goal is to design a simple windows application that run a T-SQL stored procedure that syncs the two databases each day.
    One of the options on my new app will be to copy all the tables on the central database server to the database on the client laptop.
    The second function will be to copy new database entries back from the client laptop to the central database server.
    I hope this makes sense.
  6. satya Moderator

    Fair enough, in this case you can take help of log shipping (manual) to perform updates between central repository and client's end (laptop). Also replication will help you to update only required tables.
    If the client's database is READONLY then simply you could schedule a manual job to backup the central database and restore on laptop on adhoc or daily basis (during evenings). This way performance and handling of those stored procedures can be avoided completely, refer through the articles section (DBA) in this regard.
  7. eilering New Member

    Below you find a stored procedure to copy data from one database to another.
    It looks for identical tables and then within the tables found, looks for identical column names.
    When found, an insrt query is created and data is copied.
    Good luckUSE
    [Tele] --the name of the database I'm inGO/****** this stored procedure will oopy data from one database to another
    it will not copy structure.
    you can use it for backup purposes
    it will check the columns and order and create its own insert statement
    it also does not check for violation of keys.
    Frans.eilering@gmx.net
    12/11/2008 06:05:12 ******/SET
    ANSI_NULLS ONGOSET
    QUOTED_IDENTIFIER OFFGOCREATE
    PROCEDURE [dbo].[__KopieerRecords]AS
    BEGIN
    SET NOCOUNT ON;
    --this SP must be stored in the destination database
    DECLARE @DatabaseSource nvarchar(100)
    DECLARE @DatabaseDestination nvarchar(100)
    --variables needed for creating a copy query
    DECLARE @StartInsertQuery nvarchar(100)
    DECLARE @ValuesInsertQuery nvarchar(MAX)
    DECLARE @ColumnNamesINsertQuery nvarchar(MAX)
    DECLARE @InsertQuery nvarchar(max)
    DECLARE @ROWCOUNT INT
    --the databases to be copied. You must have access!
    set @DatabaseSource = 'RegistFinal'
    set @DatabaseDestination = 'Tele'declare @TableName sysname
    declare @TableNameSource sysname declare @ColumnName sysname
    DECLARE @SQLString nvarchar(max)
    --find out which tables you have in the source database
    SET @SQLString=N'declare CursorListTablesFrom cursor for select TABLE_NAME FROM '+@DatabaseSource+'.INFORMATION_SCHEMA.TABLES'EXECUTE sp_executesql @SQLString
    --now you have a cursor containing all the table names in the database @DatabaseSource
    begin
    OPEN CursorListTablesFrom --you can walk through all the table names in this cursor
    fetch CursorListTablesFrom into @TableNameSource --get the first table nameWHILE @@FETCH_STATUS = 0
    begin
    print 'Working with table '+@TableNameSource--if you want to change the table names, you can do it here.
    --set @TableName = @TableNameSource
    ----The source table looks like Yentel_tabel
    ----and the destination table looks like tabel. It does not have this Yentel_ in front
    ----so just take the right piece of Yentel_tabel
    --set @TableName = right(@TableNameSource,len(@TableNameSource)-7)
    --first check if the table exists in the @DatabaseDestination (the database where this SP is stored)
    If EXISTS(
    select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_name = ''+@TableName+''
    )
    BEGIN
    --it exists
    print ' table '+@TableName + ' EXISTS in destination database'
    --then get all the column names in table @TableNameSource
    --Put the column names in a cursor CursorListColumnsSource
    SET @SQLString=N'declare CursorListColumnsSource cursor for select COLUMN_NAME from '+@DatabaseSource+'.INFORMATION_SCHEMA.COLUMNS WHERE table_name = '''+@TableNameSource+''''EXECUTE sp_executesql @SQLString
    --now I want to go through all the columns and check if they exsist in the @DatabaseDestination
    --If the column exists, add it to the InsertQuery
    SET @ColumnNamesINsertQuery=''
    SET @ValuesInsertQuery=''OPEN CursorListColumnsSource
    fetch CursorListColumnsSource into @ColumnName --get the first column nameWHILE @@FETCH_STATUS = 0
    begin print ' looking for column '+@ColumnName
    --check if the column name exists in the destination database.
    If exists (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''+@TableName+'' AND column_name=''+@ColumnName+'')
    beginprint ' column '+@ColumnName +' exists'
    --now I want to copy the data from one column to the other
    --I need a query like /*
    insert into I_Roles (Roles) select Roles FROM [Regist].[dbo].[Yentel_I_Roles]
    */
    --just for the comma separator
    IF @ColumnNamesINsertQuery=''
    BEGINSET @ColumnNamesINsertQuery=@ColumnName
    END
    ELSE
    BEGINSET @ColumnNamesINsertQuery=@ColumnNamesINsertQuery+','+@ColumnName
    END
    EndFETCH NEXT FROM CursorListColumnsSource INTO @ColumnName
    endClose CursorListColumnsSource
    DEALLOCATE CursorListColumnsSource
    --create the insert query
    set @InsertQuery='INSERT INTO '+@TableName+'('set @InsertQuery=@InsertQuery+@ColumnNamesINsertQuery
    set @InsertQuery=@InsertQuery+') select '+@ColumnNamesINsertQueryset @InsertQuery=@InsertQuery+' FROM ['+@DatabaseSource+'].[dbo].['+@TableNameSource+']'
    PRINT ''
    print 'data is being copied using 'print @InsertQuery
    PRINT ''EXECUTE sp_executesql @InsertQuery
    ENDFETCH NEXT FROM CursorListTablesFrom INTO @TableNameSource
    endend
    Close
    CursorListTablesFromDEALLOCATE
    CursorListTablesFromEND

Share This Page