How to insert records on SQL Server identity column?

Scenario:

Insert records in SQL Server table with pre-defined primary key on an identity column. For instance, the the data ContactId to insert is from 11-50. How to insert the ContactId as my identity column value is 150 now? The assumption here is existing ContactId  in the range could be deleted for the new INSERT.

 Concepts:

Identity column on a table increment the value automatically for each record INSERT into the table. For example, the table "Contact" has an identity column defined on ContactID column. The column is defined to increase the max identity value by 1. This is done by specifying the increment value to 1.

The seed value lets you specify the identity column starting value. For instance, I would like to start all data from ContactId = 100.

The T-SQL table create statement looks like below:

Solution:

Turn the IDENTITY_INSERT ON to tell the server that you want to insert specific ID value. Perform the record insert. Enable the identity columns auto value by setting the IDENTITY_INSERT OFF.

Syntax:
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Example:

Further references:

MSDN Table Identity Column
MSDN IDENTITY_INSERT

How to describe Oracle table columns

SELECT column_name
FROM all_Tab_Columns
WHERE table_name= ''
ORDER BY column_name;

Select top 10 records in Oracle

Microsoft TSQL has select top records statement. How do you do it in Oracle PL/SQL?

TSQL:
select top(10) from tblStudents

PL/SQL:
select * from
(select IDStudent, FirstName, LAstName from tblStudents)
where rownum <10

Where to download Northwind database?

I am working with Llblgen recently. The sample code requires the SQL2000 Northwind database. It took me some time to locate it. Let share the link then:

Download Northwind database samples

The sample installs the samples on 'C:\SQL Server 2000 Sample Databases' folder.

How about just download Northwind sql script?
Here you go . Rename the file to instnwnd.sql after download.

Is the script compatible with other version of SQL server (SQL2005, SQL2008) ?
Yes. Use the instnwnd.sql. Execute the sql script in your SQL Management Studio. The script create the Northwind database schemas with sample data. At least database backup (mdf and ldf) compatibility is least of your problem.

I didn't try the mdf backup restore but did create the db in SQL2008 instance using just sql script. No problem at all.

Happy trying. :)

How to change SQL server 2008 instance name

Find out the server instance name

1. sp_helpserver
2. select @@servername

Modify the instance name

1. sp_dropserver ‘old_name’
2. go
3. sp_addserver ‘new_name’,‘local’
4. go
   

Restart the service

Oracle PL/SQL SubQuery example

The following subquery uses EXISTS method to check whether the employee work in UK site. The crucial part of the query is the use of the usr.idemployee (main query) to filter the data in the subquery.

The subquery could be selecting from any tables but the query result is appended to the main query. The subquery result doesn't have to exist in any table.


select usr.iduser, usr.name,
DECODE ((SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT dummy
FROM empworkplace wrk
WHERE usr.idemployee = wrk.idemployee
AND wrk.sitekey = 'UK'),
NULL, 0,
1
) AS fromUKSite,
from users usr.

Oracle PLSQL Cursor Example

DECLARE

CURSOR c_emp (p_dept VARCHAR2) IS
SELECT ename, salary
FROM emp
WHERE deptno = p_dept
ORDER BY ename;

r_dept DEPT%ROWTYPE;

BEGIN

OPEN c_emp (r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename, v_salary;
EXIT WHEN c_emp%NOTFOUND;

END LOOP;
CLOSE c_emp;

END;

Sqlplus command to connect to Oracle server

How to connect to oracle database via sqlplus

1. Check tns entry is valid
2. Type sqlplus /nolog
3. Connect as SYS

• connect /@ as
• E.g. connect SYS/bob@zippie.internal as SYSDBA