June 20, 2008 at 5:33 pm
The spaces that it adds are Unicode characters. In order to get rid of them I saved it in notepad and it replaced them with ? - then I did a global replace.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 21, 2008 at 10:18 am
What would be involved in having this execute automatically for the output of this procedure that lists the user table names?
http://www.sqlservercentral.com/scripts/Miscellaneous/30070/
Then scripts for all tables could be generated in one shot!
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 21, 2008 at 10:55 am
I ran this script against one of my tables which does not have an identity column and seemed to do okay but with a couple of problems that I think are unrelated to the lack of an identity column.
The problem is that Sql Server complains abou the syntax of the word "NULL" after the field names in three places, like so:
CREATE PROCEDURE InsUpd_ChopDates
@Chop_Date date
NULL
INSERT INTO dbo.ChopDates (
Chop_Date
)
VALUES (
@Chop_Date
NULL
UPDATE dbo.ChopDates SET
Chop_Date = @Chop_Date
NULL
In order to get it to work in my test I had to delete the word "NULL" in all three places, add an "AS" keyword and close the parentheses on Values.
Shouldn't these be separate procedures? Why both in one? It looks like it will always add and always update. Or is this just to get you going, and you will just split the code up into two procedures?
I'm a major newbie, so many thanks.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 21, 2008 at 3:48 pm
Okay, I'm thinking that this is intended to be deployed as two separate scripts. To be more useful, I would suggest the following:
* have this automatically run for each table in the database (per my suggestion above)
* have the Insert and the Update be parsed into separate create statements, separated by a GO
* have all of the output for the whole job go to a single file (with all of the creates preceded by a Drop Procedure and followed by a GO)
* also add a simple SELECT *
* have the proc names begin with spIns and spSel and spUpd
Then you could, in a click, generate the whole deal!
I think that on one hand this script argues for the power of TSQL to do significant stuff while on the other hand it argues that CLR is probably going to come into play in the future for complicated stuff.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
June 22, 2008 at 2:00 pm
Ok, I have adapted this to my needs as follows:
* I changed it from a local temp table to a table so I could do all of the tables into one output
* I change the explicit ID numbers to automatic
* I added a Select function
* I added the drops
* I added support for schema names
I also changed per someone's suggestions for nVarChar, but then I had to divide the lengths by two because it doubled every one for some reason.
I took out the stuff that presumed an Identity primary key and I added a bogus "where ID = @ID" which will have to be changed later manually to the real key.
Here is my version:
/*
----------------------------------------------------------------------------------------------------------------------------------------
Author : Prasad Bhogadi
Name of the Procedure : spGenerateInsUpdateScript
----------------------------------------------------------------------------------------------------------------------------------------
Purpose :This Procedure is used generate Insert Update scripts for a tablet
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------
Input Parameters : Table Name
Expected Output : Generate script for Insert Update Stored procedure for a given table
---------------------------------------------------------------------------------------------------------------------------------------- */
DROP PROC spGenerateInsUpdateScript
GO
CREATE PROCEDURE spGenerateInsUpdateScript
@objname nvarchar(776) --object name we're after
as
SET NOCOUNT ON
DECLARE @shortObjName nVarChar(776) --strip off schema if present
DECLARE @periodPosition tinyint
SET @shortObjName = @objname
SET @periodPosition = CHARINDEX('.',@objname)
If (@periodPosition > 1) -- if there is a schema
BEGIN
SET @shortObjName = SUBSTRING(@objname,@periodPosition + 1,776)
END
DECLARE @objid int
DECLARE @sysobj_type char(2)
SELECT @objid = id, @sysobj_type = xtype
from sysobjects
where id = object_id(@objname)
DECLARE @colname sysname
SELECT @colname = name
from syscolumns
where id = @objid and colstat & 1 = 1
-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin
-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
DECLARE @numtypes nvarchar(80)
DECLARE @avoidlength nvarchar(80)
SELECT @numtypes = N'decimalreal,money,float,numeric,smallmoney'
SELECT @avoidlength = N'int,smallint,datatime,smalldatetime,text,bit'
---- INFO FOR EACH COLUMN
--CREATE TABLE MyProc
--(pkey INT NOT NULL IDENTITY (1, 1),
--ID INT ,
--MyStatement NVARCHAR(4000))
--
-- The "INSERT" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spInsert' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spInsert' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spInsert' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ' ' +
type_name(xusertype) + ' '
+ case when charindex(type_name(xtype),@avoidlength) > 0
then ''
else
case when charindex(type_name(xtype), @numtypes) <= 0
then '(' + convert(varchar(10), length / 2) + ')' else '(' +
case when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),ColumnProperty(id, name, 'precision'))
else '' end + case when charindex(type_name(xtype), @numtypes) > 0 then ',' else ' ' end +
case
when charindex(type_name(xtype), @numtypes) > 0
then convert(varchar(5),OdbcScale(xtype,xscale))
else ''
end + ')'
end
end + ', '
from syscolumns where id = @objid and number = 0 order by colid
update MyProc set MyStatement = Replace(MyStatement,', ',' ') where
pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT 'AS '
--BEGIN
--IF @' + @colname + ' <= 0
--BEGIN'
INSERT INTO MyProc (MyStatement)
SELECT ' INSERT INTO dbo.' + @objname + ' ('
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 4 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' )'
INSERT INTO MyProc (MyStatement)
SELECT ' VALUES ('
INSERT INTO MyProc (MyStatement)
SELECT ' @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 7 and MyStatement like '%' + @colname + '%'
update MyProc set MyStatement = Replace(MyStatement,'@DateCreated,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateCreated,'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 7 AND MyStatement like '%@DateModified,'
update MyProc
set MyStatement = Replace(MyStatement,',','')
where pkey = (SELECT max(pkey) from MyProc)
-- SET @colname = @@IDENTITY
INSERT INTO MyProc (MyStatement)
SELECT ')'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT ' '
-- The "UPDATE" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spUpdate' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spUpdate' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS UPDATE ' + @objname
INSERT INTO MyProc (MyStatement)
SELECT 'SET '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ' = @' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%DateCreated %'
update MyProc set MyStatement = Replace(MyStatement,'@DateModified,','GETDATE(),')
where ID = 11 AND MyStatement like '%@DateModified,'
update MyProc set MyStatement = Replace(MyStatement,',',' ')
where pkey = (SELECT max(pkey) from MyProc)
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
-- The "SELECT" routine
INSERT INTO MyProc (MyStatement)
SELECT '/* spGet' + @shortobjname + ' */'
INSERT INTO MyProc (MyStatement)
SELECT 'DROP PROCEDURE spGet' + @shortobjname + ' '
INSERT INTO MyProc (MyStatement)
SELECT 'GO'
INSERT INTO MyProc (MyStatement)
SELECT 'CREATE PROCEDURE spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'AS Select '
INSERT INTO MyProc (MyStatement)
SELECT ' ' + name + ','
from syscolumns where id = @objid and number = 0 order by colid
DELETE FROM MyProc
WHERE ID = 11 and MyStatement like '%' + @colname + '%'
INSERT INTO MyProc (MyStatement)
SELECT ' WHERE ID = @ID'
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT ' '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spInsert' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spUpdate' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
INSERT INTO MyProc (MyStatement)
SELECT 'Exec spGet' + @shortobjname
INSERT INTO MyProc (MyStatement)
SELECT 'GO '
-- disply the results into the output
-- SELECT MyStatement from MyProc ORDER BY ID
end
GO
Then to call it for all my files I do this:
DROP TABLE dbo.MyProc
GO
CREATE TABLE MyProc
(pkey INT NOT NULL IDENTITY (1, 1),
ID INT ,
MyStatement NVARCHAR(4000))
EXEC spGenerateInsUpdateScript N'dbo.Table1'
GO
EXEC spGenerateInsUpdateScript N'ref.Table2'
GO
Select MyStatement
FROM dbo.MyProc
Order by ID
Go
DROP TABLE dbo.MyProc
GO
This seems to work ok in my environment.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
August 30, 2008 at 1:21 pm
vblllove (12/6/2007)
I try to execute the Script but I get Msg 102, Level 15, State 1, Line 23Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 33
Incorrect syntax near '?'.
Msg 137, Level 15, State 1, Line 33
Must declare the scalar variable "@objid".
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near '?'.
Msg 137, Level 15, State 1, Line 39
Must declare the scalar variable "@colname".
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@sysobj_type".
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 58
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 72
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 72
Must declare the scalar variable "@objname".
Msg 102, Level 15, State 1, Line 78
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 112
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 116
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 118
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 122
Must declare the scalar variable "@colname".
Msg 102, Level 15, State 1, Line 130
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 130
Must declare the scalar variable "@objname".
Msg 102, Level 15, State 1, Line 136
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 138
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 144
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 150
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 154
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 156
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 160
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 162
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 166
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 168
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 170
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 176
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 182
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 188
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 194
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 198
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 204
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 208
Must declare the scalar variable "@colname".
Msg 102, Level 15, State 1, Line 226
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 230
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 232
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 232
Must declare the scalar variable "@objname".
Msg 102, Level 15, State 1, Line 240
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 242
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 248
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 254
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 260
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 266
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 270
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 272
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 274
Must declare the scalar variable "@colname".
Msg 102, Level 15, State 1, Line 288
Incorrect syntax near 'end'.
a lot of errors
September 1, 2008 at 2:20 am
Are you using the initial script or the modified script? Would you mind posting your table structure please.
Thanks
Prasad Bhogadi
www.inforaise.com
November 21, 2008 at 3:06 am
hi guys/girls
try this as an alternative as well
http://www.sqlservercentral.com/scripts/AutoGenerate++++++parameters/64760/
cheers
chris
November 25, 2008 at 8:13 am
Prasad Bhogadi (9/1/2008)
Are you using the initial script or the modified script? Would you mind posting your table structure please.Thanks
Hello hsitas953
I found that when I copy and paste the code in a text editor like Editplus the tabs that are in the script are converted to special characters "?". So you may want to paste the code in a text editor remove this characters and run from the query analyzer. I believe I have not taking enough precautions while posting the script to avoid such problems.
Thanks
Prasad Bhogadi
www.inforaise.com
November 26, 2008 at 3:26 am
Hi, Please forgive me sounding silly but I am a bit of a novice...
I am getting the follwoing errors. Are these as a result of my copying and pasting the script..? If so it might take me an age to try and debug as I can't see anything wrong with the transact SQL. Any tips please..?
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '?'.
Msg 137, Level 15, State 1, Line 29
Must declare the scalar variable "@objid".
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '?'.
November 26, 2008 at 3:32 am
nigelevenden (11/26/2008)
Hi, Please forgive me sounding silly but I am a bit of a novice...I am getting the follwoing errors. Are these as a result of my copying and pasting the script..? If so it might take me an age to try and debug as I can't see anything wrong with the transact SQL. Any tips please..?
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '?'.
Msg 137, Level 15, State 1, Line 29
Must declare the scalar variable "@objid".
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near '?'.
The issue is due to the special characters that are generated in the editor. What I would request you is to copy the script and paste it in a note pad and you just replace the special characters that are shown on each line where a tab is used and replace them. It should get compiled without any issues.
Prasad Bhogadi
www.inforaise.com
November 26, 2008 at 3:35 am
Ok, I've read some advice on here. I paste the script into notepad, removed all the bad characters (that are easy to spot) and now the script works... I look forward to trying it out later in the day (when I get a few minutes without customers complaining all day long...lol).
Many thanks to all 🙂
January 9, 2009 at 12:32 pm
I've been working on similar issues including reproducing creating table and missing columns and thought this would help a lot. It does, thank you. However there are some clitches in it. I created a table
CREATE TABLE dbo.tbl
(
bi BIGINT,
d DECIMAL (18, 2),
f FLOAT,
i INT,
m MONEY,
n NUMERIC (15, 3),
r REAL,
si SMALLINT,
sm SMALLMONEY,
ti TINYINT,
sChar CHAR(10),
sVarchar VARCHAR(100),
sMax VARCHAR (MAX),
id INT IDENTITY(1, 1)
)
GO
CREATE PROCEDURE InsUpd_tbl
@bi bigint (8),
@d decimal (18,2),
NULL
@i int ,
@m money (19,4),
@n numeric (15,3),
NULL
@Si smallint ,
@sm-2 smallmoney (10,4),
@ti tinyint (1),
@sChar char (10),
@sVarchar varchar (100),
@sMax varchar (-1),
@id int
AS BEGIN IF @id <= 0 BEGIN
INSERT INTO dbo.tbl (
bi,
d,
f,
i,
m,
n,
r,
si,
sm,
ti,
sChar,
sVarchar,
sMax
)
VALUES (
@bi,
@d,
@f,
@i,
@m,
@n,
@r,
@Si,
@ti,
@sChar,
@sVarchar,
@sMax
) SET @id = @@IDENTITY SELECT @id AS id END ELSE BEGIN
UPDATE dbo.tbl SET
bi = @bi,
d = @d,
f = @f,
i = @i,
m = @m,
n = @n,
r = @r,
si = @Si,
sm = @sm-2,
ti = @ti,
sChar = @sChar,
sVarchar = @sVarchar,
sMax = @sMax
WHERE id= @id SELECT @id AS id END END
and there are quite a few null fields. I think the precision or scale is causing this.
I have also have written a query that does the trick and thought I'd share it here.
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.fGetColumnList') IS NOT NULL
DROP FUNCTION dbo.fGetColumnList
GO
CREATE FUNCTION dbo.fGetColumnList( @TableId INT, @VariableList BIT )
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @ColumnList VARCHAR(MAX)
SET @ColumnList = ''
SELECT @ColumnList = @ColumnList + CASE @VariableList WHEN 1 THEN '@' ELSE '' END + LOWER(c.NAME) + ','
FROM sys.columns c
WHERE c.[object_id] = @TableId
AND c.is_identity = 0
ORDER BY c.column_id
SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList)-1)
RETURN @ColumnList
END
GO
IF OBJECT_ID('dbo.GenerateInsUpdateScript') IS NOT NULL
DROP PROCEDURE dbo.GenerateInsUpdateScript
GO
CREATE PROCEDURE dbo.GenerateInsUpdateScript(
@TableId INT
)AS
SET NOCOUNT ON
BEGIN
SELECT t.NAME TableName, 1 OrderBy, 0 column_id,
'IF OBJECT_ID(''pInsUpd_' + t.NAME + ''') IS NOT NULL' + CHAR(13) + CHAR(10) +
'DROP PROCEDURE pInsUpd_' + LOWER(t.NAME) + CHAR(13) + CHAR(10) +
'GO' + CHAR(13) + CHAR(10) +
'CREATE PROCEDURE pInsUpd_' + LOWER(t.NAME) + '('
FROM sys.tables t
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
UNION ALL
SELECT t.NAME TableName, 2 OrderBy, c.column_id, '@' + LOWER(c.NAME) + ' ' + typ.NAME +
CASE
--No Precision or scale
WHEN typ.NAME IN ('bigint','int','smallint','tinyint','bit','datetime','smalldatetime','smallmoney','money','real','text') THEN ''
--Precision
ELSE
CASE WHEN c.max_length = -1 THEN '(MAX'
ELSE
'(' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Precision') AS VARCHAR(10)) +
--and Scale
CASE WHEN typ.NAME IN ('decimal','numeric') THEN ',' + CAST(COLUMNPROPERTY(c.[object_id],c.NAME,'Scale') AS VARCHAR(10)) ELSE '' END
END +
')'
END +
CASE WHEN c.column_id < t.max_column_id_used THEN ','
ELSE
CHAR(13) + CHAR(10) +
')' + CHAR(13) + CHAR(10) +
'AS' + CHAR(13) + CHAR(10) +
'SET NOCOUNT ON' + CHAR(13) + CHAR(10) +
'BEGIN'
END
FROM sys.tables t
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
INNER JOIN sys.types typ ON typ.user_type_id = c.user_type_id
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
UNION ALL
SELECT OBJECT_NAME(c.[object_id]) TableName, 3 OrderBy, NULL column_id,
'IF @' + LOWER(c.NAME) + ' = 0'
FROM sys.tables t
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
UNION ALL
SELECT t.NAME TableName, 4 OrderBy, null,
'INSERT INTO ' + OBJECT_NAME(t.[object_id]) + '(' + dbo.fGetColumnList(t.OBJECT_ID, 0) + ')'
FROM sys.tables t
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
UNION ALL
SELECT t.NAME TableName, 5 OrderBy, null,
'VALUES(' + dbo.fGetColumnList(t.OBJECT_ID, 1) + ')'
FROM sys.tables t
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
UNION ALL
SELECT t.NAME TableName, 6 OrderBy, c.column_id,
CASE WHEN c.column_id = MinMaxColumn.MinColumn THEN 'ELSE' + CHAR(13) + CHAR(10) + 'UPDATE ' + t.[name] + ' SET' + CHAR(13) + CHAR(10) ELSE '' END +
'' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) +
CASE WHEN c.column_id < MinMaxColumn.MaxColumn THEN ',' ELSE '' END
FROM sys.tables t
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 0
INNER JOIN (
SELECT c.[object_id], MIN(c.column_id) MinColumn, MAX(c.column_id) MaxColumn
FROM sys.columns c
WHERE c.is_identity = 0
GROUP BY c.[object_id]
) MinMaxColumn ON MinMaxColumn.[object_id] = c.[object_id]
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
UNION ALL
SELECT OBJECT_NAME(c.[object_id]) TableName, 7 OrderBy, NULL column_id,
'WHERE ' + LOWER(c.NAME) + ' = @' + LOWER(c.NAME) + CHAR(13) + CHAR(10) + 'END' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.tables t
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.is_identity = 1
WHERE t.OBJECT_ID = COALESCE(@TableId, t.OBJECT_ID)
AND EXISTS(SELECT * FROM sys.columns ex WHERE ex.[object_id] = t.[object_id] AND ex.is_identity = 1)
ORDER BY TableName, OrderBy, column_id
END
GO
It can be changed a bit but the reason I'm getting tablename, orderby, column_id, etc... is becuase this is part of something bigger and i use osql and bcp to get the carriage return line feeds the way I want.
May 5, 2009 at 10:33 am
It works ok but beware, the values list generated for the Insert statement do not match the order of the columns in the Insert Into portion.
Easily remedied and it still saves a bunch of typing.
Thanks!!
October 22, 2013 at 11:08 am
Using SQL 2005
Float columns are returned as NULL...
CREATE PROCEDURE InsUpd_Master_invoicedetail
@LinkID int ,
@BU varchar (8),
@orgBU varchar (8),
@DB varchar (8),
@InvoiceNo varchar (32),
@SONo nvarchar (64),
NULL
NULL
NULL
NULL
If I select Insert to new query window I get
INSERT INTO [EProg_Master].[dbo].[Master_InvoiceDetail]
([BU]
,[orgBU]
,[DB]
,[InvoiceNo]
,[SONo]
,[ComPct1]
,[ComPct2]
,[ComPct3]
,[ComPct4]
VALUES
(<BU, varchar(8),>
,<orgBU, varchar(8),>
,<DB, varchar(8),>
,<InvoiceNo, varchar(32),>
,<SONo, nvarchar(32),>
,<ComPct1, float,>
,<ComPct2, float,>
,<ComPct3, float,>
,<ComPct4, float,>
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply