Skip to main content

SELECT * INTO

We can create a table with the select command as well. General syntax to create a table with the select command is : 

      SELECT * 
       INTO <YourNewTable>
      FROM <YourTable>

While using "SELECT * INTO " command we should keep following concepts in mind.
  1. Identity Columns
    1. if using simple select operation, new table will inherit the Identity property
    2. New table will not inherit the Identity property for following cases
      1. Identity columns are listed more than one time in select list
      2. Using multiple select operations with UNION or UNION ALL clause
      3. Identity column is a part of an expression
    3. If identity column is required in destination table but not available in source result-set you can define your own identity function e.g. 

      -- -- Step 1. Create a table "Table_1" with identity column (ID)
-- CREATE TABLE TABLE_1 (ID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(20))

-- -- Step2. Insert some records
--INSERT INTO table_1(Name) values ('Ram'), ('Shyam'), ('Sohan')

---- Step3. (ERROR) Create new table "Table_2" suing "SELECT INTO" statement
---- (try adding your own Id identity )
---- IT WILL GIVE AN ERROR AS "ID" Column in Source table is already AN IDENTITY COLUMN

--SELECT IDENTITY(INT, 1,1) AS MyIdentityId
-- , ID AS ExistingId
-- , Name
--INTO Table_2
--FROM table_1

---- Step4. (SUCCESS) Create new table "Table_2" using "SELECT INTO" statement
---- try using your own identity column, with expression (cast function) in existing ID column
---- It will successfully get executed with new table "Table_2" having "MyIdentityId" as an Identity column

--SELECT IDENTITY(INT, 1,1) AS MyIdentityId
-- , CAST(ID AS int) AS ExistingId
-- , Name
--INTO Table_2
--FROM table_1

  2. Indexes, constraints & triggers will not get transferred from the source table to destination table If it's required you've to create it explicitly.

  3. Order by clause doesn't guarantee the data will get inserted in the same order

  4. If we're using any expression in select statement (e.g. some computation), it doesn't create the computed column into the destination table instead exact data will get inserted


Comments

Post a Comment

Popular posts from this blog

EFCore - Collate function

Search in SQL server query is generally case insensitive (by default or based on database level collation). Suppose we have an employees table with a row having first-name column value as "My-First-Name", so if we want to do the case-sensitive search we have to explicitly use the related collate: In EF 5 (currently in Release Candidate version [RC.2.20475.6]) Collate function got introduced which helps us to use our specific collation based search.  C# with EF5 code sample: var employeeCaseSensitiveSearch = _dbContext.Employees .Where(x => EF.Functions.Collate(x.FirstName, "Latin1_General_CS_AS") == "my-first-name") .FirstOrDefault(); A related database query will be something like this: T-SQL: Case sensitive search (use specific collation e.g.: Latin1_General_CS_AS) SELECT * FROM dbo.Employees AS e WHERE e.FirstName Collate Latin1_General_CS_AS = 'my-first-name' Some of the useful CSharp function which g...
Post a Comment
Read more

EFCore - Parallel db call on same dbContext

Practically there are multiple instances when we want to do parallel DB call which will save us some time. Like if we want to get a few employees details as well as users detail and if both entities are independent of each other better to go with a parallel call to fetch the details.  With the growing ORM usage and enhanced & optimized framework of like EFCore, in most of the application (related to C# development), we generally prefer to use EFCore. With EFCore if we try to use the same DB context to do parallel db call we are getting an error (... different thread using the same DbContext....). Because dbContext call is not thread-safe Parallel DB call on the same dbContext:  Code snipped: which will throw an error private void DbCallWithParallelCallOnSameDbContext() { try { var employeesTask = _dbContext.Employees.ToListAsync(); var usersTask = _dbContext.Users.ToListAsync(); ...
Post a Comment
Read more

How to install Zen-coding plugin

As a web-developer, irrespective of the technologies (java, c-sharp, python, php e.t.c.…), we used to write CSS code to make our web-pages looks good if not at least we’re involved in write html codes. What if there is some tool to whom you give some instruction and that tool generates a good, well formatted html tags for you. These kind of coding is possible and known as “ Zen coding ” and there are lots of plug-in available from different vendors. We’re going to  discuss the steps to install this “zen-coding” plugin for “visual studio, eclipse, sublime-text & notepad++ in next few lines. Follow the below steps to install "zen-coding" plugin based on your editor.  Steps to install zen-coding plugin for (visual studio, eclipse, sublime-text,notepad++) -- -- For Visual Studio 1. Go to "Tools" -> "Extensions and Updates" 2. It'll open the "Extensions and Updates windows"    Select online form Left hand menu items ent...
Post a Comment
Read more