| Author |
 Topic  |
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
 Posted - 08/07/2005 : 05:21:24
|
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO
CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO
CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO
----------------------------------
CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]
SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)
END
SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)
Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS
INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'
SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1
DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END
SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(3),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)
SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME
SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY
Kapil Arya
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/08/2005 : 01:08:14
|
Kapil,
Did you test this script?
When a table given has identity column, it gives the error
Server: Msg 2744, Level 16, State 2, Procedure CreateInsQryEx, Line 32
Multiple identity columns specified for table 'KSINSERTQRY'. Only one identity column per table is allowed.
When a table without identity column is given, the following error occurs
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'XYZ'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'nn'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 08/08/2005 : 05:23:24
|
madhivanan,
i m using this script to generate insert queries in office since 1 year. yes this script fails in certain cases like
identity field or if there is " ' " in any of the string field.
Kapil Arya
|
|
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 08/08/2005 : 05:26:40
|
can u please supply me the script of the table u r trying to generate the insert query and some records so that i can test
Kapil Arya
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/08/2005 : 05:32:33
|
>>identity field or if there is " ' " in any of the string field.
Absolutely correct
I tested it with other table and it is working well
Also you didnot check whether the table name exists in the database
If there is image, text columns, then the result is empty
Apart from those It is really Nice script
Madhivanan
Failing to plan is Planning to fail |
|
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 08/08/2005 : 07:44:08
|
i created it bcaz of too much of insert queries i have to create and with column name specific insert query, i took almost 1 full day to find the logic
Kapil Arya
|
|
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
Posted - 11/10/2005 : 03:22:50
|
-- Modified Version of CreateInsQry Procedure.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INSQUERY]
GO
CREATE TABLE [dbo].[INSQUERY] (
[Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[KS]
GO
CREATE TABLE [dbo].[KS] (
[IDCOL] [int] IDENTITY (1, 1) NOT NULL ,
[COLU] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[Datatype] [varchar] (100) COLLATE Arabic_CI_AS NULL ,
[Value] [varchar] (8000) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateInsQryEx]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL
AS
TRUNCATE TABLE INSQUERY
DECLARE @QUERY VARCHAR(7500)
DECLARE @ROW int, @NROW int
DECLARE @DATATYPE varchar(20)
SET @NROW = 1
DECLARE @COL int, @nCOL int
DECLARE @VALUE varchar(7500)
DECLARE @SELECTPART1 VARCHAR(7500)
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[KSINSERTQRY]
SET NOCOUNT ON
DECLARE @InsQuery varchar(7500)
IF(@TableName IS NULL)
BEGIN
RAISERROR ('TableName Cannot be Blank', 16, 1)
END
IF (@SelectPart = '*') --for setting if * is selected then.. specifying the column name.. :-)
Begin
Declare @SelectPart2 Varchar(8000)
Select @SelectPart2 = Coalesce(@SelectPart2 + ', ' ,' ') + '['+Column_Name + ']' From Information_Schema.Columns Where Table_Name = @TableName
Set @SelectPart = @SelectPart2
End
Print @SelectPart
SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName
IF(@FilterPart IS NOT NULL)
BEGIN
SELECT @Query = @Query + ' WHERE '+@FilterPart
END
EXEC(@Query)
Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 )
TRUNCATE TABLE KS
INSERT INTO KS (COLU,Datatype)
SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'KSINSERTQRY'
SELECT @ROW = COUNT(*) FROM KSINSERTQRY
WHILE (@NROW <= @ROW)
BEGIN
SET @SELECTPART1 = NULL
SELECT @COL = COUNT(*) FROM KS
SET @NCOL = 1
DECLARE @COLNAME varchar(50)
SET @InsQuery = 'INSERT INTO ' + @TableName + ' '
IF(@SelectPart <> '*')
BEGIN
SET @InsQuery = @InsQuery + '(' + @SelectPart + ')'
END
SET @InsQuery = @InsQuery + 'VALUES ('
WHILE (@NCOL < = @COL)
BEGIN
SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL
SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' +
+ @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' +
convert(varchar(10),@NROW) +') WHERE COLU = ''' + @COLNAME + ''''
--SELECT @Query
EXEC(@QUERY)
SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME
SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + ''''
when 'nvarchar' then '''' + '''' + @VALUE + '''' + ''''
when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + ''''
when 'char' then '''' + '''' + @VALUE + '''' + ''''
when 'nchar' then '''' + '''' + @VALUE + '''' + ''''
when 'smalldatetime' then '''' + '''' + @VALUE + '''' + ''''
when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END
SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + ''''
EXEC(@QUERY)
SET @NCOL = @NCOL +1
END
SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]'
--SELECT @SELECTPART1
SET @InsQuery = @InsQuery + @SELECTPART1 + ')'
INSERT INTO INSQUERY VALUES (@InsQuery)
SET @NROW = @NROW + 1
END
SELECT * FROM INSQUERY
Kapil Arya
|
Edited by - kapilarya on 11/10/2005 03:24:57 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
| |
Topic |
|
|
|
Create Insert Query
