Suppose I have a table named test with two fields(sl int,age int).
I want to insert 10 records all at a time.The records are in an incremental manner.I like to insert 1,2,3,4,5,6,7,8,9,10 for sl column and
22,23,24,25,26,27,28,29,30,31 for age column.But the procedure should follow C protype using for (j=1;j<10,j++) clause.
Is it possible to insert in SQL SERVER following c protype?
What is the fastest way for inserting multiple sequevcial data?
SubhasishFor relatively small sequential sets, do something like this...
INSERT INTO TEST(sl, age)
SELECT a.i, a.i+21
FROM (
SELECT i = 1 UNION
SELECT i = 2 UNION
SELECT i = 3 UNION
SELECT i = 4 UNION
SELECT i = 5 UNION
SELECT i = 6 UNION
SELECT i = 7 UNION
SELECT i = 8 UNION
SELECT i = 9 UNION
SELECT i = 10 ) as a
You can build up the derived table query quickly with cut-and-paste, then go back and fix the values
For bigger sequential sets, build yourself a temporary table of sequential integers like this:
CREATE TABLE #i
(x INT IDENTITY(1,1),
y INT)
INSERT INTO #i
VALUES(NULL)
INSERT INTO #i
SELECT y FROM #i
Running the last statement over and over will populate table #i with sequential integers in the x column. 11 executions gets you 1K rows, 21 gets you 1M rows, ... Then use the temporary table to drive your insert.
INSERT INTO test(sl, age)
SELECT #i.x, #i.x+21
FROM #i
WHERE #i.x < 100 -- for 100 rows|||I think s/he's looking for a loop as well...
USE Northwind
GO
CREATE TABLE myTable99 (sl int,age int)
GO
DECLARE @.x int, @.y int
SELECT @.x = 1, @.y = 1
WHILE @.x < 100
BEGIN
INSERT INTO myTable99 (sl, age)
SELECT @.X, 1*@.y UNION ALL
SELECT @.X, 2*@.y UNION ALL
SELECT @.X, 3*@.y UNION ALL
SELECT @.X, 4*@.y UNION ALL
SELECT @.X, 5*@.y UNION ALL
SELECT @.X, 6*@.y UNION ALL
SELECT @.X, 7*@.y UNION ALL
SELECT @.X, 8*@.y UNION ALL
SELECT @.X, 9*@.y UNION ALL
SELECT @.X, 10*@.y
SELECT @.x = @.x + 1, @.y = @.y + 1
END
SELECT COUNT(*) FROM myTable99
GO
DROP TABLE myTable99
GO
Friday, March 9, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment