MULTIPLE INSERT IN A DESIRED SEQUENCE

Question

MULTIPLE INSERT IN A DESIRED SEQUENCE

Neel 7777

Right there with Babe

Points: 778
More actions
March 11, 2013 at 6:40 pm

#394362

I HAVE A TABLE WITH THREE FIELD COMCOD nchar(4) DUPLICATE ALLOWED FOREIGN KEY, ACTCODE nvarchar(6) PRIMARY KEY, ACTDESC nvarchar(250).
TABLE IS LIKE
COMCOD ACTCODE ACTDESC
3306 180001 ADVANCE TO STAFF
3306 180002 ADVANCE TO OTHERS
3306 180003 ADVANCE TO SITE OFFICE
NOW I WOULD LIKE TO INSERT 10 ROWS IN A SINGLE COMMAND STARTS FROM 180010 TO 180100 WITH SEQUENCE 0F ACTCODE=ACTCODE+10 AND ACTDESC WITH NULL VALUES AND COMCOD WILL BE THE SAME FOR EVERY ROW.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Rauf Miah

Your request sounds very much like home work. I am not adverse to helping a student, but first post what you have attempted, and the error message returned if any, or the partial results, but not meeting your criteria.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Rauf Miah (3/11/2013)
I HAVE A TABLE WITH THREE FIELD COMCOD nchar(4) DUPLICATE ALLOWED FOREIGN KEY, ACTCODE nvarchar(6) PRIMARY KEY, ACTDESC nvarchar(250).
TABLE IS LIKE
COMCOD ACTCODE ACTDESC
3306 180001 ADVANCE TO STAFF
3306 180002 ADVANCE TO OTHERS
3306 180003 ADVANCE TO SITE OFFICE
NOW I WOULD LIKE TO INSERT 10 ROWS IN A SINGLE COMMAND STARTS FROM 180010 TO 180100 WITH SEQUENCE 0F ACTCODE=ACTCODE+10 AND ACTDESC WITH NULL VALUES AND COMCOD WILL BE THE SAME FOR EVERY ROW.

What you could do with here is a numbers/tally table.

Check out: -

Uses of Tally Table Part 1[/url],

Uses of Tally Table Part 2[/url],

The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

CTE Tally[/url],

Hidden RBAR: Counting with Recursive CTE's[/url]

As bitbucket, I'm reluctant to provide you with a ready made solution. But if you want to gives it a shot based on the articles above then post what you've done then I'm more than happy to help.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

you can use some sys tables (or any other available table) as kind of tally one:

INSERT YourTable (COMCOD, ACTCODE, ACTDESC)

SELECT TOP 10

3306 AS COMCOD

,180000 +

(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) * 10 AS ACTCODE

,NULL AS ACTDESC

FROM sys.columns

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]