SQL Server – COPY TABLE from another SQL Server

To copy a table from another SQL Server Instance is easy. Create a Linked Server on the destination server and reference the Linked Server in the SELECT statement.

1) Create linked server

/****** Object:  LinkedServer [SERVER1\SQL_SERVER_INSTANCE_1]    Script Date: 02/02/2012 09:29:33 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\SQL_SERVER_INSTANCE_1', @srvproduct=N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SERVER1\SQL_SERVER_INSTANCE_1',@useself=N'False',@locallogin=NULL,@rmtuser=N'MY_MIGRATION',@rmtpassword='########'
GO

 2)   On the destination server

 

--SELECT * into  from   

--Example:  Using the Linked server

SELECT * INTO my_test_table_copy FROM [SERVER1\SQL_SERVER_INSTANCE_1].MY_DB.dbo.my_test_table

 

See Also

SQL SELECT INTO

SQL Server Linked Servers and User permissions

SQL Server - Generate SQL INSERT from Excel

Generate SQL Server INSERT statements with Excel Concatenate.

A developer requested  access to a Production database. The details were to COPY and PASTE from a Excel spreadsheet into Access , which then updated SQL Server.  Upon asking him , why he used this method , he said “It’s the way I always insert this data to the lookup table”.

There are better ways . One way , is to use the “Concatenate” function in Excel.  It concatenates the ascii portions of the statement, adding single quotes where relevant.

In this example , there are four rows with the values in col A and B.  Create the formula for the first line, the copy to  the rest of the lines.

Copy and Paste the the INSERT statements directly into SQL Server Management Studio or create a text file.

See Also

Powershell , Excel charts and data presentation

Excel Connect to SQL Server

SQL Server – Modifying Production Data Good Practise

Q. “I need to modify Production Data directly on a Production database. What are some steps I can follow to ensure rollback is possible? Also, some steps to ensure I can capture mistakes before the data becomes invalid “

 A. Here are some suggestions

1. Take a Log Backup before the change – giving you a restore point

2. Sit with another DBA . They can verify what you’re doing is correct. This is particuarly useful if late at night

3. Create a table backup , before beginning.

4. Create  a transaction. Confirm the results and record counts before you commit the changes. If it’s part of  larger process consider some “human validation”. Gives you a chance to rollback if necessary.

5.If the Update source is  from another database or a different database platform , such as DB2 , create a staging database. Place data in the staging database, validate the data and then commit the UPDATE

6. If using audit columns, set the updated datetime. Use unique characters to indicate the rows updated in this particular task.  

See Also

BACKUP failed to complete the command BACKUP LOG

Disable Index and Rebuild Index for Updates on very large tables

 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Arithmetic overflow error and isdate sql

Executing a t-sql statement with a convert function

 convert(datetime, my_date_column)            ,           

 threw an arithmetical overflow error with the message :

Message
 Arithmetic overflow error converting expression to data type datetime. [SQLSTATE 22003] (Error 8115)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.

 

Use  the ISDATE function  , to identify invalid dates (or valid dates). Clean up as necessary.

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

select seq_number
from   dbo.STAGING_TABLE 
where  ISDATE(my_date_column ) = 0
and  my_date_column  

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Disable Indexes and Rebuild Indexes dynamically

To generate the ALTER INDEX..DISABLE  and  the ALTER INDEX..REBUILD statements for all nonclustered indexes for a single table use the following sql statements. 

Copy and Paste the statements , execute on the database. I’ve included the URL column  for easy click through to some notes on the code

1) Generate the ALTER INDEX..DISABLE statements

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON [' + sys.objects.name + '] DISABLE;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-disable-index.html' as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 

2) Generate the ALTER INDEX..REBUILD statements

 

SELECT  
'ALTER INDEX [' +  sys.indexes.name + '] ON ' + sys.objects.name + ' REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
;' +CHAR(13)+CHAR(10) as EXECUTE_STATEMENT,
'http://www.sqlserver-dba.com/2012/01/sql-server-rebuild-index.html'> as URL

FROM    sys.indexes         JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id 
WHERE   sys.indexes.type_desc = 'NONCLUSTERED'   
AND sys.objects.type_desc = 'USER_TABLE' 
AND sys.objects.name = 'MyTable'

 Related Posts

SQL SERVER REBUILD INDEX

SQL SERVER DISABLE INDEX