Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Create Insert Query

Author  Topic 

kapilarya
Yak Posting Veteran

86 Posts
Posted - 2005-08-07 : 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

22864 Posts
Posted - 2005-08-08 : 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
Go to Top of Page

kapilarya
Yak Posting Veteran

86 Posts
Posted - 2005-08-08 : 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
Go to Top of Page

kapilarya
Yak Posting Veteran

86 Posts
Posted - 2005-08-08 : 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-08-08 : 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
Go to Top of Page

kapilarya
Yak Posting Veteran

86 Posts
Posted - 2005-08-08 : 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
Go to Top of Page

kapilarya
Yak Posting Veteran

86 Posts
Posted - 2005-11-10 : 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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-11-11 : 09:58:44
Here is a simpler method
http://sqlteam.com/forums/topic.asp?TOPIC_ID=53420

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site