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

Azure SQL - User management with Active Directory Group

To manage the user's roles we use Service Account on OnPrem/IaaS servers. We can use this service account on our SQL Server and accordingly manage users' permissions for the users who are part of this service account. Suppose we have an Azure SQL and we want to manage set-of-users permission shall we create each-and-every users' user profile in Azure SQL and set the permissions accordingly. Obviously, we will not .  If we've anything like Service Account (or Group) on Azure we could create the user of this GROUP on Azure SQL and set its permission/role. Here comes the Azure Active Directory Group to help us.  In case if our organization has some scheduled tasks to sync Active Directory (service account) to Azure Active Directory (AAD group), already in place, there will be some delay when we add a user to the Service account and get reflected in the AAD Group. If we have a requirement that as soon as we add a user to our group we wan...
Post a Comment
Read more

Git - Update submodule

Sometimes in the large project when we separate it in different repositories we need to take reference of one repository in another where some the sub-module concepts. Now if we move our submodule to a different repository or renaming its repo-path then we've to also update the repository where this sub-module is getting referenced. Let's see how to update the git submodule.  Step-1: First clone the repo Step-2: Open git UI to create a new local branch from master  Step-3: Open git bash -- Now run below command to (remove submodules & add them):  -- Remove submodule git submodule deinit YOUR_FIRST_REPO git rm YOUR_FIRST_REPO git commit -m "Removed submodule YOUR_FIRST_REPO" rm -rf .git/modules/YOUR_FIRST_REPO -- Add submodule git submodule add <<..YOUR_FIRST_REPO git URL>> Step-4: All good, let's push our changes to the master
Post a Comment
Read more