Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MULTIPLE INSERT IN A DESIRED SEQUENCE


MULTIPLE INSERT IN A DESIRED SEQUENCE

Author
Message
Neel 7777
Neel 7777
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 84
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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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

Before posting a performance problem please read
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
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,
Uses of Tally Table Part 2,
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
CTE Tally,
Hidden RBAR: Counting with Recursive CTE's

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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search