YT YouTube2Text

YouTube2Text → Transcripts → Select into in sql server

Select into in sql server — Transcript

by kudvenkat · 1,974 words · 280 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00this is part 97 of SQL Server tutorial
0:03in this video we'll discuss the power
0:05and use of Select into statement in SQL
0:07server for the purpose of this demo
0:09we'll be using these two tables
0:11departments and employees I've already
0:13created these tables and here is the SQL
0:15script to create and public them with
0:17test data I'll have the script available
0:19on my blog in case you need it so what's
0:22the use of Select into statement select
0:24into statement selects data from one
0:26table and inserts it into a new table
0:29now let's look at some of the use cases
0:31for this select into statement we can
0:34use it to copy all rows and columns from
0:36an existing table into a new table this
0:39is extremely useful when you want to
0:41make a backup copy of the existing table
0:44let's understand this with an example
0:45here we have got this employees table
0:47which has some data within it now we
0:49want to make a backup copy of this table
0:52the easiest way to do that is by using
0:54select into statement look at the
0:57example here select star into employees
0:59backup from employees table so the star
1:02here specifies that we want to retrieve
1:05all the rows and columns from employees
1:07table and then this employees backup
1:09table should be created on the Fly and
1:12all that data from the employees table
1:14should be inserted into that employees
1:16backup table this employees backup table
1:19has to be a new table it cannot be an
1:22existing table if you specify an
1:24existing table with select into
1:26statement it's going to throw an error
1:27saying that the table already exists
1:30let's look at this in action let's flip
1:31to SQL management studio so if you look
1:34at the tables folder within sample DB
1:36database we don't have the employees
1:38backup table there we only have the
1:40employees table so now let's go ahead
1:43and execute our select into statement so
1:45when we execute that the first thing
1:47that should happen is this employees
1:49backup table should have been created on
1:51the Fly and all the data from employees
1:53should have been copied into it so let's
1:56refresh this tables folder notice that
1:58we have our employees back up table
2:00there and when we select the data from
2:02it we should have all the rowes of the
2:04employees table within that employees
2:07backup now let's go ahead and drop this
2:10table now we can also use this select
2:13into statement to copy all rows and
2:16columns from an existing table into a
2:19new table in an external database so if
2:22you look at this employees and employees
2:24backup table they are present within the
2:27same database now what if you want you
2:30know the backup in a different database
2:32now here I have another database hrdb
2:35now what we want to do is create a
2:38backup copy for this employees table in
2:41an external database that is in hrdb
2:44database so the employees table is
2:46present in Sample DB but we want the
2:48backup in another database HR DB now we
2:51can still achieve that using select into
2:53statement all we have to do is specify
2:57the fully qualified name so this EMP
2:59employees backup table has to be present
3:01in hrdb database and we're using the
3:03schema dbo so HR db. dbo do employees
3:07backup so when we execute this select
3:10into statement you know this employees
3:13backup table should have been created in
3:15hrdb database so let's go ahead and
3:18refresh this tables folder notice that
3:20we have the employees backup table there
3:23and when we select the data we should
3:24have all the rules of the employees
3:26table so now let's go ahead and drop
3:29that table
3:31we can also use the select into
3:33statement to copy only selected columns
3:36into a new table so if you look at this
3:40employees table we have got like five
3:43columns in it now let's say for example
3:46in our backup table I don't want all the
3:49columns I just want the first three
3:51columns ID name and gender okay if
3:54that's the case instead of using star
3:56simply specify the column list the
3:58columns that you want we want ID name
4:01gender from employees table the backup
4:03table has to be employees backup so this
4:05table will now have only those three
4:08columns so let's look at that in action
4:10when I execute that the employees backup
4:12table is created on the fly with those
4:14three columns and all the data should
4:17have been copied into that so let's drop
4:21this backup table
4:22again now we can also use select into
4:25statement to copy only selected rows
4:28into a new table table so now if you
4:31look
4:33at this employees table at the moment it
4:36has got five rows in it uh in it now
4:39what we want to do is we want to
4:40retrieve and make a backup copy of the
4:43employees whose department is Department
4:46ID 1 we can still do that using a wear
4:49Clause here so in this case the schema
4:52for this employees backup table is going
4:54to match up with the employees table but
4:57as far as the data is concerned it's
4:59going to contain only the employee rows
5:02where Department ID equals 1 so let's go
5:05ahead and execute this
5:07query now look at that the message says
5:09two rows affected and if you look at the
5:11data in our backup table we should only
5:14have those employees whose Department ID
5:17is one let's drop
5:22that now so far we have seen examples of
5:25how to make a backup copy from a single
5:29table
5:30now we can also use select into to copy
5:33columns from two or more tables into a
5:36new table so here we have got two tables
5:39departments and employees now what I
5:41want to do is I want to copy all the
5:44columns from both of these tables into
5:47you know a backup table okay so if you
5:50look at the select into statement here
5:52this is again straightforward now let me
5:54actually remove that uh you know into
5:57here and let me say simply select star
6:01from employees in a join departments and
6:03that's the join condition we are joining
6:05with Department ID in employees table
6:08with Department ID in Department table
6:11so when we execute this what are we
6:12going to get we're going to get you know
6:15all the columns from both the tables
6:17okay now what I'm going to do here is
6:19Select star
6:23into
6:26employees backup so this is going to set
6:29select all these rows and columns into
6:31this employees backup table let's
6:33actually look at that in action so I am
6:36going to execute this and when we select
6:39data from employees backup look at that
6:42we've got all the columns and rows from
6:45both the tables employees and
6:47departments now what if we want you know
6:49only specific columns now if you look at
6:51this data definitely Department ID is
6:54duplicated we have Department ID in
6:56employees table and Department ID in
6:58Department table table now let's say we
7:00want only you know one column either
7:04from the employees table or from the
7:05Department's table we don't want that to
7:07be duplicated we can still do that so
7:10instead of specifying star here we can
7:12specify the list of columns that we want
7:15so let's do that now so let's actually
7:18drop
7:19this and I am going to say we want all
7:23the
7:24columns from employee table so I'm going
7:27to say employees uh
7:29star and then I want from departments
7:33table so departments dot Department name
7:38so let's execute
7:40this and now if we select from employees
7:44backup notice that we only got the
7:46department ID from employees table and
7:49then you know the department name from
7:51Department's table we didn't get the
7:52Department ID column from departments
7:55table so you can either specify star or
7:58the column list list that you want when
8:00you are joining two or more tables so
8:02here the example covers only two tables
8:05but you can have as many tables as you
8:08want now you can also create a new table
8:12whose columns and data types match with
8:14an existing table now there might be
8:17situation where you know your table has
8:19got a lot of columns maybe like 40 to 50
8:22different columns in a given table now
8:25your requirement is to create a table
8:28whose ke matches with that table but you
8:31don't want any data within that new
8:33table that you're going to create now
8:35you can still do that using select into
8:38if you have to manually do that you know
8:40you have to write that create table
8:41script and it's going to take a bit of
8:43time but with select into statement it's
8:45going to take literally like 5 Seconds
8:47to do that now the trick here is to
8:51specify your wear clause and you know
8:53the condition here is never going to be
8:55true one not equal to one that will
8:58never be true so no row will be copied
9:01from employees into this employees
9:03backup okay it will only create that
9:06table with the structure of the
9:07employees table okay let's look at that
9:10in action let's drop this employees
9:12backup table and now I'm going to
9:14execute this query right here and look
9:17at the message it says zero rows
9:19affected that means no rows are copied
9:21from the employees table into employees
9:23backup but the structure of this table
9:26must be very similar to employees table
9:29look at that we've got ID name gender
9:31salary and Department ID columns the
9:33same columns that we have in employees
9:36table now one very important thing to
9:40keep in mind is that you cannot use
9:43select into statement to select data
9:45into an existing table select into
9:48statement can only be used to select
9:50data into a new table okay to select
9:54data into an existing table you will
9:56have to use insert into state
9:59statement so look at this when I execute
10:02this select star into employees backup
10:04now at the moment we have this employees
10:06backup table we don't have any data in
10:08that so it's an existing table and here
10:11I'm using select into statement and I'm
10:14trying to select data into this existing
10:16table and look at what we are going to
10:18get look at the message it says there is
10:21already an object named employee backup
10:23in the database so you cannot use select
10:25into statement to select data into an
10:27existing table to select data into the
10:30existing table you will have to use
10:32insert into statement so here insert
10:34into employees backup select sta from
10:38employees so when we do that it's going
10:41to retrieve all the rows from employees
10:43and insert them into this existing table
10:45employees
10:47backup there we go now let's actually
10:50truncate the data in this
10:55one so truncate table employees backup
10:58so this is going to remove all the rows
11:01from that table so now we don't have any
11:05data now let's say for some reason you
11:07don't want you know all the columns from
11:10employees table you only want to insert
11:12ID name and gender column values uh but
11:15not salary and Department ID if that's
11:17the case you can specify the column list
11:20here so insert into employees backup and
11:23the values that I want to insert are ID
11:26name gender and and we want to select ID
11:33name and gender from employees so when
11:36we execute this within our employees
11:39backup you know only those three columns
11:41should be populated whereas salary and
11:43Department ID or
11:49not thank you for listening and have a
11:51great
11:52day

About this transcript

This page contains the full transcript of Select into in sql server by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 1,974 words across 280 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.