Skip to main content

SQL table copies - SELECT INTO VS INSERT INTO


Creating a new table from another table 

see: https://www.w3schools.com/sql/sql_select_into.asp

 This creates a new table and inserts all the data from the oldtable 

 SELECT * INTO newtable FROM oldtable

You can create a new empty table from the schema of an other using a statement like

SELECT * INTO newtable FROM oldtable WHERE 1 = 0;

Copy data from one table into another existing table 


Insert data from one table into another 

INSERT INTO table2 SELECT * FROM table1
If you need to specify a column list (as is the cast when dealing with identity insert), the syntax is...
  • The tips are:
    • Brackets are required around the destination fields
    • No brackets are required around the select fields, just like a normal select statement 
    • You need the identity_insert flag

SET IDENTITY_INSERT table2 ON; 

INSERT INTO table2 ( col1, col2, col3) SELECT col1, col2, col3 FROM table1

SET IDENTITY_INSERT table2 OFF; 

Comments

Post a Comment

Popular posts from this blog

Changing Password - in AD, when you're changing one of your other accounts, not the logged in account

  Use Powershell  Step 1 - Put your existing password in a Secure String  $oldPassword = Read-Host "Your old Password" -AsSecureString  Step 2 - Start the password change set-AdAccountPassword -Identity paul-admin -OldPassword  $oldPassword Step 3 - Enter your new password You'll be prompted for your new password... Please enter the desired password for 'CN=xxxx,OU=Admin Users,OU=Users,OU=Privileged,DC=CAIS,DC=com,DC=edu,DC=au' Password: ************************** Repeat Password: **************************
Post a Comment
Read more

Tip to create an underline on a heading shorter than the heading itself

You can use a pseudo element with :before (or :after ): h1 { font - weight : 300 ; display : inline - block ; padding - bottom : 5px ; position : relative ; } h1 : before { content : "" ; position : absolute ; width : 50 %; height : 1px ; bottom : 0 ; left : 25 %; border - bottom : 1px solid red ; } http://jsfiddle.net/9e27b/ This is another solution that centers the heading, the problem here is that the underline gets shorter as the column gets shorter. h2 {   display: inline-block;   padding-bottom: 15px;   position: relative;   width: 100% ;   text-align: center; } h2:before{     content: "";     position: absolute;     width: 8%;     height: 1px;     bottom: 0;     left: 46%;     border-bottom: 1px solid red; }
Post a Comment
Read more

The web.config no extension mime problem Lets Encrypt on IIS/Windows

  Tip when using LetsEncrypt in Windows - Feature Requests - Let's Encrypt Community Support In a paragraph, just use the web.config file to set the mime type, eg...  After trying to figure out why my Lets Encrypt failed to generate in Windows, and discovering that it's the no extension mime type problem, I wondered whether certbot could fill in the missing gap. All that is needed is the following web.config file to be placed in the same directory as the challenge <?xml version="1.0" encoding="UTF-8"?> <configuration> <system.webServer> <staticContent> <mimeMap fileExtension="." mimeType="text/xml" /> </staticContent> </system.webServer> </configuration> Because there's no mime type for files without an extension on IIS, IIS sends back a 404 when verification happens. The web.config file above sets the mime type. After I created the .well-known
1 comment
Read more