|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Monday, June 22, 2009 6:55 AM
Points: 17,
Visits: 52
|
|
I have a table called config_itemi. It has a single indentity column (pk_auto_id). I have a user that wants to be able to create duplicate records in the table based on an intial record. He has the identity column value of the base record/row. I wanted to prompt in VBscript for the pk_auto_id value and somehow do a select * and write a duplicate row. I am hoping the pk_auto_id value will be ignored on the INSERT and overwritten by the auto-incrementing value of the identity column. How do I write the SQL to select all the columns and write them out? I am an SQL newbie but am not too bad at VBscript. I tried extracting the data with a select statement in VBscript and then doing an addnew in VBScript but I am not able to obtain values for int type columns so I gave up on a total VBscript solution. I am guessing T-SQL will be much simpler even with a VBscript wrapper. CAN ANYBODY HELP ME !!!!!!!????????
Here is my poor man's version (with debugging code in it :-( ) to show you I am trying:
'Option Explicit
Dim rsOut
Dim rsIn
Dim dbConnOut
Dim fieldVals
Dim dbConnIn
Dim i
Dim dBTable
Dim connectString
Dim shell
Dim tmp
Dim fso
Dim ts
Dim line
Dim fieldPost
Dim fieldValues
Dim fieldcolumns
Dim dboption
Dim fieldpos
Const adOpenDynamic = 2
Const adLockPessimistic = 2
Const adCmdTable = 2
Const adOpenForwardOnly = 0
Const adOpenStatic = 3
Const adLockReadOnly = 1
' here we are using TCP to connect to SQL Server 2005 Express
connectString = "Provider=SQLNCLI;" _
& "Server=itopshelpdesk;" _
& "Database=sworks;" _
& "Uid=sw;" _
& "Pwd=xyz123;"
dBTable = "config_itemi"
' connect to and open the output database table
Set dBConnOut = CreateObject("ADODB.Connection")
dBConnOut.Open connectString
dbConnOut.Execute ("SET IDENTITY_INSERT " & dBTable & " ON")
dbConnOut.Execute ("SET NOCOUNT ON")
Set rsOut = CreateObject("ADODB.RecordSet")
rsOut.Open _
"config_itemi", _
dbConnOut, _
adOpenDynamic, _
adLockPessimistic, _
adCmdTable
Set shell = CreateObject("WScript.Shell")
' connect and open the input "table" that is actually a csv file
Set dBConnIn = CreateObject("ADODB.Connection")
dbConnIn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & shell.SpecialFolders("Desktop") & ";" & _
"Extended Properties=""text;FMT=Delimited;HDR=yes"";"
Set rsIn = CreateObject("ADODB.RecordSet")
rsIn.Open _
"config_itemi.csv", _
dbConnIn, _
adOpenStatic, _
adLockReadOnly, _
adCmdTable
Set fso = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const Unicode = -1, ASCII = 0
Set objTextFile = fso.OpenTextFile("c:\config_itemi.csv", ForReading)
'fso.OpenTextFile("config_itemi.csv", 1, 0)
' fso.OpenTextFile("config_itemi.csv", 1, 0, Unicode)
Do Until objTextFile.AtEndOfStream
output= objTextFile.Readline
'arrStr = split(objTextFile.ReadLine,",")
msgbox output
loop
' dimension the arrays to the size of the number of columns
ReDim fieldPos(rsIn.Fields.Count - 1)
ReDim fieldValues(rsIn.Fields.Count - 1)
ReDim fieldcolumns(rsIn.Fields.Count - 1)
' set array with field location values
i = 0
For i = 0 To rsIn.Fields.Count - 1
fieldPos(i) = i + 1
Next
' go to the first record
rsIn.MoveFirst
q = 0
' until we get to the end of file....loop
While Not rsIn.EOF
q = q + 1
For i = 0 To rsIn.Fields.Count - 1
' if this is our firstpass, grab the column headings
If q = 1 Then
fieldcolumns(i) = rsIn.fields(i).Value
' otherwise grab the field values
Else
fieldValues(i) = rsIn.fields(i).Value
' if this is a NULL value we cannot write it as such
If Not IsNull(fieldValues(i)) Then
Else
'If IsNull(fieldValues(i)) Then make it a unique value that we will update in the table after we write it
fieldValues(i) = "-#%REMOVE%#-"
End If
End If
Next
output = ""
For x = 0 To rsIn.Fields.Count - 1
' fieldpos(x) = x+1
output = output & "fieldpos is " & fieldpos(x) & Chr(13)
output = output & "fieldcolumn is " & fieldcolumns(x) & Chr(13)
'If fieldvalues(x) = "-#%REMOVE%#-" Then fieldvalues(x) = ""
output = output & "fieldvalue is " & fieldvalues(x) & Chr(13)
Next
' routine to put data on the clipbopard for debug purposes
StrClipboard = output
' use Internet Explorer to grab the data
With CreateObject("InternetExplorer.Application")
.Navigate "about:blank"
' give it time to load
Do until .ReadyState = 4 : Wscript.Sleep 100 : Loop
' don't show IE
.visible = False
With .document
' write a dummy header and then the clip board contents
.writeln(" ")
.writeln(StrClipboard)
.writeln(" ")
' now select all the text (like ALT-A)
.execcommand "SelectAll"
' now copy the data to the clipboard from IE (like ALT-C)
.execcommand "Copy"
' close up shop
End With ' document
End With 'Createobject
' rsOut.AddNew fieldPos,fieldValues
rsIn.MoveNext
Wend
' rsOut.AddNew fieldPos,fieldValues
rsIn.Close
rsOut.Close
dbConnOut.Execute ("UPDATE " & dBTable & " SET Data='' WHERE Data='-#%REMOVE%#-'")
WScript.Echo "Import Complete"
'=======================================================================================================================
'Inform user we are done!
MsgBox "File Export using " & connectString & " for the " & dBTable & " table is done."
wscript.quit
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, June 15, 2009 4:37 AM
Points: 7,
Visits: 16
|
|
Hello randal.schmidt,
In SQL, you can duplicate row by using following command:
Note: I have taken example using database.table = Northwind.Products, you can modify it as per your need.
INSERT INTO products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, unitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, unitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM products WHERE ProductId = 80
Let me know if it helped you or you were in need of something else.
Good Luck! :)
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, March 04, 2009 7:27 PM
Points: 79,
Visits: 142
|
|
You would be better off to create a Stored Procedure that has a parameter for the ID of the record you want to copy, and uses it as for your insert statement....
CREATE Procedure CopyTableRecord
(
@pRecordID int OUTPUT
)
AS
INSERT INTO Table(field2, field3...)
SELECT field2,field3...
FROM Table
WHERE field1 = @pRecordID
[add error trap...]
SET @pRecordID = scope_identity() -- so you can pass back the new ID for your front end app.
GO
Gary Johnson
Sr Database Engineer
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Monday, June 22, 2009 6:55 AM
Points: 17,
Visits: 52
|
|
| This is close. The row I want already exists. I can grab it with a select and then I have the pk_auto_id also. I understand I can write another one out thru INSERT. The table has 96 columns so I was hoping to find something that could eliminate having to code all those column names for the select into and ISERT statements. Lastly, when I write out the new row, I need to find out what new pk_auto_id was created for it. There are some extended tables I must also duplicate rows in and putthis new pk_auto_id in it. If it were a trigger, I could use the MAX function to the INSERTED table. How would I do it in this case?
|
|
|
|
|
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, March 04, 2009 7:27 PM
Points: 79,
Visits: 142
|
|
The Scope_Identity function call returns the last ID inserted into the identity field. Thus, if you need to add child records you use that value for their inserts.
Gary Johnson
Sr Database Engineer
|
|
|
|
