Does SQL server have an inbuilt SP which I could use to generate INSERT INTO...VALUES(...) statements for all the data the table contains?
Printable View
Does SQL server have an inbuilt SP which I could use to generate INSERT INTO...VALUES(...) statements for all the data the table contains?
Not that I'm aware of.
You could write one using the SysObjects and SysColumns tables I would have thought. Might save time in the long run.
Not sure I really understand your question - but...Quote:
Originally Posted by mendhak
If you go into QUERY ANALYZER - and have the object panel open - expand the tree until you see the table you want...
Then RIGHT-CLICK and DRAG that table into the QUERY PANEL - you will get a pop-up menu with options...
Choose INSERT and you will get an INSERT template.
What do you want to do with this anyway?
I understood it to be that he wants to generate a list of inserts that he can use to port data from a table to a copy of itself some where (but can't use DTS).
ie. He has Table1 with cols Col1 and Col2.
and from that he wants to generate a text file of inserts like:Code:--data
Col1 Col2
'AAA' 111
'BBB' 222
'CCC' 333
Code:INSERT INTO Table1 (Col1, Col2) VALUES ('AAA', 111)
INSERT INTO Table1 (Col1, Col2) VALUES ('BBB', 222)
INSERT INTO Table1 (Col1, Col2) VALUES ('CCC', 333)
That seems like an expensive way to move data.
We have a little program called TEXTOUTPUT that creates a TDF text file from any table we want - tables identified in a TEXTOUTPUT.INI file...
Those TDF text files load back into SQL effortlessly with BULK INSERT (from QA) or BCP (which we never have actually used).
Nice thing is that it's not a "logged" activity...
The reason I want to do this, even though it's expensive, is because I need to insert seed data into certain tables for my deployment. I wanted to simply run query analyzer for that, but had to find a convenient way to generate the INSERT statements.
This is an SP I found on the Internet, but for tables with lots of columns, it just falls over it's own rear end:
Code:
CREATE PROCEDURE GetInsertStatements
@TABLENAME VARCHAR ( 2000 )
AS
BEGIN
DECLARE CUR_METADATA CURSOR FOR
SELECT NAME , XTYPE
FROM SYSCOLUMNS
WHERE ID IN ( SELECT ID FROM SYSOBJECTS WHERE NAME = @TABLENAME )
/* DECLARE VARIABLES*/
DECLARE @QRY1 VARCHAR (8000 ) /* Has column names */
DECLARE @QRY2 VARCHAR ( 8000 ) /* Has column values */
DECLARE @COLNAME VARCHAR ( 8000)
DECLARE @COLDATATYPE TINYINT
DECLARE @PRVCOLDATATYPE TINYINT
DECLARE @CHARPREFIX VARCHAR ( 5 )
DECLARE @CHARPOSTFIX VARCHAR ( 5 )
DECLARE @COLUMNSEPARATOR CHAR ( 1 )
DECLARE @NULLCHAR CHAR ( 4 )
SET @CHARPREFIX = ''''
SET @CHARPOSTFIX = ''''
SET @COLUMNSEPARATOR = ','
SET @NULLCHAR = 'NULL'
/* Start building the query */
SET @QRY1 = "SELECT 'INSERT INTO " + @TABLENAME + "(" --+ " VALUES ( "
SET @QRY2 = ''
OPEN CUR_METADATA
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add the Column names to the query */
SET @QRY1 = @QRY1 + @COLNAME + ','
/* Add Column values - Prefix & postfix the column values with quotes if they belong to any of the following datatypes */
/* TEXT - 35 | SMALLDATETIME - 58 | DATETIME - 61 | NTEXT - 99 | VARCHAR - 167 | CHAR - 175 | NVARCHAR - 231 | NCHAR - 239 */
IF ( @COLDATATYPE = 35 ) OR ( @COLDATATYPE = 58 ) OR ( @COLDATATYPE = 61 ) OR ( @COLDATATYPE = 99 ) OR
( @COLDATATYPE = 167 ) OR ( @COLDATATYPE = 175 ) OR ( @COLDATATYPE = 231 ) OR ( @COLDATATYPE = 239 )
BEGIN
IF @QRY2 = ''
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + "'NULL'" + ')' --+ "'')"
END
ELSE
BEGIN
SET @QRY2 = @QRY2 + @CHARPREFIX + @CHARPREFIX + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + "'NULL'" + ')' --+ "'')"
END
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @CHARPOSTFIX + @CHARPOSTFIX + @COLUMNSEPARATOR
END
ELSE /*If the column is a number dont prefix/postfix quotes */
BEGIN
SET @QRY2 = @QRY2 + '''' + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' + "'NULL'" + ')'
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @COLUMNSEPARATOR
END
--PRINT @QRY2
SET @PRVCOLDATATYPE = @COLDATATYPE
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
END
CLOSE CUR_METADATA
DEALLOCATE CUR_METADATA
SET @QRY1 = SUBSTRING ( @QRY1 , 1 , LEN ( @QRY1 ) - 1 )
SET @QRY1 = @QRY1 + ')VALUES(' --+ "'" + "'" +"'"
SET @QRY2 = SUBSTRING ( @QRY2 , 1 , LEN ( @QRY2 ) - 1 )
SET @QRY2 = @QRY2 + ')' + @CHARPOSTFIX
SET @QRY2 = @QRY2 + ' FROM ' + @TABLENAME
/* Create a temporary table to hold the result of the query - i.e. it will have the INSERT statements */
CREATE TABLE #SQLTRACE ( SQL VARCHAR ( 8000 ) )
--print @QRY1 + @QRY2
SET @QRY2 = ' INSERT INTO #SQLTRACE ' + @QRY1 + @QRY2
IF LEN ( @QRY2 ) < 8000
BEGIN
SET NOCOUNT ON
--PRINT @QRY2
EXEC ( @QRY2 )
--PRINT ' INSERT INTO #SQLTRACE ' + @QRY1 + @QRY2
--EXEC ( ' INSERT INTO #SQLTRACE ' + @QRY1 + @QRY2 )
UPDATE #SQLTRACE SET SQL = REPLACE ( SQL , "'NULL'" , @NULLCHAR )
SELECT * FROM #SQLTRACE
END
ELSE
BEGIN
PRINT 'UNABLE TO GENERATE INSERT STATEMENTS FOR TABLE :' +@TABLENAME
END
SET NOCOUNT OFF
END /* END OF FILE */
GO
Nice stuff. Had started on something similar but only got as far as the column list :blush:
Above query works fine on most tables I tried on except for one which goes over the 8096 row size (which needs changing anyway :( ) Not sure what you can do about tables that have data over 8k per row (inc. col names) as that's the max for the varchar can hold.
Try it on a table with 30 columns.
I tried it with one that had 42 columns and it was fine.
It's not the number of columns that's the problem, I'm sure it's the actual size of the data in the columns that's creating an insert text larger than 8k (ie. the size of the varchar that's holding it).
We seed data in our deployed systems with .SQL scripts like this - that run in QA or with ISQL/OSQL (can never remember which one!)...
Mendhak - I'm still unclear on why you need to build INSERT statements to insert data into tables that you know the structure of in advance.Code:USE Acctfiles
GO
delete from ledger_t
delete from invoice_t
delete from po_t
.
.
.
delete from cycle_t
delete from batch_t
delete from vendor_t
Go
PRINT 'About to Insert Invoice_T_0'
BULK INSERT Invoice_T FROM "c:\acs desktop\textoutput acct\Invoice_T_0.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Po_T_0'
BULK INSERT Po_T FROM "c:\acs desktop\textoutput acct\Po_T_0.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT ShipTo_T FROM "c:\acs desktop\acctfiles\ShipTo_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLFund_T FROM "c:\acs desktop\acctfiles\GLFund_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLObject_T FROM "c:\acs desktop\acctfiles\GLObject_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLSubObj_T FROM "c:\acs desktop\acctfiles\GLSubObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLType_T FROM "c:\acs desktop\acctfiles\SLType_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLFund_T FROM "c:\acs desktop\acctfiles\SLFund_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLRcvType_T FROM "c:\acs desktop\acctfiles\SLRcvType_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLOrg_T FROM "c:\acs desktop\acctfiles\SLOrg_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLFunc_T FROM "c:\acs desktop\acctfiles\SLFunc_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLObj_T FROM "c:\acs desktop\acctfiles\SLObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSubObj_T FROM "c:\acs desktop\acctfiles\SLSubObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchObj_T FROM "c:\acs desktop\acctfiles\SLSchObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchProgram_T FROM "c:\acs desktop\acctfiles\SLSchProgram_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchDept_T FROM "c:\acs desktop\acctfiles\SLSchDept_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLASN_T FROM "c:\acs desktop\acctfiles\SLASN_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLYear_T FROM "c:\acs desktop\acctfiles\SLYear_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLAcct_T FROM "c:\acs desktop\acctfiles\GLAcct_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Control_T'
BULK INSERT Control_T FROM "c:\acs desktop\acctfiles\Control_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Cycle_T'
BULK INSERT Cycle_T FROM "c:\acs desktop\acctfiles\Cycle_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Batch_T'
BULK INSERT Batch_T FROM "c:\acs desktop\acctfiles\Batch_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Vendor_T'
BULK INSERT Vendor_T FROM "c:\acs desktop\acctfiles\Vendor_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Check_T'
BULK INSERT Check_T FROM "c:\acs desktop\acctfiles\Check_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Invoice_T'
BULK INSERT Invoice_T FROM "c:\acs desktop\acctfiles\Invoice_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Po_T'
BULK INSERT Po_T FROM "c:\acs desktop\acctfiles\Po_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Ledger_T'
BULK INSERT Ledger_T FROM "c:\acs desktop\acctfiles\Ledger_T.txt" WITH
(KEEPIDENTITY
)
GO
Ah, I see what you're doing in your code, and that had not occurred to me.Quote:
Originally Posted by szlamany
I wanted to generate INSERT statements like that because I didn't really want to type out the entire INSERT statement of a say, 60 column table.
Your method actually looks pretty good though.
Like I said in an earlier post - we have this little program call TEXTOUTPUT...
This is a SUBMAIN from within that program (excuse the extremely poor code - probably the second program I wrote in VB four years ago when we migrated from the mainframes :blush: )...
It's all controlled through a .INI file in the app directory. The program is designed to run in a .BAT file - so no user interface...Code:Public Sub Main()
Dim i As Long, j As Long, k As Long, x As Long, y As Long, z As Long
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String
Dim booIni As Boolean _
, booSysStatOff As Boolean _
, booConnect As Boolean _
, booCleanup As Boolean
Dim rs As New ADODB.Recordset
Dim strDate As String
Dim strSel As String
Dim strTab As String
Dim lngDateFields() As Long
Dim f As Field
Dim strTbl(1 To 50) As String
Dim strSQL(1 To 50) As String
Dim lngCount(1 To 50) As Long
Dim lngTblCnt As Long
Dim booNoTDATE As Boolean
Dim booPosPay As Boolean
Dim strCheckType As String
Dim strCheckNum As String
Dim strCheckAmt As String
Dim strCheckStart As String
Dim strCheckEnd As String
Dim strIssueDate As String
Dim lngVoidCount As Long
Dim curVoidAmt As Currency
Dim curCheckAmt As Currency
Dim lngIssueCount As Long
Dim curIssueAmt As Currency
Dim objcmdSP As New ADODB.Command
Debug.Print "Main"
On Error GoTo Error_Handler
Begin:
'MousePointer = vbHourglass
If App.PrevInstance = True Then End
Open App.Path & "\" & App.EXEName & ".ini" For Input Access Read Shared As #1
gstrOutput = App.Path & "\"
Do While Not EOF(1)
Line Input #1, s1
i = InStr(s1, "=")
If i Then
Select Case Left(s1, i - 1)
Case "SERVER"
gstrServer = Mid(s1, i + 1)
Case "DATABASE"
gstrDatabase = Mid(s1, i + 1)
Case "TABLE"
lngTblCnt = lngTblCnt + 1
strTbl(lngTblCnt) = Mid(s1, i + 1)
strSQL(lngTblCnt) = "Select * from " & strTbl(lngTblCnt)
Case "SELECT"
strSQL(lngTblCnt) = Mid(s1, i + 1)
Case "COUNT"
lngCount(lngTblCnt) = CLng(Mid(s1, i + 1))
Case "OUTPUT"
If Right(s1, 1) <> "\" Then s1 = s1 & "\"
gstrOutput = Mid(s1, i + 1)
Case "TDATE"
If Mid(s1, i + 1) = "NO" Then booNoTDATE = True
Case "SP"
gstrSP = Mid(s1, i + 1)
Case "POSPAY"
booPosPay = True
End Select
End If
Loop
Close #1
Open gstrOutput & "TextOutput.log" For Output As #2
Print #2, "Log File Created"
gstrProgram = "Text Output Application"
booConnect = False
booCleanup = True
booSysStatOff = False
'Establish connection to SQL Server
If Not EstablishConnection(gstrServer, gstrDatabase) Then
Print #2, "Cannot Establish Connection"
GoTo Abort_Error
End If
Print #2, "Connection established - about to create ConnectRecord"
booConnect = True
Call ConnectRecord
If gintConnId = 0 Then
Print #2, "Connection Id=0 - not successful"
End
End If
For i = 1 To lngTblCnt
Open gstrOutput & strTbl(i) & ".txt" For Output As #1
strSel = strSQL(i) ' "Select * From " & strTbl(i)
rs.Open strSel, gCn
ReDim lngDateFields(0 To rs.Fields.Count - 1)
If lngCount(i) = 0 Then
lngCount(i) = rs.Fields.Count - 1
Else
lngCount(i) = lngCount(i) - 1
End If
For j = 0 To lngCount(i)
' 135 - Date, 3 - int, 129 - char
Select Case rs.Fields(j).Type
Case 135
lngDateFields(j) = 1
End Select
Next j
Do While Not rs.EOF
glngRecAffected = glngRecAffected + 1
s2 = ""
strTab = ""
For j = 0 To lngCount(i)
If booNoTDATE = True And j = lngCount(i) Then
s2 = s2 & strTab
Else
If lngDateFields(j) = 1 Then
s2 = s2 & strTab & Format(rs.Fields(j), "yyyymmdd")
Else
s2 = s2 & strTab & rs.Fields(j)
End If
End If
If j = 0 Then strTab = vbTab
Next j
Print #1, s2
If booPosPay Then
If Left(s2, 2) <> "$$" Then
strCheckNum = Left$(s2, 10)
strCheckAmt = Mid$(s2, 11, 11)
strCheckType = Mid$(s2, 44, 1)
curCheckAmt = CCur(Left$(strCheckAmt, 9) & "." & Mid$(strCheckAmt, 10))
If strCheckType = "V" Then
lngVoidCount = lngVoidCount + 1
curVoidAmt = curVoidAmt + curCheckAmt
Else
If strIssueDate = "" Then
strIssueDate = Mid$(s2, 22, 6)
strIssueDate = Mid$(strIssueDate, 3, 2) & "/" & Mid$(strIssueDate, 5, 2) & "/" & Left(strIssueDate, 2)
End If
lngIssueCount = lngIssueCount + 1
curIssueAmt = curIssueAmt + curCheckAmt
If strCheckStart = "" Or strCheckNum < strCheckStart Then strCheckStart = strCheckNum
If strCheckEnd = "" Or strCheckNum > strCheckEnd Then strCheckEnd = strCheckNum
End If
End If
End If
rs.MoveNext
Loop
rs.Close
Close #1
Next i
Print #2, ""
Print #2, "Records Affected : "; glngRecAffected
If gstrSP <> "" Then
Print #2, ""
Print #2, "Executing SP="; gstrSP
objcmdSP.ActiveConnection = gCn
objcmdSP.CommandText = gstrSP
objcmdSP.CommandType = adCmdStoredProc
objcmdSP.CommandTimeout = 300
objcmdSP.Execute
End If
If booPosPay Then
Print #2, ""
Print #2, "Issue Date : "; strIssueDate
Print #2, "Total Number of Issues : "; lngIssueCount
Print #2, "Total Dollar Amount : "; FormatNumber(curIssueAmt)
Print #2, "Beginning Serial Number : "; strCheckStart
Print #2, "Ending Serial Number : "; strCheckEnd
Print #2, ""
Print #2, "Total Number of Voids : "; lngVoidCount
Print #2, "Total Dollar Amount (Voids) : "; FormatNumber(curVoidAmt)
End If
Call ConnectRecord
Call TerminateConnection
'MsgBox "Success"
Here's an example of the .INI file. Note that if the SELECT= statement is left off, the default is SELECT * from the TABLE listed. We keep adding new keyword controls to the .INI file - last we did was for a POSITIVE PAY summary sheet for a BANK TRANSMISSION file.
Code:SERVER=SZLAMANYNB
DATABASE=Acctfiles
TABLE=PO_T
SELECT=Select * from PO_T where FiscalYr<>0 and POEntry=0
TABLE=Invoice_T
SELECT=Select * from Invoice_T where FiscalYr<>0 and InvEntry=0
OUTPUT=c:\acs desktop\TextOutput Acct