Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MULTIPLE INSERT IN A DESIRED SEQUENCE Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 6:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, 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.

Post #1429553
Posted Monday, March 11, 2013 8:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1429561
Posted Tuesday, March 12, 2013 4:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 2,386, Visits: 7,623
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1429668
Posted Tuesday, March 12, 2013 4:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1429672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse