x

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

more ▼

asked Sep 14, 2015 at 12:15 PM in Default

avatar image

nanigmp
1 2

Is this homework? If so, what do you have thus far?

Sep 14, 2015 at 12:23 PM JohnM

"My questions are..."

Those aren't questions. What problems are you having? What have you tried so far?

Sep 14, 2015 at 03:33 PM ThomasRushton ♦♦

i know how to populate by using insert ..but i want it in TSQl programming

Sep 14, 2015 at 04:08 PM nanigmp

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

Sep 14, 2015 at 04:14 PM nanigmp

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

Sep 15, 2015 at 05:08 AM nanigmp
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)

more ▼

answered Sep 14, 2015 at 08:08 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2219
x113

asked: Sep 14, 2015 at 12:15 PM

Seen: 167 times

Last Updated: Sep 15, 2015 at 05:08 AM

Copyright 2018 Redgate Software. Privacy Policy