SQL Clone
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
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 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
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15965 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 Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9190 Visits: 8492
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12654 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