October 24, 2008 at 2:48 pm
Comments posted to this topic are about the item AUTOGENERATE INSERT UPDATE SELECT STATEMENTS UPDATED
November 21, 2008 at 8:44 am
Shouldn't it be CHAR(13) + CHAR(10) (i.e. CR + LF)?
November 23, 2008 at 5:04 am
if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allways
this error msg.
Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177
unknown Objectname '#sp'.
is there something missing in your script? ... creation of this "#sp" temp table?
i have nothing found .. pls send me a reply.
thx
Sincerely Lars
November 23, 2008 at 7:50 pm
RE: CR/LF/NewLine (Cade Roux)
Actually, with Transact-SQL, it's even easier, just embed the newline in your string. Try this in your Management Studio/Query Analyzer
SELECT 'This is a string
with some
embedded new line characters'
AS Message
(have output to text to see it properly).
Unlike most programming languages, SQL doesn't require strings to be terminated on the same line, and the newlines that are embedded in the string are preserved. (I know C-based languages have better multi-line support for strings, but I don't think any of them treat the embedded new lines the way that SQL does).
/Ryan
November 23, 2008 at 8:06 pm
ryan.price (11/23/2008)
RE: CR/LF/NewLine (Cade Roux)Actually, with Transact-SQL, it's even easier, just embed the newline in your string.
Yeah, I use that occasionally. I would prefer if there was a \r escape sequence or something, though. (I usually DECLARE @CRLF and SET @CRLF = CHAR(13) + CHAR(10) when I need that)
November 23, 2008 at 8:42 pm
I went through a similar exercise not long ago...
I haven't actually run the code from this article, since I'm quite happy with mine :P, but looked through it and have a couple of general comments/suggestions (more to do with the SQL than the auto-generation logic)
1) lose all the CHAR(10) + CHAR(13) stuff, and use embedded linefeeds in your constants (see my other reply). it'll make it much easier to read and maintain (once you can get your head around the fact that you can actually do it - it took me a while).
2) if you're using this on SQL 2005 or later, you can switch all your varchar(8000)s for varchar(MAX)s, and the 8000 character restriction will go.
3) you should probably declare your '@Tablename' as nvarchar(128) or sysname - which is what SQL stores them as. Even if 50 already seems extremely long for a table name...
4) nchars and nvarchars have a length that is half the 'datalength' that you get from sys.columns (I may have missed that in your code).
5) rather than use a multi-line CASE..WHEN..WHEN..WHEN construct, you could use an 'IN' statement instead e.g.: select case when 'a' in ('a','b','c') then 'yes' else 'no' end.
/Ryan
P.S. just for interest' sake, I'll attach the SQL 2005+ code for a view and udf that I use for generating the 'metadata' in my equivalent sproc. In my database, I've left the view there permanently, but there's no reason why it couldn't be used as a temp table-populating query inside a sproc.
November 23, 2008 at 8:59 pm
lars (11/23/2008)
if i execute the [dbo].[_SPLOG_AutoGenStatements] with different tables and option i get allwaysthis error msg.
Meldung 208, Ebene 16, Status 0, Prozedur _SPLOG_AutoGenStatements, Zeile 177
unknown Objectname '#sp'.
is there something missing in your script? ... creation of this "#sp" temp table?
i have nothing found .. pls send me a reply.
thx
Sincerely Lars
Thought I'd better try the procedure before I rate the article.... and I got the same problem....
November 24, 2008 at 12:01 am
perhaps your particular login does not have permissions top create temp tables. instead of using #sp create a table perhaps to accomodate the contents of #sp and modify the code appropiately...
something like this:
CREATE TABLE sp
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(100) NOT NULL
)
then use the find and replace function to replace #sp with sp
November 24, 2008 at 12:17 am
Thanks for the suggestions. i am actually not that experienced in sql and your suggestions have helped me advance my skill. thanks. also please see the post about the temporary table. as a matter of interest the code that creates the temp table is in _SPLOG_SPConstructor
CREATE TABLE #sp
(
spid BIGINT IDENTITY(1, 1),
sptext TEXT NOT NULL,
spname VARCHAR(100) NOT NULL
)
April 25, 2017 at 9:28 am
-- Description:Builds an insert-select statement from two table object_ids for a pair of temporary tables
CREATE
Function [dbo].[gen_InsertSelect] (@From_Object_Id int, @Into_Object_Id int) returns nvarchar(max)
AS
BEGIN
DECLARE @SQL nvarchar(max);
DECLARE @Into_TableName nvarchar(max)
DECLARE @From_TableName nvarchar(max)
DECLARE @Cols TABLE ([Name] nvarchar(100) NULL, [Id] int NULL)
;
SELECT @From_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @From_Object_id;
SELECT @Into_TableName=t.name FROM tempdb.sys.tables t WHERE t.object_id = @Into_Object_id;
----------------------------------------------------------------------------------------------------------------------------------------
-- Get list of common column names
INSERT INTO @Cols SELECT i.[Name], i.[Column_Id]
FROM tempdb.sys.columns i JOIN tempdb.sys.columns f
ON i.object_id = @Into_Object_Id and f.object_id = @From_Object_Id and i.Name=f.Name
;
-- convert list of names into a comma seperated string
SET @SQL = substring((SELECT ( ', ['+[NAME]+']' ) FROM @ColS b ORDER BY [Id] FOR XML PATH( '' )), 3, 4000 )
;
IF len(@SQL)>0 SET @SQL ='INSERT INTO '+@Into_TableName+' ('+@SQL+') SELECT '+@SQL+' FROM '+@From_TableName
;
RETURN @SQL
;
END
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply