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
"My questions are..." Those aren't questions. What problems are you having? What have you tried so far?
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
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)
19 People are following this question.
