Add a group id to address book kind of data with SQL

Posted: February 23, 2009 | Author: | Filed under: SQL Server | Leave a comment
I have been given a Word document with thsousands users’ information. Each user information is separated with a blank line. I saved the file to an Excel and imported to a SQL table. In the table I want group each user data with a group id for further processing. I used a formula to add the group id in Excel:
=IF(B2="",1+D1,D1). I would like to do it in my SQL Server database. Here is what I came up with in SQL Server:
Assisgn an identity column for the table and I will check the data column ( I named it as myColumn) for the blank row to do the update.
Query:

declare @i int

set

@i=1

update

dbo.NABS_List

SET

@i= CASE WHEN myColumn is null THEN @i+1 ELSE @i END,

gid

= @i

IMEX=1 revisit and TypeGuessRows setting change to 0 (watch for performance)

Posted: February 13, 2009 | Author: | Filed under: Excel, SQL Server | 12 Comments

When we import mixed type data in a column from excel to SQL server, we may have issues with losing some data. We can set IMEX=1 to allow the incoming column allow mixed data types based on the Registry setting ImportMixedTypes to Text. However, there is a default setting for Excel to Guess Data Type of a column, the default value is 8.(The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype.) We can change this value to 0 to force Excel to check all values in the column to choose the data type for the column.

Here is the registry setting we need to check:

Type Regedit from Run… command:

Under entries

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

Change:
TypeGuessRows: 0    —-Check all column values before choosing the appropriate data type.ImportMixedTypes: Text         —-import mixed-type columns as text.

Connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\Book1.xls;Extended Properties="EXCEL 8.0;IMEX=1;HDR=YES";

–Or:

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;IMEX=1;HDR=YES;Database=C:\data\Book1.xls;’, ‘select * from [Sheet1$]’)

We are using IMEX=1, the Import Mode, in the connection string  to apply the registry setting.

PS:

The possible settings of IMEX are: 0,1,2.

(0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities))

 

 

http://support.microsoft.com/kb/194124

http://support.microsoft.com/kb/189897

Swap column using UPDATE in SQL

Posted: February 9, 2009 | Author: | Filed under: SQL Server | Leave a comment
You can swap columns in one SQL UPDATE statement. Here is a sample you can try.

DECLARE  @t  TABLE(
                   oddseqnum  INT,
                   evenseqnum INT
                   )

INSERT INTO @t
SELECT 1,
       2
UNION ALL
SELECT 3,
       4
UNION ALL
SELECT 5,
       6
UNION ALL
SELECT 7,
       8
UNION ALL
SELECT 9,
       10

SELECT *
FROM   @t

UPDATE @t
SET    oddseqnum = evenseqnum,
       evenseqnum = oddseqnum

SELECT *
FROM   @t