question

nanigmp avatar image
nanigmp asked

i have created the foolowing tables in SQLSERVER2008 but am not getting how to populate them by using TSQL

SQL SERVER 2008 here are the details of tables Employee: empid int empname varchar Salary int dateofjoin date deptno int Department: deptno int deptname varchar locationid int location: locationid int locname varchar myyy questions are - Populate 10 locations using ids 1 to 10 with names loc1..loc10 - Populate departments 1 to 200 with names dept1 to dept200. Assign locations 1 to 10 rahdomly to the departments - Populate 1 to 100000 employees into 1 to 200 departments randomly
sql-server-2008homework
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this homework? If so, what do you have thus far?
0 Likes 0 ·
"My questions are..." Those aren't questions. What problems are you having? What have you tried so far?
0 Likes 0 ·
i know how to populate by using insert ..but i want it in TSQl programming
0 Likes 0 ·
i want to insert them byy using tsql i tries like this for location table Declare @ID NUMBER,@LOCNAME VARCHAR(10) SET @counter = 1 WHILE(@counter <= 10) BEGIN SELECT @ID = LOCATION ID ,@LOCNAME=LOCNAME INSERT INTO LOCATION VALUES (@ID,@LOCNAME) Set @counter = @counter+1; END for department table;; Declare @ID NUMBER,@DEPTNAME VARCHAR(10),@LOCNAME VARCHAR(10),@counter int SET @counter = 1 WHILE(@counter <= 200) BEGIN SELECT @ID = DEPTNO,@DEPTNAME=DEPTNAME,@LOCNAME=LOCNAME INSERT INTO DEPARTMENT VALUES (@ID,@LOCNAME,@DEPTNAME); Set @counter = @counter+1; END for employee table Declare @ID NUMBER,@empname varchar,@Salary number,@date date,@deptno number SET @counter = 1, WHILE(@counter <= 100000) BEGIN SELECT @ID = EMPID ,@EMPNAME=EMPNAME.@SALARY=SALARY,@DATE=DATEOFJOIN,@DEPTNO=DEPTNO INSERT INTO EMPLOYEE VALUES (@ID,@empname,@Salary,@dateofjoin,@deptno); Set @counter = @counter+1; END
0 Likes 0 ·
it was a assigenment given by my faculty,,i am learning sqlserver from past 15 days ,,so i want to populte above byy sql server
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
T-SQL is designed (and therefore much more efficient) to be used for set-based operations. The loops you proposed would insert 1 row of data per loop iteration. It would be better to skip the loop and insert the whole set at once. That said, where is the source data? Is it in another database table or maybe a text file? Knowing more details about what you need to do will help us help you. (with all due respect to Jerry Maguire, lol)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.