InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 05:25:21 GMT20http://www.sqlservercentral.com/Forums/Topic563436-338-1.aspxThe 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.Thu, 04 Sep 2008 13:41:50 GMTGary Johnson-259336http://www.sqlservercentral.com/Forums/Topic563436-338-1.aspxThis 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?Wed, 03 Sep 2008 20:33:40 GMTrandal.schmidthttp://www.sqlservercentral.com/Forums/Topic563436-338-1.aspxYou 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....[code]CREATE Procedure CopyTableRecord ( @pRecordID int OUTPUT )ASINSERT INTO Table(field2, field3...)SELECT field2,field3...FROM TableWHERE field1 = @pRecordID[add error trap...]SET @pRecordID = scope_identity() -- so you can pass back the new ID for your front end app.GO[/code]Wed, 03 Sep 2008 20:12:29 GMTGary Johnson-259336http://www.sqlservercentral.com/Forums/Topic563436-338-1.aspxHello 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, DiscontinuedFROM products WHERE ProductId = 80Let me know if it helped you or you were in need of something else.Good Luck! :)Wed, 03 Sep 2008 17:56:09 GMTinutshttp://www.sqlservercentral.com/Forums/Topic563436-338-1.aspxI 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 rsOutDim rsInDim dbConnOutDim fieldValsDim dbConnInDim iDim dBTableDim connectStringDim shellDim tmpDim fsoDim tsDim lineDim fieldPostDim fieldValuesDim fieldcolumnsDim dboptionDim fieldposConst adOpenDynamic = 2Const adLockPessimistic = 2Const adCmdTable = 2Const adOpenForwardOnly = 0Const adOpenStatic = 3Const adLockReadOnly = 1' here we are using TCP to connect to SQL Server 2005 ExpressconnectString = "Provider=SQLNCLI;" _ & "Server=itopshelpdesk;" _ & "Database=sworks;" _ & "Uid=sw;" _ & "Pwd=xyz123;"dBTable = "config_itemi"' connect to and open the output database tableSet dBConnOut = CreateObject("ADODB.Connection")dBConnOut.Open connectStringdbConnOut.Execute ("SET IDENTITY_INSERT " & dBTable & " ON")dbConnOut.Execute ("SET NOCOUNT ON")Set rsOut = CreateObject("ADODB.RecordSet")rsOut.Open _"config_itemi", _dbConnOut, _adOpenDynamic, _adLockPessimistic, _adCmdTableSet shell = CreateObject("WScript.Shell")' connect and open the input "table" that is actually a csv fileSet 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, _adCmdTableSet fso = CreateObject("Scripting.FileSystemObject")Const ForReading = 1, ForWriting = 2, ForAppending = 8Const Unicode = -1, ASCII = 0Set 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.AtEndOfStreamoutput= objTextFile.Readline'arrStr = split(objTextFile.ReadLine,",")msgbox outputloop' dimension the arrays to the size of the number of columnsReDim fieldPos(rsIn.Fields.Count - 1)ReDim fieldValues(rsIn.Fields.Count - 1)ReDim fieldcolumns(rsIn.Fields.Count - 1)' set array with field location valuesi = 0For i = 0 To rsIn.Fields.Count - 1 fieldPos(i) = i + 1Next' go to the first recordrsIn.MoveFirstq = 0' until we get to the end of file....loopWhile Not rsIn.EOFq = q + 1For 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 IfNextoutput = ""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 purposesStrClipboard = output' use Internet Explorer to grab the dataWith 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,fieldValuesrsIn.MoveNextWend' rsOut.AddNew fieldPos,fieldValuesrsIn.ClosersOut.ClosedbConnOut.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.quitWed, 03 Sep 2008 15:36:43 GMTrandal.schmidt