SQL Insert Select Examples

1-) Add all authors to the students table

Solution 1)

Insert into students(name,surname) 
	Select name,surname from authors

ETİKETLER

Select - Insert -

2-) Add the male students from class "9b" to the authors table

Solution 1)

Insert into authors(name,surname) 
       select name,surname from students
              where sinif='9A' and gender='M'

Solution 2)

Insert into authors 
       select name,surname from students 
              where sinif='9A' and gender='M'

ETİKETLER

Select - Insert - Where -

3-) Insert into the authors table, Random selected five students

Solution 1)

Insert into authors(name,surname) 
	Select top 5 name,surname from students order by newid()

ETİKETLER

Select - Insert - Top - Order By -

4-) Add the authors whose name contains "a" character from autors table to students table. The class of autors will have been '12M'

Solution 1)

Insert into students(name,surname,sinif) 
	Select name,surname,'12M' from authors where name like '%a%'

ETİKETLER

Select - Insert - Where -

5-) Generate Unique Random Numbers In Sql With Stored Procedure

Solution 1)

Create procedure randomGenerate(@unit int,@min int,@max int)
as 
Begin
	Declare @numbers table(number int)
	Declare @i int = 0
	Declare @number int
	while (@i<@unit)
	Begin
		Set @number = floor(rand()*(@max-@min+1))+@min
		if(not exists(Select * from @numbers where number = @number))
		begin
			insert into @numbers values(@number)
			Set @i = @i + 1 
		end
	end
	Select * from @numbers order by 1
End
--To Execute Stored Procedure
Execute randomGenerate 5,20,30

ETİKETLER

Select - Insert - Procedure - Declare -