SQL Server Performance Forum – Threads Archive

Insert Multiple Records

Hi to all,
Pls help, i have a problem on how to insert/add multiple records into my table.
the format of the field i want to insert is like this: 001-01-001 up to 001-01-999 and it will continue to 001-02-001 up to 001-02-999 until my last record reach to 026-99-999. Now, my problem is that not all of the rows/records are all filled up or not sequential yet because the records are not yet available. Meaning, not all records runs from ###-##-001 straight to ###-##-999. for example i have records from 020-10-001 up to 020-10-020 but rows 020-10-005, 020-10-009, and 020-10-016 to 020-10-019 are not there yet. Some of the gaps in the sequence runs from one to hundred rows not yet inserted. Our office wants to include and see the field values in order to determine (or count) the numbers still available for filling up How can i insert/add this many records which are not yet added to my table automatically? thanks in advance.

Please post the table schema and datatypes used. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

hello satya,
here is the table schema of my database: Table: TMCR
Fields: Column NameDatatypeLengthPrecisionAllow Nulls
Pin Nvarchar130
Name Nvarchar600 â€žÃ‘
Address1Nvarchar400 â€žÃ‘
City_ProvNvarchar300 â€žÃ‘
Lot_No Nvarchar300 â€žÃ‘
Sur_No Nvarchar300 â€žÃ‘
Title Nvarchar110 â€žÃ‘
Area Int 410 â€žÃ‘
TD Nvarchar60 â€žÃ‘
Name_longNvarchar120 â€žÃ‘
Remarks Nvarchar500 â€žÃ‘
the field i would like to make the records sequential is the Pin field.
Some Sample
Note: duplicate pin is just ok since it duplicates varies other values from the rest of the rows.
PIN
001-01-001
001-01-002
001-01-003
001-01-004
001-01-005
001-01-006
001-01-007
001-01-008
001-01-009
001-01-010
001-01-011
001-01-012
001-01-012
001-01-012
001-01-012
001-01-013
001-01-014
001-01-015
001-01-016
001-01-017
001-01-018
001-01-019
001-01-020
001-01-020
001-01-020
001-01-021
001-01-021
001-01-022
001-01-023
001-01-024
001-01-025
001-01-026
001-01-027
001-01-027
001-01-028
001-01-028
001-01-029
001-01-030
001-01-031
001-01-032
001-01-033
001-01-034
001-01-035
001-01-036
001-01-036
001-01-037
001-01-038
001-01-039
001-01-040
001-01-041
001-01-042
001-01-043
001-01-043
001-01-044
001-01-045
001-01-046
001-01-047
001-01-048
001-01-049
001-01-050
001-01-051
001-01-051
001-01-052
001-01-053
001-01-054
001-01-055
001-01-056
001-01-057
001-01-057
001-01-058
001-01-059
001-01-060
001-01-061
001-01-062
001-01-063
001-01-064
001-01-065
001-01-066 — I want to insert row 001-01-068 between 001-01-067 and 001-01-0068
001-01-067
001-01-069
001-01-070
001-01-071
001-01-072
001-01-073
001-01-074
001-01-074
001-01-074
001-01-075
001-01-076
001-01-077
001-01-078
001-01-079
001-01-080 — I want to insert row 001-01-081 to 001-01-083 between 001-01-080 & 001-01-084
001-01-084
001-01-085
001-01-086
001-01-087
001-01-088
001-01-089
001-01-090
001-01-091
001-01-092
001-01-093
001-01-094
001-01-095
001-01-096
001-01-097
001-01-098
001-01-099
001-01-100
001-01-101
001-01-102
001-01-103
001-01-104
001-01-105
001-01-106
001-01-107
001-01-108
001-01-109
001-01-110
001-01-111
001-01-112
001-01-113
001-01-114
001-01-115
001-01-116
001-01-117
001-01-118
001-01-119
001-01-120
001-01-121
001-01-122
001-01-123
001-01-125
001-01-125
001-01-125
. . .
. . . and many more gaps up to the last record.

I would split your pin into 3 separated columns of type TINYINT. That makes it a whole lot easier to determine gaps and fill them.
Here’s a bit of code you might be able to use. You would need to build your own logic and make the code aware when one of the first two columns change.
CREATE TABLE t
(
k1 INT NOT NULL
, c1 CHAR NOT NULL
CONSTRAINT pk_t PRIMARY KEY(k1)
)
GO
CREATE FUNCTION dbo.CloseMyGaps() RETURNS INT
AS
BEGIN
RETURN
CASE
WHEN EXISTS
(SELECT *
FROM t
WHERE k1 = 1)
THEN (SELECT MIN(t1.k1) + 1
FROM t t1
LEFT OUTER JOIN t t2
ON t1.k1 = t2.k1 – 1
WHERE t2.k1 IS NULL)
ELSE 1
END
END
GO
ALTER TABLE t ADD CONSTRAINT d_k1 DEFAULT dbo.CloseMyGaps() FOR k1
GO
INSERT INTO t(c1) VALUES(‘a’)
INSERT INTO t(c1) VALUES(‘b’)
INSERT INTO t(c1) VALUES(‘c’)
INSERT INTO t(c1) VALUES(‘d’) SELECT *
FROM t
DELETE FROM t WHERE k1 IN (2,3)
INSERT INTO t(c1) VALUES(‘d’)
INSERT INTO t(c1) VALUES(‘d’)
DELETE FROM t WHERE k1 =1
INSERT INTO t(c1) VALUES(‘f’)
SELECT *
FROM t
DROP TABLE t —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¯Â¿Â½tze PASS Deutschland e.V. http://www.sqlpass.de)

Hello Frank,
Thank you for your reply. But since i’m a beginner in sql server i can’t quite grasp you given code. Is there any simpler method for inserting the multiple records without inserting in one row at a time? I can use "Insert roll1 (pin) values ("001-01-081")" as an example to add/insert one row. Is there a similar insert procedure to insert/add multiple or a given list of rows?thanks again.

]]>